UNION vs UNION ALL

Selectors want one list of all players from batters and bowlers. Ravindra Jadeja and Hardik Pandya are all-rounders — they're in both tables. Should they appear once or twice? That one decision is the difference between UNION and UNION ALL.

Definitions

UNION returns all distinct rows from both queries — rows that appear in both (or repeat within one) are shown only once.

UNION ALL returns all rows from both queries, including duplicates — it simply appends the second result under the first.

• Distinct means compared on all selected columns: ('Hardik Pandya','MI') and ('Hardik Pandya','GT') would be two different rows.
• UNION has to sort or hash the data to find duplicates; UNION ALL does no extra work.

The data

Example — UNION (Jadeja and Pandya appear once)

SELECT name, team FROM batters
UNION
SELECT name, team FROM bowlers
ORDER BY name;

5 + 5 = 10 rows in, 8 rows out — the 2 duplicates were removed.

Example — UNION ALL (duplicates kept)

SELECT name, team FROM batters
UNION ALL
SELECT name, team FROM bowlers;

All 10 rows; Jadeja and Pandya appear twice.

UNION ALL vs UNION — when to use which

1. UNION ALL is faster than UNION because it does not remove duplicates.
2. Use UNION ALL if you are sure there are no duplicates (e.g. data from different seasons with a season column).
3. Use UNION ALL to identify duplicates and data-quality issues — stack the data, then count:

SELECT name, COUNT(*) AS times
FROM (
    SELECT name FROM batters
    UNION ALL
    SELECT name FROM bowlers
) AS all_players
GROUP BY name
HAVING COUNT(*) > 1;

Tip — label where each row came from

Add a constant column so you know the source:

SELECT 'Batter' AS role, name, team FROM batters
UNION ALL
SELECT 'Bowler', name, team FROM bowlers
ORDER BY name;

Now Hardik Pandya shows twice — once as Batter, once as Bowler. Note that with UNION (not ALL) these rows would not be merged either, because the role column makes them different.

Common mistakes

• ❌ Using UNION "by default" on huge tables — pays for a dedup you don't need. ✅ Default to UNION ALL unless you need distinct rows.
• ❌ Expecting UNION to dedupe by name only. ✅ It compares every selected column.
• ❌ Expecting the result in a particular order. ✅ Without ORDER BY, row order is not guaranteed.

Note: SET operators treat NULLs as equal when removing duplicates — two rows (NULL, 'MI') collapse into one in a UNION, unlike in a JOIN where NULL never matches NULL.

Interview tip

Q: Which is faster, UNION or UNION ALL, and why? A: UNION ALL — it just appends rows. UNION must additionally sort or hash the combined rows to remove duplicates.