General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

Subquery is placed inside the opening and closing parenthesis of main query. Subquery is executed first then only main query will be executed.

Syntax:


SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

Single Row Subquery:

If a subquery returns only one row as result then it is called as single row subquery

Multirow subquery:

If a subquery returns more than one row as result, it is  called as multirow subquery The important thing is that we should know when to use subquery. Take the following scenario. We know the mobile sales tables data. From that we know which mobile is sold at highest cost. However, by using query how shall we find the high-cost mobile.

Steps:

1.    First we need the get the maximum price from the table using max query

2.    After that we have to write another query to find out the high-valued mobile name

Drawbacks:

1.    We have to write more than one query

2.    We are comparing the first query result with second query manually. So at any time the highest cost may change. So we cannot say the above steps will always produce the same correct result.

3.    This will cause data error. That’s why we going for subquery.

First we are going to take single row subquery. Subsequently, we will see about multirow subquery. We are going to write a query to find out the high-cost mobile using subquery.


Select mobile_name,price from mobile_sales_details
 where price =(select max(price) from mobile_sales_details)

 
mobile_name                           price     
------------------------------------  ----------
Apple iPhone 5S (Silver  with 32 GB)  62500

First the subquery which is residing inside the () is executed. The subquery will return the mobile phones maximum price and the main query compares the subqueries maximum price with its price and produce an output. So the costliest mobile phone in the table is apple iPhone 5s version.

Suppose if you change the price of mobiles or you are adding high-cost mobile other than this apple phone, the above query works fine and produce exact result always.

There is no need to change anything in the above query to find out high-cost mobile. The below query is also used as subquery concept. Select 9999 returns 9999 as result and the 9999 compared with price and produces an output.


Select * from mobile_sales_details where price =(select 9999);

However, if you choose more than one column in subquery that will throw an error. Below query chooses two columns in subquery. So the SQLite compiler will throw an error.


Select * from mobile_sales_details where price =(select 9999,9999);

(Error cannot select more than one column in subquery) The below query is used to find out mobile name and their prices ,whose price is greater than the price of Nokia Lumia 1320.

So the subquery will return Nokia Lumia’s price first then the price is compared with main query.


Select mobile_name,price from mobile_sales_details
 where price>(select price from mobile_sales_details
 where mobile_name=’Nokia Lumia 1320 (Black)’)

mobile_name             price     
----------------------  ----------
Moto X (16 GB) (Black)  23999     
Samsung Galaxy Grand 2  18990     
Samsung Galaxy S4 Mini  24730     
Samsung Galaxy S5 (Cha  54300     
Samsung Galaxy S4 I950  28071     
Samsung Galaxy S3 (Mar  31850     
Samsung Galaxy Grand D  19000     
Apple iPhone 5S (Gold   43199     
Apple iPhone 4S (White  21712     
Apple iPhone 5C (Blue   41900     
Apple iPhone 5S (Silve  62500     
Apple iPhone 5 (White   52500     
Sony Xperia ZR (Black)  19990     
Sony Xperia T2 Ultra (  28999     
BlackBerry Z10 (Charco  17990     
BlackBerry Z10 (Pure W  43490     

If you want to know how many mobiles have the same primary camera as BlackBerry Z10. The below query is used to achieve the result.


Select mobile_name,primary_camera_pixel from mobile_Sales_details
 where primary_camera_pixel=(Select primary_camera_pixel from mobile_Sales_details 
where mobile_name=’BlackBerry Z10 (Pure White)’)

mobile_name                              primary_camera_pixel
---------------------------------------  --------------------
Asus Zenfone 5 A501CG (Black with 8 GB)  8                   
Asus Zenfone 5 A501CG (Black  with 16 G  8                   
Samsung Galaxy Grand 2 (Black)           8                   
Samsung Galaxy S4 Mini I9192 (White Fro  8                   
Samsung Galaxy S3 (Marble White  with 1  8                   
Samsung Galaxy Grand Duos I9082          8                   
Apple iPhone 5S (Gold  with 16 GB)       8                   
Apple iPhone 4S (White  with 8 GB)       8                   
Apple iPhone 5C (Blue  with 16 GB)       8                   
Apple iPhone 5S (Silver  with 32 GB)     8                   
Apple iPhone 5 (White  with 32 GB)       8                   
Micromax Canvas 2 Colors A120 (Grey)     8                   
Micromax Canvas 2.2 A114 (Black)         8                   

The BlackBerry Z10’s camera size is 8 pixel. Then the main query will list out how many mobile have the same 8 pixel camera.

The below query is same as the above query. But the one difference is >= operator. In the above query we used =(equal) only.


Select mobile_name,primary_camera_pixel from mobile_Sales_details 
where primary_camera_pixel>=(Select primary_camera_pixel from mobile_Sales_details 
where mobile_name=’BlackBerry Z10 (Pure White)’)

mobile_name             primary_camera_pixel
----------------------  --------------------
Moto X (16 GB) (Black)  10                  
Asus Zenfone 5 A501CG   8                   
Asus Zenfone 5 A501CG   8                   
Asus Zenfone 6 A600CG   13                  
Alcatel Onetouch Idol   13.1                
Samsung Galaxy Grand 2  8                   
Samsung Galaxy S4 Mini  8                   
Samsung GT 1200 R/I/M                       
Samsung Guru 1200 (Bla                      
Samsung Galaxy S5 (Cha  16                  
Samsung Galaxy S4 I950  13                  
Samsung Guru E1207T (B                      
Samsung Galaxy S3 (Mar  8                   
Samsung Galaxy Grand D  8                   
Nokia 105 (Black)                           

It will return the 8 pixel and more than 8 pixel mobiles list. The below query will return the opposite result to the above query. It will return mobile name’s whose camera size is less than 8 pixel(black berry z10 has 8 pixel size).


Select mobile_name,primary_camera_pixel from mobile_Sales_details 
where primary_camera_pixel<(Select primary_camera_pixel from 
mobile_Sales_details where mobile_name=’BlackBerry Z10 (Pure White)’)

mobile_name                 primary_camera_pixel
--------------------------  --------------------
Moto G (Black  with 16 GB)  5                   
Moto E (Black)              5                   
Asus Zenfone 4 A400CG (Bla  5                   
Samsung Galaxy S Duos 2 S7  5                   
Samsung Galaxy Star Pro S7  2                   
Samsung Galaxy S Duos 2 S7  5                   
Samsung Galaxy Core I8262   5                   
Samsung Galaxy Trend S7392  3                   
Samsung Galaxy Grand Neo G  5                   
Samsung I8552 - Galaxy Gra  5                   
Nokia Lumia 520 (Black)     5                   
Nokia Lumia 630 Dual SIM (  5                   
Nokia XL (Bright Orange)    5                   
Nokia Lumia 1320 (Black)    5                   
Nokia 206 (Black  with Dua  1.3                 
Nokia Lumia 625 (Black)     5                   
Nokia 220 (Black)           2                   
Sony Xperia E (Black)       3.2                 

The below query is used to find mobile list, whose camera size is same as BlackBerry Z10 camera size and price is greater than the Nokia Lumia 1320. So here I just used two single row sub query with and condition


Select mobile_name,primary_camera_pixel,price from mobile_Sales_details where
 primary_camera_pixel=(Select primary_camera_pixel from mobile_Sales_details 
where mobile_name=’BlackBerry Z10 (Pure White)’) and price >
( select price from mobile_sales_details where mobile_name=’Nokia Lumia 1320 (Black)’)

mobile_name                     primary_camera_pixel  price     
------------------------------  --------------------  ----------
Samsung Galaxy Grand 2 (Black)  8                     18990     
Samsung Galaxy S4 Mini I9192 (  8                     24730     
Samsung Galaxy S3 (Marble Whit  8                     31850     
Samsung Galaxy Grand Duos I908  8                     19000     
Apple iPhone 5S (Gold  with 16  8                     43199     
Apple iPhone 4S (White  with 8  8                     21712     
Apple iPhone 5C (Blue  with 16  8                     41900     
Apple iPhone 5S (Silver  with   8                     62500     
Apple iPhone 5 (White  with 32  8                     52500     
BlackBerry Z10 (Charcoal Black  8                     17990     
BlackBerry Z10 (Pure White)     8                     43490

We can use group function in sub query. Here am going to find the low cost mobile by, first finding the low cost then compare the low cost with the main query.


Select mobile_name,price from mobile_sales_details 
where price=(select min(price) from mobile_sales_details)

mobile_name                   price     
----------------------------  ----------
Micromax Bolt X101i (Yellow)  882

Mircomax Bolt is the low-cost mobile in the mobile sales table. Now we are going to find out each brands low mobile price, but the price must be greater than the Samsung brand’s minimum price.


Select brand_name,min(price) from mobile_sales_details group by brand_name
 having min(price) > (Select min(price) from mobile_sales_details where brand_name=’Samsung’)

brand_name  min(price)
----------  ----------
Alcatel     16999     
Apple       21712     
Asus        5999      
BlackBerry  9125      
Karbonn     2699      
Lenovo      3490      
Motorola    6999      
Sony        6150      

If subquery returns no data the main query also return empty records. We do not have any mobile which is named as Nokia 1100. So the subquery will produce no result. So the main query also will return empty records.


Select mobile_name, price from mobile_sales_details 
where price = (Select price from mobile_sales_details 
where mobile_name=’Nokia 1100’)

(No result because subquery return nothing)