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.
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 where
This 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
However, we could write this query using inline views in the
FROM clause, as shown in Example 21-11.
SELECT departments.department_name,employee_id,surname,firstname FROM (SELECT * FROM departments ) departments JOIN (SELECT * FROM employees) employees USING (department_id)
WHEREemployees.date_of_birth<DATE_SUB(curdate( ), INTERVAL 55 YEAR) Explain plan 1 PRIMARY ...