Set operations

Set operations combine the RESULTS of two separate queries. UNION stacks results vertically (like appending rows), while INTERSECT and EXCEPT find commonalities and differences.

Set operations visualized

JOINs combine columns horizontally (adding more columns per row). Set operations like UNION combine rows vertically (stacking result sets on top of each other). Both queries in a UNION must return the same number of columns with compatible types.

union.sql
sql
-- UNION: cities with customers OR sellers (deduplicated)
SELECT customer_city AS city FROM customers
UNION
SELECT seller_city AS city FROM sellers
ORDER BY city
LIMIT 20;

UNION combines results and removes duplicates. Both queries must have the same number of columns with compatible types.

UNION ALL is the faster sibling of UNION because it skips the deduplication step. Let us compare the two to see the difference in row counts.

union_all.sql
sql
-- UNION vs UNION ALL: see the difference
SELECT 'UNION' AS type,
  COUNT(*) AS row_count
FROM (
  SELECT customer_city FROM customers
  UNION
  SELECT seller_city FROM sellers
) u
UNION ALL
SELECT 'UNION ALL' AS type,
  COUNT(*) AS row_count
FROM (
  SELECT customer_city FROM customers
  UNION ALL
  SELECT seller_city FROM sellers
) ua;

UNION ALL keeps duplicates and is faster (no deduplication step). Use it when you know results are already unique or when you want all rows.

Beyond combining rows, SQL set operators can also find overlaps and differences. INTERSECT and EXCEPT are perfect for this.

intersect_except.sql
sql
-- INTERSECT: cities with BOTH customers and sellers
SELECT customer_city AS city FROM customers
INTERSECT
SELECT seller_city AS city FROM sellers
ORDER BY city
LIMIT 20;

-- EXCEPT: customer cities with no sellers
SELECT customer_city AS city FROM customers
EXCEPT
SELECT seller_city AS city FROM sellers
ORDER BY city
LIMIT 20;

INTERSECT finds common values. EXCEPT finds values in the first query that are NOT in the second. Order matters for EXCEPT!

Matching exercise: Match the set operation

Loading practice…

Quiz: Quiz

Loading practice…

Fill in the blanks: Complete the set operation

Loading practice…

Code playground: Sp-only categories

Loading practice…