A named subquery is also known as inline view. It can be joined in the same manner as a regular table.
-- -- The following query returns a list of employee managers as well as -- the department id to which each is assigned. Note that the employee -- managers utilize the self relationship in the EMPLOYEES table. Also -- note that an employee manager is different than a department manager. -- SELECT first_name, last_name, department_id FROM employees WHERE manager_id IS NULL OR employee_id IN (SELECT manager_id FROM employees); -- -- This query can be used as a named subquery with an alias of managers. -- It can then be joined to the departments table to see a list of each -- employee manager and the name of the department to which each is assigned. -- SELECT managers.first_name, managers.last_name, d.department_name FROM ( SELECT first_name, last_name, department_id FROM employees WHERE manager_id IS NULL OR employee_id IN ( SELECT manager_id FROM employees ) ) managers INNER JOIN departments d ON d.department_id = managers.department_id;
This example starts with a query that makes sense for use as an inline view. It is a listing of all employee managers. This query is then used in the second query in the place of a table and is joined with the departments table to provide the final set of results.