UNION, EXCEPT & CTEs: Combining and Structuring Queries

UNION stacks the results of two SELECTs that have the same number of columns and compatible types, and removes duplicate rows. UNION ALL keeps duplicates and is faster. Use it to merge money-in and money-out into one list:

SELECT account_id, amount, 'in'  AS direction FROM deposits
UNION ALL
SELECT account_id, amount, 'out'             FROM withdrawals;

EXCEPT returns the rows of the first result that do not appear in the second - 'on list A but not on list B'. INTERSECT returns the rows present in both. They compare whole rows and de-duplicate the output:

SELECT employee_id FROM badge_swipes
EXCEPT
SELECT employee_id FROM approved_visitors;

Rules for set operators: both queries need the same number of columns in the same order, the column names come from the first query, and ORDER BY goes once, at the very end.

A CTE (common table expression) names a query so you can use it like a table:

WITH big_orders AS (
  SELECT customer_id, amount
  FROM orders
  WHERE amount > 1000
)
SELECT c.name, COUNT(*) AS big_orders
FROM big_orders AS b
JOIN customers AS c ON c.id = b.customer_id
GROUP BY c.name;

Chain several CTEs with commas; each one can use the ones above it. This is how long questions stay readable - every step is a small query you can test by selecting from it:

WITH daily AS (
  SELECT date(created_at) AS day, SUM(amount) AS revenue
  FROM orders
  GROUP BY date(created_at)
),
best AS (
  SELECT MAX(revenue) AS top FROM daily
)
SELECT day, revenue
FROM daily
WHERE revenue = (SELECT top FROM best);

CTE vs. subquery. The result (and usually the speed) is the same; the CTE wins on readability, can be referenced more than once, and lets you check each step independently. Decide the steps first, then name them.

Approach for a multi-step puzzle. Write one plain sentence per step ('find the suspect accounts', 'find their transfers', 'sum by destination'), turn each sentence into a CTE, then finish with a short final SELECT.