How to use subquery in JOIN operation in MySQL

In the example below, the subquery actually returns a temporary table which is handled by database server in memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select statement. Note that the left and right table of the join keyword must both return a common key that can be used for the join.

Also note that, using subquery in JOIN operation should generally be avoided if you can rewrite your query in a different way, the reason being that no indexes can be used on a temporary table in memory.

Practice #1: Use subquery in SELECT statement with an aggregate function.

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

/*
For each product category, we want to know at what average unit 
price they were sold and what the average unit price we would 
like to sell for.
 
Subquery is used in FROM clause to get table x which returns the 
average unit price sold for each product category.
 
Table y in the join clause returns the average unit price 
we'd like to sell for each product category.
 
Then table x is joined with table y for each category.
*/
select y.CategoryID, 
    y.CategoryName,
    round(x.actual_unit_price, 2) as "Actual Avg Unit Price",
    round(y.planned_unit_price, 2) as "Would-Like Avg Unit Price"
from
(
    select avg(a.UnitPrice) as actual_unit_price, c.CategoryID
    from order_details as a
    inner join products as b on b.ProductID = a.ProductID
    inner join categories as c on b.CategoryID = c.CategoryID
    group by c.CategoryID
) as x
inner join 
(
    select a.CategoryID, b.CategoryName, avg(a.UnitPrice) as planned_unit_price
    from products as a
    inner join categories as b on b.CategoryID = a.CategoryID
    group by a.CategoryID
) as y on x.CategoryID = y.CategoryID

Query result set - 8 rows returned:

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 a list of values (known as column subquery)

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

5. Using correlated subqueries

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

7. Using subquery in SELECT statement in MySQL

8. Using subquery in FROM clause in MySQL

Back to Tutorial Index Page

This website is hosted by HostGator .

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.