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:
-- Selecting specific columns from the orders table
SELECT ord_num, ord_amount, ord_date,
cust_code, agent_code
-- Filtering orders based on agent_code
FROM orders
-- Subquery to find agent_codes working in Bangalore
WHERE agent_code IN (
-- Selecting agent_codes from agents table
SELECT agent_code
-- Filtering agents based on working_area
FROM agents
WHERE working_area='Bangalore'
);
Explanation:
- This SQL query retrieves specific columns from the "orders" table.
- It filters the orders based on the agent_code.
- The subquery is used to find agent_codes of agents who work in Bangalore.
- It first selects the agent_codes from the "agents" table.
- Then it filters these agents based on their working_area being 'Bangalore'.
- The main query then selects orders where the agent_code matches those found in the subquery, meaning it selects orders handled by agents working in 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:
-- Selecting the agent_code column from the agents table
SELECT agent_code
-- Filtering agents based on their working_area
FROM agents
-- Specifying the condition for filtering
WHERE working_area='Bangalore';
Explanation:
- This SQL query retrieves the agent_code from the "agents" table.
- It filters the agents based on their working_area.
- The condition specified in the WHERE clause restricts the results to only those agents whose working_area is '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:
-- Selecting specific columns from the orders table
SELECT ord_num, ord_amount, ord_date,
cust_code, agent_code
-- Filtering orders based on agent_code
FROM orders
-- Filtering out orders handled by agents in Bangalore
WHERE agent_code NOT IN (
-- Selecting agent_codes from agents table
SELECT agent_code
-- Filtering agents based on working_area
FROM agents
WHERE working_area='Bangalore'
);
Explanation:
- This SQL query retrieves specific columns from the "orders" table.
- It filters the orders based on the agent_code.
- The subquery is used to find agent_codes of agents who work in Bangalore.
- It first selects the agent_codes from the "agents" table.
- Then it filters these agents based on their working_area being 'Bangalore'.
- The main query then selects orders where the agent_code does not match those found in the subquery, meaning it selects orders handled by agents who do not work in 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:
-- Selecting specific columns from the agents table
SELECT agent_code, agent_name, working_area, commission
-- Filtering agents based on agent_code
FROM agents
-- Filtering agents whose agent_code matches any in the subquery
WHERE agent_code = ANY (
-- Selecting agent_codes from customer table
SELECT agent_code
-- Filtering customers based on cust_country
FROM customer
WHERE cust_country='UK'
);
Explanation:
- This SQL query retrieves specific columns from the "agents" table.
- It filters the agents based on the agent_code.
- The subquery is used to find agent_codes of agents who have customers in the UK.
- It first selects the agent_codes from the "customer" table.
- Then it filters these customers based on their cust_country being 'UK'.
- The main query then selects agents whose agent_code matches any of those found in the subquery, meaning it selects agents who have customers in the 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:
-- Selecting specific columns from the agents table
SELECT agent_code, agent_name, working_area, commission
-- Filtering agents based on agent_code
FROM agents
-- Filtering agents whose agent_code matches any in the subquery
WHERE agent_code = ANY (
-- Selecting agent_codes from customer table
SELECT agent_code
-- Filtering customers based on cust_country
FROM customer
WHERE cust_country='UK'
);
Explanation:
- This SQL query retrieves specific columns from the "agents" table.
- It filters the agents based on the agent_code.
- The subquery is used to find agent_codes of agents who have customers in the UK.
- It first selects the agent_codes from the "customer" table.
- Then it filters these customers based on their cust_country being 'UK'.
- The main query then selects agents whose agent_code matches any of those found in the subquery, meaning it selects agents who have customers in the UK.
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:
-- Selecting specific columns from the orders table
SELECT ord_num, ord_amount, ord_date,
cust_code, agent_code
-- Filtering orders based on agent_code
FROM orders
-- Filtering orders where the agent_code matches a subquery result
WHERE agent_code = (
-- Selecting distinct agent_code from orders table
SELECT DISTINCT agent_code
-- Filtering orders based on cust_code
FROM orders
WHERE cust_code='C00005'
);
Explanation:
- This SQL query retrieves specific columns from the "orders" table.
- It filters the orders based on the agent_code.
- The subquery selects distinct agent_codes from the "orders" table.
- It filters these agent_codes based on the cust_code being 'C00005'.
- The main query then selects orders where the agent_code matches the result of the subquery, meaning it selects orders handled by the agent(s) who have customers with the 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:
-- Selecting specific columns from the orders table
SELECT ord_num, ord_amount, ord_date,
cust_code, agent_code
-- Filtering orders based on agent_code
FROM orders
-- Specifying the condition for filtering
WHERE agent_code = 'A002';
Explanation:
- This SQL query retrieves specific columns from the "orders" table.
- It filters the orders based on the agent_code.
- The condition specified in the WHERE clause restricts the results to only those orders where the agent_code is 'A002', meaning it selects orders handled by the agent with the 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 and Language Statistics
- Weekly Trends and Language Statistics