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
|101||23-MAR-20||James||123 blue avenue||737373737||800|
|103||23-MAR-20||Kelly||888 Bin Lin Street||727277272||300|
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’);
The following tables are used in example 1 to example 4
|102||Flat 23||Diamond Building||Toronto||Toronto||727398|
|104||123, Blue Reef||Basil||Chicago||Chicago||92243|
|105||63 H1 Block||Super Enclave||Bunny||NewYork||528111|
|107||12 GIGI block||Lina||Clifford||Dublin||431002|
Fetch the address details of customer having name ‘Viva’.
select *from custaddress where custid =( select custid from custmaster where fname='Viva')
List the details of customers living in state ‘Toronto’
select * from custmaster where custid in (select custid from custaddress where state='Toronto')
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);
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))