Depending on the amount of results a sub-query returns, we distinguish 2 types of sub-queries:
- Single row sub-queries: Queries that return only one row from the inner SELECT statement
- Multiple row sub-queries: Queries that return multiple rows from the inner SELECT statement
In the following single sub-query example, the AVG function returns just one result which is used by the outer query.
SELECT employee_id, last_name, salary
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary;
In the next example a multiple row sub-query is used to return some departments which the outer query will use in its IN clause.
SELECT last_name, department_id, job_id
WHERE department_id IN (SELECT department_id
It is important that both single row and multiple row sub-queries are correctly used in a context that either expects a single result or a multiple result respectively.
Its important to mention that the Oracle server will always execute sub-queries first.
Sub-queries can be used in the WHERE clause, but also in the HAVING clause.
This example returns job with the lowest average salary.
SELECT job_id, AVG(salary)
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
GROUP BY job_id);
The difference between HAVING and WHERE, is that WHERE does not work with aggregated functions, such as AVG, COUNT...