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

Using subquery in SELECT statement and WHERE clause

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.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
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.

/*
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.

Next tutorial: Using subquery to return one or more rows of values
Back to Section Index: Using Subquery
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search