SQL: Multiple Row and Column Subqueries
Multiple Row Subqueries
Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.
Contents:
SQL: Using IN operator with a Multiple Row Subquery
IN operator is used to checking a value within a set of values. The list of values may come from the results returned by a subquery. See the following example :
To get 'ord_num', 'ord_amount', 'ord_date', 'cust_code' and 'agent_code' from the table 'orders' with following conditions:
Sample table: agents
Sample table: orders
in the outer query:
'agent_code' of 'orders' table must be in the list within IN operator in inner query :
in inner query:
'working_area' of 'agents' table must be 'Bangalore',
Here is the complete SQL statement :
SQL Code:
SELECT ord_num,ord_amount,ord_date,
cust_code, agent_code
FROM orders
WHERE agent_code IN(
SELECT agent_code FROM agents
WHERE working_area='Bangalore');
Output:
ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ---------- ---------- --------- ---------- ---------- 200130 2500 30-JUL-08 C00025 A011 200105 2500 18-JUL-08 C00025 A011 200117 800 20-OCT-08 C00014 A001 200124 500 20-JUN-08 C00017 A007 200112 2000 30-MAY-08 C00016 A007
Let's break the above query and analyze what's going on in inner query. Here is the code of inner query :
SQL Code:
SELECT agent_code FROM agents
WHERE working_area='Bangalore';
Relational Algebra Expression:

Relational Algebra Tree:

Output:
AGENT_CODE ---------- A001 A007 A011
The above query returns two agent codes 'A011' and 'A001'.
Pictorial Presentation:

SQL: Using NOT IN operator with a Multiple Row Subquery
You can also use NOT IN operator to perform the logical opposite of IN operator. See the following example :
Sample table: orders
Sample table: agents
To get 'ord_num', 'ord_amount', 'ord_date', 'cust_code' and 'agent_code' from the table 'orders' with following conditions :
in outer query:
'agent_code' of 'orders' table must be other than the list within IN operator.
in inner query :
'working_area' of 'agents' table must be 'Mumbai'
Here is the complete SQL statement :
SQL Code:
SELECT ord_num,ord_amount,ord_date,
cust_code, agent_code
FROM orders
WHERE agent_code NOT IN(
SELECT agent_code FROM agents
WHERE working_area='Bangalore');
Output:
ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CO --------- ---------- --------- ---------- -------- 200129 2500 20-JUL-08 C00024 A006 200118 500 20-JUL-08 C00023 A006 200111 1000 10-JUL-08 C00020 A008 200101 3000 15-JUL-08 C00001 A008 200114 3500 15-AUG-08 C00002 A008 200100 1000 08-JAN-08 C00015 A003 200127 2500 20-JUL-08 C00015 A003 200113 4000 10-JUN-08 C00022 A002 200133 1200 29-JUN-08 C00009 A002 200128 3500 20-JUL-08 C00009 A002 200126 500 24-JUN-08 C00022 A002 200120 500 20-JUL-08 C00009 A002 200123 500 16-SEP-08 C00022 A002 200106 2500 20-APR-08 C00005 A002 200116 500 13-JUL-08 C00010 A009 200132 4000 15-AUG-08 C00013 A013 200115 2000 08-FEB-08 C00013 A013 200125 2000 10-OCT-08 C00018 A005 200103 1500 15-MAY-08 C00021 A005 200134 4200 25-SEP-08 C00004 A005 ......... .........
Let's break the above query and analyze what's going on in inner query. Here is the code of inner query :
SQL Code:
SELECT agent_code FROM agents
WHERE working_area='Bangalore';
Relational Algebra Expression:

Relational Algebra Tree:

Output:
AGENT_CODE ---------- A001 A007 A011
Pictorial Presentation:

SQL: Using ANY with a Multiple Row Subquery
You can use the ANY operator to compare a value with any value in a list. You must place an =, <>, >, <, <= or >= operator before ANY in your query. The following example uses ANY to check if any of the agent who belongs to the country 'UK'.
Sample table : agents
Sample table : customer
To get 'agent_code', 'agent_name', 'working_area', 'commission' from 'agents' table with following conditions -
in outer query:
'agent_code' should be any 'agent_code' from 'customer' table
in inner query:
) 'cust_country' in the 'customer' table must be 'UK',
Here is the complete SQL statement :
SQL Code:
SELECT agent_code,agent_name,working_area,commission
FROM agents
WHERE agent_code=ANY(
SELECT agent_code FROM customer
WHERE cust_country='UK');
Output:
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION ---------- ---------------------------------------- ----------------------------------- ---------- A009 Benjamin Hampshair .11 A003 Alex London .13 A006 McDen London .15
Pictorical Presentation:

SQL: Multiple Column Subqueries
You can write subqueries that return multiple columns. The following example retrieves the order amount with the lowest price, group by agent code.
Sample table : orders
SQL Code:
select ord_num, agent_code, ord_date, ord_amount
from orders
where(agent_code, ord_amount) IN
(SELECT agent_code, MIN(ord_amount)
FROM orders
GROUP BY agent_code);
Output:
ORD_NUM AGENT_CODE ORD_DATE ORD_AMOUNT ---------- ---------- --------- ---------- 200104 A004 13-MAR-08 1500 200121 A004 23-SEP-08 1500 200126 A002 24-JUN-08 500 200120 A002 20-JUL-08 500 200123 A002 16-SEP-08 500 200124 A007 20-JUN-08 500 200116 A009 13-JUL-08 500 200105 A011 18-JUL-08 2500 200130 A011 30-JUL-08 2500 200131 A012 26-AUG-08 900 200135 A010 16-SEP-08 2000 200115 A013 08-FEB-08 2000 200117 A001 20-OCT-08 800 200111 A008 10-JUL-08 1000 200118 A006 20-JUL-08 500 200103 A005 15-MAY-08 1500 200100 A003 08-JAN-08 1000
Pictorial Presentation:

SQL: Subqueries using DISTINCT
In this section, we are discussing the usage of DISTINCT clause in a subquery.
Example:
Sample table: orders
To get 'ord_num', 'ord_amount', 'ord_date', 'cust_code' and 'agent_code' from the table 'orders' with following conditions -
in outer query:
the 'agent_code' of 'orders' table must be the same 'agent_code' of 'orders' table with following conditions -
'agent_code' of 'orders' table should come distinctly with following
inner query:
;the 'cust_code' of 'orders' table must be 'C00005'
Here is the complete SQL statement :
SQL Code:
SELECT ord_num,ord_amount,ord_date,
cust_code, agent_code
FROM orders
WHERE agent_code=(
SELECT DISTINCT agent_code
FROM orders WHERE cust_code='C00005');
Output:
ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ---------- ---------- --------- ---------- ---------- 200106 2500 20-APR-08 C00005 A002 200123 500 16-SEP-08 C00022 A002 200120 500 20-JUL-08 C00009 A002 200126 500 24-JUN-08 C00022 A002 200128 3500 20-JUL-08 C00009 A002 200133 1200 29-JUN-08 C00009 A002 200113 4000 10-JUN-08 C00022 A002
The inner of the above query returns the 'agent_code' A002.
The simplified form of above code is :
SQL Code:
SELECT ord_num,ord_amount,ord_date,
cust_code, agent_code
FROM orders
WHERE agent_code='A002';
Relational Algebra Expression:

Relational Algebra Tree:


Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Single Row Subqueries
Next: Correlated subqueries using aliases
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join