Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Basics & Joins

UNION vs UNION ALL

Easyunionset operationsduplicates

Interview question

What is the difference between UNION and UNION ALL?

How to answer

The short answer

UNION combines results and removes duplicates.

UNION ALL combines results and keeps duplicates.

SQL
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Duplicates are removed.

SQL
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Duplicates remain.

Which is faster?

Usually UNION ALL, because it doesn't need to perform duplicate elimination.

Use

  • UNION → when duplicates must be removed
  • UNION ALL → when you know duplicates are valid or don't care about them

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill