Subqueries and CTEs Updated November 2026

Subquery

A subquery is a SELECT nested inside another statement, in parentheses. It can return one value to compare against (WHERE salary > (SELECT AVG(salary) FROM employees)), a list for IN, or a whole table to select from in FROM. A correlated subquery references the outer row and runs once per row.

Syntax

SELECT ... WHERE column operator (SELECT ...);  |  SELECT ... FROM (SELECT ...) AS t;

Examples

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

Result: Employees paid above the company average

A scalar subquery returns exactly one value.

SELECT country, AVG(spent) AS avg_spent
FROM (SELECT c.country, SUM(o.amount) AS spent
      FROM orders o JOIN customers c ON c.id = o.customer_id
      GROUP BY c.id, c.country) AS per_customer
GROUP BY country;

Result: Average spend per customer, by country

A derived table in FROM must have an alias.

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

Result: Employees above the average of their own department

Correlated: the inner query uses e.dept from the outer row.

Common mistakes

A scalar subquery that returns more than one row errors in PostgreSQL (more than one row returned by a subquery used as an expression); SQLite silently uses the first row. Aggregate or add LIMIT 1.
Forgetting the alias on a subquery in FROM is an error in PostgreSQL (subquery in FROM must have an alias).
A correlated subquery in SELECT or WHERE runs once per outer row and can be slow on big tables; rewrite as a JOIN or window function when it matters.

Practice Subquery

These drills check your answer on live data, free, no account needed.

Above the average salaryReturn the names of employees who earn more than the average salary of all employees.
Second highest salaryReturn the second highest salary as a single value.
Third highest salaryReturn the third highest distinct salary as a single value.

Questions

What is the difference between a subquery and a JOIN?

A JOIN combines columns from both tables in the output; a subquery usually filters or computes a value without exposing the inner table's columns. Many subqueries can be rewritten as joins and vice versa; pick the clearer one.

What is a correlated subquery?

A subquery that refers to a column of the outer query, so it is evaluated for each outer row. EXISTS subqueries are usually correlated.

WITH (CTE)A common table expression defines a named temporary result with WITH name AS (...) that the main query uses like a table. Examples, several CTEs, recursion.
EXISTSEXISTS is true when the subquery returns at least one row. Use it for has-any checks and NOT EXISTS for rows with no match, the safe alternative to NOT IN.
INWHERE column IN (...) keeps rows whose value appears in a list or in a subquery result. Examples, NOT IN, the NULL trap, and when to use EXISTS instead.
Aliases (AS)An alias gives a column or table a temporary name: SELECT price * qty AS total, FROM orders o. Where aliases are allowed (ORDER BY yes, WHERE no) and quoting.