Subquery in SQL- Nesting Queries within Queries

Subquery in SQL or nesting a query within another query is an extremely important concept in SQL. It allows the programmer to fetch rows from one table depending upon the data stored in another table.

Consider this situation

Your supermarket billing data is maintained in two tables. You have Invoice data in InvoiceMaster table and details of invoice in InvoiceDetail table. The table InvoiceMaster contains columns- Invoice No, Invoice Date, Customer Name, Customer Address, Contact Number and Total Amount Paid. The InvoiceDetail table is having columns- Invoice No, Item Name, Quantity, Price and amount. You have to find all the invoices which were billed for “Pineapple” and report the Customer Names and Contact Numbers for the month of February.

What will you do?

In simple solution you have to find the invoice numbers from InvoiceDetail with item name =”Pineapple”. Using these invoice numbers you can now find the customer name and phone numbers from InvoiceMaster Table. So your queries will be like

First Query: SELECT InvoiceNo from InvoiceDetail where ItemName=’Pineapple’

Second Query: SELECT CustomerName, ContactNumber from InvoiceMaster where invoiceDate between ’01-Mar-20’ and ’29-Mar-20’ and InvoiceNo in (invoice numbers returned by previous query)

SQL provides a better solution to this with SubQuery. You can nest the first query within second query such that the results of one query are used in where condition of second query. Read along to get answer to the above and learn how this is done!!

 Syntax of Subquery in SQL

SELECT column-name[s] FROM table1 WHERE filter-column-name COMPARISON-OPERATOR (SELECT column-name FROM table2)

This is the simplest form of using a subquery within a query. The COMPARISON-OPERATOR can be any comparison operator from =, !=, >,>=,<,<=, ANY or IN.  

Rules to define Subquery

  • A subquery can have only one column to return after execution
  • When a subquery returns a single row use these comparison operators =, !=, >,>=,<,<=
  • When a subquery returns multiple rows use ANY,ALL or IN
  • You can use a subquery with in a subquery of another query

Examples of SubQuery in SQL

We will be first solving the requirement of the case we discussed at the beginning. The tables are created using following create table SQL statements.

create table invoicemaster
(
InvoiceNo int,
InvoiceDate date, 
CustomerName varchar2(40),
CustomerAddress varchar2(50), 
ContactNumber varchar2(20),
TotalAmount number(8,2) 
);

create table InvoiceDetail (
InvoiceNo int, 
ItemName varchar2(20), 
Quantity int, 
Price int,
amount int
);

with following data

INVOICENO INVOICEDATE CUSTOMERNAME CUSTOMERADDRESS CONTACTNUMBER TOTALAMOUNT
102 23-MAR-20 Mary bing Valley 737733838 200
101 23-MAR-20 James 123 blue avenue 737373737 800
103 23-MAR-20 Kelly 888 Bin Lin Street 727277272 300
INVOICENO ITEMNAME QUANTITY PRICE AMOUNT
102 Pineapple 2 40 80
102 Cornflakes 1 120 120
103 Shampoo 1 100 100
103 Medicines 2 100 200
101 Pineapple 5 40 200
101 Rice 2 400 400
101 Shampoo 1 100 100
101 Medicines 2 50 100

Query

SELECT customername, contactnumber FROM invoicemaster WHERE invoiceno in
(SELECT invoiceno FROM invoicedetail WHERE itemname='Pineapple' and InvoiceDate BETWEEN ’01-mar-20’ AND ’31-mar-20’);

Output

Subquery in SQL example

The following tables are used in example 1 to example 4

CUSTID FNAME LNAME
101 Evan Bayross
102 Reeta Narayan
103 Martin King
104 Teena Turner
105 Guy Bulls
106 Viva Merchant
107 Bob Dylan
108 Kate Helan

CustAddress

CUSTID HOUSENO STREET CITY STATE PINCODE
101 123 Pale Street Ontario Ontario 123456
102 Flat 23 Diamond Building Toronto Toronto 727398
103 cabin1 office Enclave Curant California 367098
104 123, Blue Reef Basil Chicago Chicago 92243
105 63 H1 Block Super Enclave Bunny NewYork 528111
106 424 Boulevard 33 Blive Washington 431001
107 12 GIGI block Lina Clifford Dublin 431002

CustFixedDep

CUSTID ACCNO
101 221
102 222
103 223
104 224
105 225
106 226
107 227

FixdepDetails

ACCNO AMOUNT
221 10000
222 20000
223 3000
224 5000
225 7000
226 1000
227 10000

Example 1

Fetch the address details of customer having name ‘Viva’.

select *from custaddress where custid =( select custid from custmaster where fname='Viva')

Output

Example1

Example 2

List the details of customers living in state ‘Toronto’

select * from custmaster where custid in (select custid from
custaddress where state='Toronto')

Output

Subquery Example 2

Example 3

List the details of customers having fixed deposits greater than average FD amounts

select * from custfixeddep C, fixdepdetails F  where C.accno= F.accno and F.amount >(select avg(amount) from fixdepdetails);

Output

Example 3

Example 4

Display customer ID, and name whose fixed deposit amounts are greater than 5000

select *from custmaster  where custid in (select custid from
custfixeddep  where accno in(select accno
from FIXDEPDETAILS where amount>5000))

Output

Subquery Example 4

Be First to Comment

Leave a Reply

Your email address will not be published.