How to write subquery in FROM clause

Using subquery in FROM clause

When you put a select statement into a FROM clause, it becomes a subquery. The subquery returns a temporary table in database server's memory and then it is used by the outer query for further processing.

Please note that, using subquery in FROM clause 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. Also, A subquery in the FROM clause can't be correlated subquery as it can't be evaluated per row of the outer query.

Practice #1: Use subquery in FROM clause.

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 uses a subquery in the FROM clause.
The subquery is given an alias x so that we can
refer to it in the outer select statement.
*/
select x.ProductID, 
    y.ProductName,
    x.max_unit_price
from
(
    select ProductID, max(UnitPrice) as max_unit_price
    from order_details
    group by ProductID
) as x
inner join products as y on x.ProductID = y.ProductID

Query result set - 77 rows returned:
Use subquery within FROM clause.

Next tutorial: Using subquery in JOIN operation
Back to Section Index: Using Subquery
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search