Below are the different types of examples.
- Find all the employees who are department managers.
FROM employee
WHERE employee_number IN
(SELECT manager_employee_number
FROM department);
last_name
---------
Trader
Ryan
Runyon
Note: Subqueries always produce a DISTINCT list of values
SELECT Subquery with AND
- Who are the department managers whose salaries are less than 550000 and whose budget amounts are greater than 950000?
,first_name
FROM employee
WHERE salary_amount< 550000
AND (SELECT manager_employee_number
FROM department
WHERE budget_amount > 950000 );
last_name first_name
----------- ------------
Ryan Loretta
SELECT Subquery with Quantifier ANY
- Who are the employees who work in support departments?
,first_name
,department_number
FROM employee
WHERE department_number = ANY
(SELECT department_number
FROM department
WHERE department_name LIKE%Support%’);
last_name first_name department_number
----------- ----------- ----------------------
Daly James 402
Trader James 401
Johnson Darlene 401
Rogers Frank 401
: : :
EXISTS with Subquery
- Do any departments have no employees?
SELECT ‘YES’
WHERE EXISTS
(SELECT department_number FROM department
WHERE department_number NOT IN
(SELECT department_number
FROM employee));
- Do any employees work in department 600?
SELECT ‘TRUE’
WHERE EXISTS
(SELECT *
FROM employee
WHERE department_number =600);
Rules for Subqueries
Rules for Subqueries
- Must be enclosed in parentheses
- Can be the object of an IN or NOT IN clause
- Can be the object of EXISTS or NOT EXISTS clause
- Support quantifiers ALL, ANY, SOME
- Support LIKE or NOT LIKE used with a quantifier
- Can specify more than one column to match
- Generate a DISTINCT list of values
- Cannot use ORDER BY (within the subquery statement).
- Keep in mind that a maximum of 64 tables/views can be specified in an SQL statement.
