Merging Query Results

Lesson 0010 — UNION, UNION ALL & Intersection Logic — ~12 min read

Combining results is not just appending. UNION deduplicates, UNION ALL preserves, and intersection is logic — group and count — when no direct command exists.

UNION is one of those topics that triggers school memories: set theory, merging lists, intersection. That familiarity is comforting, and also dangerous — most learners treat UNION like a simple “add these two tables” button. SQL isn’t only about combining data; it’s about combining with meaning intact.

UNION — Combine and Deduplicate

UNION combines results from two or more SELECT statements and removes duplicate rows.

When you run UNION, you’re not saying “append table B to table A.” You’re saying “create one combined result set, and ensure rows are unique.” That means UNION does two jobs:

1. Concatenation of results
2. Duplicate elimination

Superhero example:

• Table A: Iron Man, Spider-Man
• Table B: Spider-Man, Thor

-- UNION removes duplicate row
SELECT hero FROM heroes_a
UNION
SELECT hero FROM heroes_b;

SELECT hero FROM heroes_a
UNION
SELECT hero FROM heroes_b;

• Iron Man appears once
• Spider-Man appears once (duplicate removed)
• Thor appears once

Key: SQL decides duplicates by comparing the entire row — not just name or ID — position-by-position.

UNION ALL — Merge Without Deduplication

UNION ALL merges results but does not remove duplicates — so it is faster. Duplicate elimination is work (row comparison and filtering).

Use UNION ALL when:

• You want every row preserved
• Duplicates are meaningful
• You care about speed and will handle uniqueness later

Same tables, UNION ALL:

SELECT hero FROM heroes_a
UNION ALL
SELECT hero FROM heroes_b;

SELECT hero FROM heroes_a
UNION ALL
SELECT hero FROM heroes_b;

• Iron Man — once
• Spider-Man — twice (duplicates kept)
• Thor — once

UNION ALL does not check for duplicates — no comparison, no filtering, just appends.

Difference Between UNION and UNION ALL

Intersection — No Direct Command (MySQL Workaround)

There is no straightforward INTERSECTION in older MySQL the way UNION exists. If you want intersection, you must build it — and this is where SQL becomes a thinking chapter.

Idea:

1. Combine all values (UNION ALL)
2. Group them
3. Count appearances
4. Anything with count > 1 is intersection

That’s the SQL mindset: if no direct command, express the result through logic.

-- Intersection via GROUP BY + HAVING
SELECT hero, COUNT(*) AS cnt
FROM (
  SELECT hero FROM heroes_a
  UNION ALL
  SELECT hero FROM heroes_b
) AS combined
GROUP BY hero
HAVING COUNT(*) > 1;

-- PostgreSQL has INTERSECT directly
SELECT hero FROM heroes_a
INTERSECT
SELECT hero FROM heroes_b;

-- Or same workaround as SQL:
SELECT hero FROM (
  SELECT hero FROM heroes_a UNION ALL SELECT hero FROM heroes_b
) t GROUP BY hero HAVING COUNT(*) > 1;

Only Spider-Man appears in both — intersection is count > 1. For multi-column rows, GROUP BY compares the entire row.

Notes

Primary sources: MySQL UNION · Postgres UNION/INTERSECT. Ask me anything that's unclear.

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.