Using subquery to return a list of values (known as column subquery)

Inside This Tutorial Series

Using subquery to return a single value
Using subquery to return a list of values
Using subquery to return one ore more rows of values
Using correlated subqueries
Using EXISTS and NOT EXISTS with correlated subqueries

Using subquery to return a list of values

When the subquery returns a list of values, the subquery is only evaluated once and then the list of values is returned to outer query to use. This kind of subqueries are also known as column subquery.

Column subquery is normally used in WHERE clause with an IN operator that tests each value in the returned list from the subquery.

Practice #1: Use subquery to return a list of values.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query retrieves a list of customers that made 
purchases after the date 1998-05-01.
 
The subquery returns a list of CustomerIDs which is
used in outer query.
*/
select CustomerID, CompanyName
from customers
where CustomerID in 
(
   select CustomerID 
   from orders 
   where orderDate > '1998-05-01'
);
 
/*
This query returns the same result as query above
because the list of CustomerIDs are used rather than
the subquery.
*/
select CustomerID, CompanyName
from customers
where CustomerID in 
(
    'BONAP',
    'DRACD',
    'ERNSH',
    'LEHMS',
    'LILAS',
    'PERIC',
    'QUEEN',
    'RATTC',
    'RICSU',
    'SIMOB',
    'TORTU'
);

Query result set - 11 rows returned:
Use subquery to return a list of values.

Practice #2: Use inner join to return the same result as using a subquery.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns the same result as the one
in Practice #1 but here no subquery is used. 
Instead, we used inner join. 
 
Often, a query that contains subqueries can be 
rewritten as a join.
 
Using inner join allows the query optimizer to 
retrieve data in the most efficient way.
*/
select a.CustomerID, a.CompanyName
from customers as a
inner join orders as b on a.CustomerID = b.CustomerID
where b.orderDate > '1998-05-01'

Query result set - 77 rows returned:
Use inner join to return the same result as using a subquery.

Other tutorials in this category

1. What is subquery

2. Using subquery to return a single value (known as single-value subquery or scalar subquery)

3. Using subquery to return one or more rows of values (known as row subquery)

4. Using correlated subqueries

5. Using EXISTS and NOT EXISTS in correlated subqueries in MySQL

6. Using subquery in SELECT statement in MySQL

7. Using subquery in FROM clause in MySQL

8. JOIN a table with a subquery

Back to Tutorial Index Page

	Custom Search