Subqueries, EXISTS & set operations

A subquery is a query embedded inside another SQL statement. Set operations combine complete query results. These tools are especially useful when the business rule is naturally phrased as “rows where another set contains/does not contain something.”

Scalar subqueries

A scalar subquery must return one value. It can be compared with each outer row.

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

Run the query to see the result.

If the subquery unexpectedly returns multiple rows, engines normally raise an error rather than guessing.

IN with a subquery

SELECT id, name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'paid'
)
ORDER BY id;

Run the query to see the result.

IN is readable when NULL behavior is understood and the intent is membership in a set.

EXISTS and correlated subqueries

EXISTS asks whether at least one row satisfies a condition. The subquery can refer to the current outer row.

SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
    AND o.status = 'paid'
)
ORDER BY c.id;

Run the query to see the result.

SELECT 1 is conventional because EXISTS cares about row existence, not selected values.

NOT EXISTS for missing relationships

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

Run the query to see the result.

This avoids the NULL trap associated with NOT IN.

UNION versus UNION ALL

UNION combines result sets and removes duplicates. UNION ALL preserves every row and is normally faster because it does not need duplicate elimination.

SELECT email FROM active_customers
UNION ALL
SELECT email FROM archived_customers
ORDER BY email;

Run the query to see the result.

Choose UNION only when deduplication is actually part of the requirement.

INTERSECT and EXCEPT

INTERSECT returns rows present in both result sets. EXCEPT returns rows from the first set that are absent from the second. They are excellent for expected-versus-actual comparisons when both sides have compatible columns.

SELECT id, status, amount FROM expected
EXCEPT
SELECT id, status, amount FROM actual;

Run the query to see the result.

Reverse the two sides to find unexpected actual rows.

When to prefer joins, EXISTS, or sets

Use a join when you need columns from both relations. Use EXISTS when you only need to test presence. Use a set operation when you are comparing entire compatible row sets. These choices often express intent better than forcing every problem into a join.

Practice: return customers that have a paid order but no pending order.