SQL: Subquery reference

Example & execution plan

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

MySQL will execute the above query as follows:

1. Execute the sub/inner query first to return the set of office codes in the US.
2. Execute the outer query and feed the result of the subquery to the WHERE clause.

Sources

• MySQL Subquery – mysqltutorial.org