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.

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:

Pictorial Presentation:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Single Row Subqueries
Next: Correlated subqueries using aliases