Subquery

A subquery is a query nested inside another query. It can appear in SELECT, FROM, or WHERE, and either runs once or, when correlated, once per outer row.

A non-correlated subquery is independent and runs a single time (e.g. WHERE salary > (SELECT AVG(salary) FROM employees)). A correlated subquery references the outer query and re-executes for each outer row, which is powerful but can be slow on large tables.

Many subqueries can be rewritten as joins, which are often faster; EXISTS and NOT EXISTS are the idiomatic exception.

Example

SELECT first_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);