How to use subquery in JOIN operation in MySQL

Using subquery in JOIN operation

A subquery can be used with JOIN operation.

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.

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.

/*
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:
Use subquery with JOIN operation.

Next tutorial: Summarize Data with GROUP BY clause
Back to Section Index: Using Subquery
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search