Subqueries and EXISTS

Queries inside queries: scalar values, IN lists, correlated checks and derived tables.

• Intermediate
• 16 min read
• 3 objectives

Before this lessonLesson 5: UNION, INTERSECT and EXCEPT

A subquery is a SELECT wrapped in parentheses inside another statement. It shows up in four shapes, and telling them apart is most of the skill.

1. Scalar: a subquery that returns one value

If a subquery produces exactly one row and one column, you can use it anywhere a value is allowed — typically on the right of a comparison.

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

name     | salary
---------+-------
Grace    | 190000
Ada      | 150000
Margaret | 140000

2. IN: a subquery that returns a list

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

name
------
Ada
Grace

SELECT name FROM employees WHERE id NOT IN (SELECT manager_id FROM employees);

(no rows)

No rows — and not because every employee is a manager. Two rows have a NULL manager_id, which poisons the whole comparison. Here is the fix:

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

name
--------
Ada
Linus
Alan
Barbara

3. EXISTS: a correlated existence check

A correlated subquery mentions a column from the outer query, so it is evaluated once per outer row. EXISTS stops at the first match, so it does not care how many rows the inner query could return.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

name
------
Linus

This is the classic "who never did X" query, and it reads almost like the question does. The SELECT 1 is idiomatic: EXISTS ignores the selected columns entirely.

4. Derived tables: a subquery in FROM

A subquery in FROM behaves like a temporary table, which lets you filter on a value you had to compute first — something WHERE cannot do on its own.

SELECT name, spent
FROM (
  SELECT c.name AS name, SUM(o.total) AS spent
  FROM customers c JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
) AS totals
WHERE spent > 100;

name | spent
-----+------
Ada  | 155.5

Correlated subqueries in SELECT

You can also put a scalar subquery in the select list. It is readable, but it runs once per row, so a join or a window function is usually faster on large tables.

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c;

name  | orders
------+-------
Ada   | 2
Linus | 0
Grace | 1

Key takeaways

• A scalar subquery returns one value and can sit beside a comparison operator.
• IN takes a one-column list; NOT IN breaks on NULL.
• EXISTS / NOT EXISTS answer "is there any?" safely and stop early.
• A subquery in FROM is a derived table and needs an alias.

-- Write your solution here