1. Using subquery to return a single value
2. Using subquery to return a list of values
3. Using subquery to return one ore more rows of values
4. Using correlated subqueries
5. Using EXISTS and NOT EXISTS with correlated subqueries

The name of correlated subqueries means that a subquery is correlated with the outer query. The correlation comes from the fact that the subquery uses information from the outer query and the subquery executes once for every row in the outer query.

A correlated subquery can usually be rewritten as a join query. Using joins enables the database engine to use the most efficient execution plan. The query optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery should normally be rephrased as a join to gain the extra speed in performance.

Note that alias must be used to distinguish table names in the SQL query that contains correlated subqueries.

Practice #1: Using correlated subquery.

By examining the query in this practice, we can sum up the following steps that the database engine takes to evaluate the correlated subquery. It demonstrates that the subquery uses data from the outer query and the subquery executes once for every row in the outer query.

1. The outer query passes a value for ProductID to the subquery. It takes place in the WHERE clause in the subquery [ where a.ProductID = b.ProductID ]

2. The subquery uses this passed-in ProductID value to look up the max unit price for this product
   [ select max(UnitPrice) from order_details ]

3. When the max unit price for the product is found in the subquery, it's returned to the outer query.

   The outer query then uses this max unit price in its WHERE clause to match unit price in order_details table for this product [ where a.UnitPrice = ]

   When the row is found, query engine temporarily holds the row in memory. It's guaranteed that a row will be found because both outer query and subquery use the same table - order_details.

4. The query engine then moves onto next row in the order_details table and repeat Step 1 to 3 again for the next product.

5. When all products in order_details have been evaluated, it does a sorting and then returns the query result.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
Because a product can be sold at a discount, 
we want to know the highest unit price ever 
sold for each product.
 
This query uses correlated subquery to get
the highest unit price for each product sold. 
 
Alias is used for order_details in both the
subquery and outer query.
*/
select distinct a.ProductID, 
       a.UnitPrice as Max_unit_price_sold
from order_details as a
where a.UnitPrice = 
(
    select max(UnitPrice)
    from order_details as b
    where a.ProductID = b.ProductID
)
order by a.ProductID;
 
/*
This query returns the exact same result as the one above. 
It uses inner join to rephrase the query above and 
the query performance is dramatically improved.
*/
select distinct a.ProductID, a.UnitPrice as Max_unit_price_sold
from order_details as a
inner join 
(
    select ProductID, max(UnitPrice) as Max_unit_price_sold
    from order_details 
    group by ProductID
) as b
on a.ProductID=b.ProductID and a.UnitPrice=b.Max_unit_price_sold
order by a.ProductID;

Query result set - 77 rows returned:

Practice #2: Using correlated subquery and extra inner join.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query is the same as the query in Practice #1
except that it added in an extra inner join for products
table to look up the product name. This inner join is 
not involved in the subquery.
*/
select distinct a.ProductID, 
       p.ProductName, 
       a.UnitPrice as Max_unit_price_sold
from order_details as a
inner join products as p on a.ProductID = p.ProductID
where a.UnitPrice = 
(
    select max(UnitPrice)
    from order_details as b
    where a.ProductID = b.ProductID
)
order by a.ProductID;

Query result set - 77 rows returned:

Practice #3: Using different tables in subquery and outer query.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query finds out a list of orders and their customers 
who ordered more than 20 items of product Grandma's 
Boysenberry Spread (ProductID 6) on a single order.
*/
select a.OrderID, 
       a.CustomerID
from orders as a
where 
(
    select Quantity 
    from order_details as b 
    where a.OrderID = b.OrderID and b.ProductID = 6
) > 20;

Query result set - 5 rows returned: