Subqueries in PostgreSQL

A subquery is a SELECT statement nested inside another query — used when a value or filter depends on the result of a separate lookup.

Scalar Subquery — Returns a Single Value

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- employees earning above the company-wide average

Subquery with IN

SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE name = 'Engineering'
);

Subquery with EXISTS

EXISTS only checks whether the subquery returns any rows at all — it's often faster than IN for large subquery results, since Postgres can stop as soon as it finds one match.

SELECT name FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.id
);
-- departments that have at least one employee

SELECT name FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.id
);
-- departments with ZERO employees

Correlated Subquery

A subquery that references a column from the outer query — it re-runs once per outer row, unlike a plain subquery that runs once total.

SELECT name, salary, department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);
-- employees earning more than their OWN department's average

Subquery in the SELECT List

SELECT
  name,
  (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS headcount
FROM departments d;

Common Errors & Troubleshooting

• ERROR: more than one row returned by a subquery used as an expression — a scalar subquery (= (SELECT ...)) must return exactly one row; if multiple rows are possible, use IN/ANY/EXISTS instead, or add a LIMIT 1 if only one is genuinely meaningful.
• Correlated subquery running unexpectedly slowly — since it re-runs once per outer row, a correlated subquery over a huge table can be slow; check whether it can be rewritten as a JOIN or a window function instead (see the Window Functions lesson).
• NOT IN silently returning zero rows — if the subquery can produce even one NULL, NOT IN returns no rows at all for the entire query — use NOT EXISTS instead (covered in the Anti-Joins lesson).

Key Takeaways

• Scalar subqueries return one value and can be used anywhere a literal value would go.
• EXISTS/NOT EXISTS are typically the fastest way to test "does at least one matching row exist?"
• A correlated subquery references the outer query's current row, so it effectively runs once per outer row.