Tuning Subqueries in the FROM Clause
It is possible to include subqueries within the FROM clause of a SQL statement. Such
subqueries are sometimes called unnamed views
, derived tables , or inline views .
For instance, consider the query in Example 21-10, which retrieves a list of employees and department details for employees older than 55 years.

Figure 21-7. Scalability of various anti-join techniques (no index)
Example 21-10. Example SQL suitable for rewrite with an inline view
SELECT departments.department_name,employee_id,surname,firstname
FROM departments
JOIN employees
USING (department_id)
WHERE employees.date_of_birth<date_sub(curdate( ),interval 55 year)
Short Explain
1 SIMPLE select(range) on employees using i_employee_dob
Using where
1 SIMPLE select(eq_ref) on departments using PRIMARY
Using whereThis query is well optimized—an index on date of birth finds the
customers, and the primary key index is used to find the department
name on the departments table.
However, we could write this query using inline views in the FROM clause, as shown in Example 21-11.
Example 21-11. SQL rewritten with an inline view
SELECT departments.department_name,employee_id,surname,firstname
FROM (SELECT * FROM departments ) departments
JOIN (SELECT * FROM employees) employees
USING (department_id)
WHERE employees.date_of_birth<DATE_SUB(curdate( ), INTERVAL 55 YEAR) Explain plan 1 PRIMARY ...