Subqueries, CTEs, set operations

What is a subquery and where can it be used?

A subquery is a query inside another query, in parentheses.

• Scalar: returns one value, can be compared: WHERE amount > (SELECT AVG(amount) …).
• In WHERE with IN / EXISTS: a membership or existence check.
• In FROM: a temporary table, with an alias required.
• Correlated: references the outer query and is logically executed for each of its rows.

-- transactions larger than the average
SELECT id, amount FROM transactions
WHERE amount > (SELECT AVG(amount) FROM transactions);

-- correlated: each account's largest transaction
SELECT t.* FROM transactions t
WHERE t.amount = (SELECT MAX(t2.amount)
                  FROM transactions t2
                  WHERE t2.account_id = t.account_id);

How does IN differ from EXISTS?

• IN compares a value with a list returned by a subquery.
• EXISTS only checks whether the subquery returns at least one row, and can stop at the first one found.

For positive checks modern optimisers often execute both variants the same way. The real difference is in handling NULL in the negative form: NOT EXISTS behaves predictably, and NOT IN doesn't (see the next question).

-- users who have at least one failed transaction
SELECT u.email FROM users u
WHERE EXISTS (
  SELECT 1 FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  WHERE a.user_id = u.id AND t.status = 'failed'
);

Why can NOT IN return zero rows?

If the subquery in NOT IN returns at least one NULL, the condition becomes UNKNOWN for every row, and the result is empty. With no error at all.

The logic: 5 NOT IN (1, 2, NULL) means 5 <> 1 AND 5 <> 2 AND 5 <> NULL. The last is UNKNOWN, so the whole condition is not TRUE.

The solution: NOT EXISTS, an anti-join through LEFT JOIN, or explicitly filter out NULL in the subquery.

-- if at least one account.user_id = NULL, it returns 0 rows
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM accounts);

-- safe
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM accounts a WHERE a.user_id = u.id);

What is a CTE (WITH) and why is it needed?

A CTE (Common Table Expression) is a named temporary result declared with WITH before the main query. The same as a subquery in FROM, but with a name.

• Readability: a complex query is split into clear steps from top to bottom.
• Reuse: one CTE can be referenced several times.
• Recursion: WITH RECURSIVE walks hierarchies, for example a category tree or subordination.

WITH monthly AS (
  SELECT a.user_id, SUM(t.amount) AS deposits
  FROM transactions t
  JOIN accounts a ON a.id = t.account_id
  WHERE t.type = 'deposit' AND t.status = 'completed'
    AND t.created_at >= '2026-09-01' AND t.created_at < '2026-10-01'
  GROUP BY a.user_id
)
SELECT u.email, m.deposits
FROM monthly m
JOIN users u ON u.id = m.user_id
WHERE m.deposits > 10000
ORDER BY m.deposits DESC;

How does UNION differ from UNION ALL? What are INTERSECT and EXCEPT?

• UNION combines the results of two queries and removes duplicates, so it is slower.
• UNION ALL combines everything as is, with duplicates. If duplicates can't occur or are needed, take it.
• INTERSECT: the rows present in both results.
• EXCEPT: the rows from the first that aren't in the second (in Oracle it is MINUS).

The condition: the same number of columns and compatible types.

-- emails that are in the old system but missing in the new one after migration
SELECT email FROM legacy_users
EXCEPT
SELECT email FROM users;