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.

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

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

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. JOIN a table with a subquery

Back to Tutorial Index Page