Subquery basics - Oracle SQL quick ref 3

A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. A sub-query is also called an inner query or inner select, while the statement containing a sub-query is also called an outer query or outer select.

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
FROM employees
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
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM departments
WHERE location_id=2897); 

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)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                                           FROM employees 
                                           GROUP BY job_id);


The difference between HAVING and WHERE, is that WHERE does not work with aggregated functions, such as AVG, COUNT...