Stacking and comparing results

UNION stacks two results into one. INTERSECT and EXCEPT find what two results share, or what one has that the other doesn't.

Joins (lessons 14 and 15) put tables side by side, adding columns. Set operations put results on top of each other, working with whole rows. This lesson uses the new contacts table: the people at each customer account. Show tables in the sandbox lists its columns.

UNION ALL: stack two results

SELECT name, 'Rep' AS type FROM users
UNION ALL
SELECT name, 'Contact'     FROM contacts;

That’s 5 reps plus 31 contacts: one list of 36 people. The text 'Rep' and 'Contact' labels each row with where it came from, which you’ll almost always want once two sources are mixed together.

Three rules for stacking:

The database matches columns by position, not by name. SELECT name, email stacked on SELECT email, name runs without complaint and mixes names and emails in the same column.

UNION vs. UNION ALL

UNION ALL keeps every row. UNION removes duplicate rows after stacking:

SELECT name FROM users
UNION
SELECT name FROM contacts;        -- 35 rows

35, not 36. A contact at Granite Credit Union is also called Ben Carter, the same as one of the reps, so UNION treats the two as one row. They’re two different people.

Default to UNION ALL. It’s faster, because removing duplicates means sorting everything, and it never silently drops real rows. Use UNION only when you actually want duplicates gone.

Stacking summary rows

UNION ALL is handy for building a small report out of separate counts:

SELECT 'Won'  AS outcome, COUNT(*) AS deals FROM opportunities WHERE is_won = 1
UNION ALL
SELECT 'Lost',            COUNT(*)          FROM opportunities WHERE is_won = 0
UNION ALL
SELECT 'Open',            COUNT(*)          FROM opportunities WHERE closed_at IS NULL;

Sorting a stacked result

ORDER BY goes once, at the very end, and sorts the whole stacked result:

SELECT name, 'Rep' AS type FROM users
UNION ALL
SELECT name, 'Contact'     FROM contacts
ORDER BY name;

An ORDER BY in the middle, after the first query, is an error.

INTERSECT: rows in both

Which accounts have contacts and deals?

SELECT account_id FROM contacts
INTERSECT
SELECT account_id FROM opportunities;

11 accounts. Like UNION, INTERSECT removes duplicates, so each account shows once however many contacts or deals it has.

EXCEPT: rows in the first but not the second

Which accounts have contacts but no deals?

SELECT account_id FROM contacts
EXCEPT
SELECT account_id FROM opportunities;     -- 14: Oakline Accounting

Order matters with EXCEPT. Swap the two queries and you get the opposite question, accounts with deals but no contacts: Harbor Foods (10) and Ironworks Fabrication (11). That’s a CRM data-quality check you’d hand to sales ops.

EXCEPT and INTERSECT compare whole rows. They’re best for short lists of IDs. When you need other columns from the matching rows, use EXISTS (next lesson) or a join.

Common mistakes

Dialect notes

Snowflake notes

Snowflake supports UNION, UNION ALL, INTERSECT and EXCEPT, and accepts MINUS as another name for EXCEPT. It also has UNION ALL BY NAME, which matches columns by name instead of position, so two queries with the same columns in a different order stack correctly.

More in the field guide: UNION / UNION ALL and Set logic: UNION, INTERSECT, EXCEPT.

Try it

Build one list of every person’s name: reps from users and customer contacts from contacts, sorted by name. Keep every row, even when two people share a name. (Adding a 'Rep' or 'Contact' label column is good practice, but leave it off here so the check can tell UNION from UNION ALL.)

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson