UNION, INTERSECT & EXCEPT

Joins combine tables side by side . Set operators combine results top to bottom — stacking, intersecting, or subtracting whole result sets. They answer questions like "everyone in either list", "people in both lists", and "people in this list but not that one".

Learn UNION, INTERSECT & EXCEPT in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll confidently reach for UNION , UNION ALL , INTERSECT , and EXCEPT , and know the column-alignment rules that make them work.

What You'll Learn

Our Sample Tables

Two lists of customers — some shop in both channels.

1. UNION — Stack and De-duplicate

UNION places one result set on top of another and removes duplicate rows, giving a clean combined list. Both queries must return the same number of columns with compatible types.

Merging two address books with UNION is like combining them and deleting any contact that appears twice — you end up with one entry per person.

2. UNION ALL — Keep Everything

UNION ALL skips the duplicate-removal step. It's faster and keeps every row — ideal when you know the inputs don't overlap, or when duplicates carry meaning.

3. INTERSECT — Rows in Both

INTERSECT returns only the rows that appear in both result sets — here, customers who shop both online and in store.

4. EXCEPT — Subtract a Set

EXCEPT returns rows in the first query that are not in the second. Order matters — swapping the two SELECTs flips the answer. (Oracle calls this MINUS .)

Your Turn: one merged list

Fill in the operator that merges both tables into one de-duplicated list of names.

5. Sorting and Labelling Combined Results

A single ORDER BY at the very end sorts the whole combined set. Adding a constant "channel" column is a neat trick to remember which query each row came from.

6. Column Compatibility Rules

Set operators line columns up by position , not name. Every SELECT must return the same number of columns with compatible types. If counts don't match, pad the shorter side with a placeholder like NULL .

Reorder Challenge

These lines of a sorted UNION query are scrambled. Put them in order:

Quick Recall

1) Online = {Ava, Ben, Cleo} , Store = {Ava, Ben, Dan, Eve} . What does UNION return?

Ava, Ben, Cleo, Dan, Eve — five unique names. UNION removes the duplicate Ava and Ben.

2) What does INTERSECT return for those same sets?

Ava and Ben — the only names present in both lists.

3) SELECT ... FROM store EXCEPT SELECT ... FROM online returns?

Dan and Eve — store names that are not online. Order matters: EXCEPT subtracts the second set from the first.

Common Errors (and the fix)

📘 Quick Reference

Operator

Returns

Frequently Asked Questions

Mini-Challenge: Store-Only Customers

Pick the right set operator to find customers who only appear in the store list. Write it and verify in a playground.

🎉 Lesson Complete

Practice quiz

What is the key difference between UNION and UNION ALL?

  • UNION keeps duplicates; UNION ALL removes them
  • They are identical
  • UNION removes duplicate rows; UNION ALL keeps them
  • UNION ALL only works on one table

Answer: UNION removes duplicate rows; UNION ALL keeps them. UNION de-duplicates the combined result; UNION ALL keeps every row.

Which is generally faster, and why?

  • UNION ALL, because it skips the duplicate-removal step
  • UNION, because it sorts the data
  • They take exactly the same time
  • UNION, because it returns fewer rows always

Answer: UNION ALL, because it skips the duplicate-removal step. UNION ALL just concatenates results; UNION must sort/compare to remove duplicates.

For a set operation to work, each SELECT must return...

  • Exactly one column
  • Columns with identical names
  • Data from the same table
  • The same number of columns with compatible types

Answer: The same number of columns with compatible types. Columns line up by position; counts must match and types must be compatible.

Set operators line up columns by...

  • Column name
  • Position (order in the SELECT)
  • Alphabetical order
  • Data size

Answer: Position (order in the SELECT). Matching is positional; the result column names come from the first SELECT.

What does INTERSECT return?

  • Rows present in both result sets
  • Rows in either result set
  • Rows in the first set only
  • All rows with duplicates kept

Answer: Rows present in both result sets. INTERSECT returns only rows that appear in both result sets.

What does 'SELECT ... FROM A EXCEPT SELECT ... FROM B' return?

  • Rows in B not in A
  • Rows in both A and B
  • Rows in A that are not in B
  • All rows from A and B

Answer: Rows in A that are not in B. EXCEPT returns rows in the first set that are not in the second; order matters.

What is Oracle's name for the EXCEPT operator?

  • DIFF
  • MINUS
  • SUBTRACT
  • REMOVE

Answer: MINUS. Oracle spells EXCEPT as MINUS.

Where should ORDER BY go in a UNION query?

  • On each SELECT separately
  • It is not allowed with UNION
  • Before the first SELECT
  • Once, at the very end, to sort the combined result

Answer: Once, at the very end, to sort the combined result. A single ORDER BY at the end sorts the whole combined result.

If one SELECT has 1 column and the other has 2, how can you make a UNION work?

  • UNION automatically pads the shorter side
  • Pad the shorter side with a placeholder like NULL AS col
  • Use UNION ALL instead
  • Swap the order of the SELECTs

Answer: Pad the shorter side with a placeholder like NULL AS col. Add a NULL (or constant) column so both sides have the same column count.

You see unexpected duplicate rows in your combined result. What likely happened?

  • You used UNION when you wanted UNION ALL
  • You forgot ORDER BY
  • You used UNION ALL when you wanted UNION
  • You used INTERSECT

Answer: You used UNION ALL when you wanted UNION. UNION ALL keeps duplicates; switch to UNION to de-duplicate.

Continue this course