Using Named Subqueries

Contact Us or call 1-877-932-8228
Using Named Subqueries

Using Named Subqueries

A named subquery is also known as inline view. It can be joined in the same manner as a regular table.

Code Sample:

Joining-Tables/Demos/named_subquery.sql
--
--  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.

Next