Merging Query Results

Part 1: Concept Notes: UNION, UNION ALL, and Intersection

Introduction

What is it? UNION and UNION ALL are SQL's tools for stacking the results of two or more SELECT statements into one combined result set. Instead of joining tables side by side (adding more columns), these operators stack results vertically (adding more rows).

Why do we need it? Real businesses often store related data in more than one table or system. Online orders might live in one table, in-store orders in another. Active users in one table, archived users in another. When you need a single combined report across both sources, UNION and UNION ALL are exactly the tools for stitching those separate result sets back together into one list.

Real-world analogy Picture two different class rosters, one from a morning session and one from an evening session of the same course. If a student attended both sessions, their name appears on both rosters. If the school wants a single master list of "everyone who took this course," with no one listed twice, that's UNION. If the school instead wants an attendance log where every single session is recorded separately (even if the same student shows up twice, once per session), that's UNION ALL.

Business use case A common example is generating a consolidated customer report from multiple regional databases, or building an audit trail where every transaction across multiple systems needs to be visible, duplicates and all, because in an audit, a duplicate might actually be meaningful information (like the same sale accidentally recorded in two systems), not noise to be cleaned away.

This topic comes with a comforting kind of familiarity, since it echoes set theory ideas from school (union, intersection). That familiarity is genuinely useful, but it can also be misleading if you treat UNION like a simple "append these two tables together" button. SQL isn't just stacking data, it's building one combined, meaningful result set, and exactly how it decides what counts as a "duplicate" is the part most learners skip past too quickly.

Dataset Used

This topic uses a simple, memorable dataset throughout: two small lists of superheroes.

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

This dataset is intentionally tiny, its whole purpose is to make the behavior of UNION and UNION ALL easy to see and remember, before applying the same logic to larger, more realistic tables.

Syntax

-- UNION: combines results, removes duplicate rows
SELECT column_list FROM table_a
UNION
SELECT column_list FROM table_b;

-- UNION ALL: combines results, keeps every row, including duplicates
SELECT column_list FROM table_a
UNION ALL
SELECT column_list FROM table_b;

A few strict rules apply to both UNION and UNION ALL:

• Every SELECT statement being combined must return the same number of columns.
• The columns must be in a compatible order and data type (the first column of every SELECT is treated as "the same column" as the first column of every other SELECT, regardless of what it's named).
• The final column names in the combined result come from the first SELECT statement, later SELECTs' column names are ignored for naming purposes.

Mental Model

Picture UNION and UNION ALL as two different photocopier settings.

UNION ALL is the simple setting: it takes every page from Pile A, then every page from Pile B, and staples them all together into one thick stack, in order, without looking at what's actually written on any page. If the exact same page appears in both piles, it simply ends up in the final stack twice, once from each pile.

UNION is the more careful setting: after stapling everything together, it goes back through the entire combined stack and physically removes any page that is an exact, word-for-word duplicate of another page already in the stack, keeping only one copy of each unique page.

This is the core mental model to carry forward: UNION ALL just appends. UNION appends, and then deduplicates.

How SQL Executes It

For UNION ALL, SQL does the simplest possible thing:

1. Run the first SELECT, collect its rows.
2. Run the second SELECT, collect its rows.
3. Append the second result set directly after the first, with no comparison between them at all.

For UNION, SQL does extra work:

1. Run the first SELECT, collect its rows.
2. Run the second SELECT, collect its rows.
3. Combine both sets of rows together.
4. Compare rows against each other, examining the entire row, every column, position by position, not just one "key" column.
5. Remove any row that is a complete, exact duplicate of another row already kept in the result.

This last step is exactly why UNION is slower than UNION ALL, deduplication requires comparing rows against each other, which takes real computational work, especially on large result sets.

Example 1: UNION (Duplicates Removed)

Problem Combine two superhero lists into one list, with no superhero appearing more than once, even if they show up in both lists.

Data

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

SQL Query

SELECT hero_name FROM table_a
UNION
SELECT hero_name FROM table_b;

Output

Explanation Spider-Man appears in both Table A and Table B, but UNION keeps only one occurrence of it in the final combined result. Iron Man and Thor, each appearing in only one of the two tables, show up exactly once as well, the same as they would have with UNION ALL.

Why it works UNION's whole job is to guarantee that the final combined result set contains no exact duplicate rows. Here, since "Spider-Man" is a full match between the two tables (the entire row is identical, there's only one column being compared in this simple example), it gets collapsed down to a single appearance.

Example 2: UNION ALL (Duplicates Kept)

Problem Combine the exact same two superhero lists, but this time, preserve every single row, even ones that are exact duplicates of each other.

Data

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

SQL Query

SELECT hero_name FROM table_a
UNION ALL
SELECT hero_name FROM table_b;

Output

Explanation This time, Spider-Man appears twice, once from Table A, and once from Table B, since UNION ALL never checks for duplicates at all. It simply stacks both result sets together, exactly as they are.

Why it works UNION ALL performs no row comparison whatsoever, no "is this already present?" check happens anywhere. It just appends rows from the second SELECT directly after the rows from the first SELECT. This is precisely why it's faster: there's simply less work being done.

Deep Dive

Internal behavior: how SQL decides what counts as a "duplicate"

This is the single most important, and most commonly misunderstood, detail about UNION. SQL does not decide duplicates by looking at "just the name" or "just the ID," it compares the entire row, every single column, position by position. Two rows only count as duplicates if every corresponding column value matches exactly.

This matters enormously in real, multi-column tables. Consider two order rows:

Even though both rows share the same order_id (3) and the same order_date, UNION would not treat these as duplicates, because the amount column differs (700 vs 750). Both rows would be kept in a UNION's result, since as full rows, they are not identical. This is why the earlier superhero examples used a single column, to keep the "full row comparison" idea simple and visible, but the same exact-row-matching rule applies no matter how many columns are involved.

NULL handling When comparing rows for duplicates, most databases (including MySQL) treat two NULLs in the same column position as equal to each other for the purposes of UNION's deduplication, even though NULL = NULL would normally evaluate to UNKNOWN in a regular WHERE comparison (as covered in the CASE Expression notes). This is a special case: UNION's deduplication logic behaves more like IS NOT DISTINCT FROM than a plain equality check.

Performance considerations UNION requires the database to sort or hash the combined result set internally in order to detect and remove duplicates, this is genuine extra computational work. UNION ALL skips this step entirely, making it meaningfully faster, especially on large result sets. As a rule of thumb: if you already know there are no duplicates between your sources (for example, if the two tables can never share an overlapping primary key), or if duplicates are actually meaningful to you and shouldn't be removed, always prefer UNION ALL for the performance benefit.

Database behavior The column-matching and row-comparison rules for UNION are part of the SQL standard and behave consistently across MySQL, PostgreSQL, SQL Server, and other major databases. However, the syntax for column aliasing in the final result (which SELECT's column names "win") is consistent too: the first SELECT statement's column names define the output column names for the entire combined result set, regardless of what later SELECT statements name their columns.

Intersection: Values Present in Both Sources

MySQL supports the INTERSECT set operator from version 8.0.31. On that version or newer, the direct form is the clearest way to return rows present in both query results:

SELECT hero_name FROM table_a
INTERSECT
SELECT hero_name FROM table_b;

On older MySQL versions, use a source-aware pattern such as EXISTS or an INNER JOIN. This version returns each matching hero once even if either table contains internal duplicates:

SELECT DISTINCT a.hero_name
FROM table_a AS a
WHERE EXISTS (
    SELECT 1
    FROM table_b AS b
    WHERE b.hero_name = a.hero_name
);

A common workaround combines both sources with UNION ALL, groups the result, and filters with HAVING COUNT(*) > 1. That is only safe when each source is already unique on the compared columns. If Table A contains Spider-Man twice and Table B contains none, a plain count greater than one would incorrectly call it an intersection. The source-aware query above does not have that problem.

Version distinction: use INTERSECT on MySQL 8.0.31 or newer. For older MySQL versions, use EXISTS or INNER JOIN and deduplicate deliberately when set semantics are required.

UNION vs. UNION ALL: Quick Comparison

Real-world Use Cases

• Consolidated user reports: Merging active and archived user tables into one master list, as seen in Problem 1
• Multi-channel sales or order reporting: Combining online and in-store transaction tables into a single unified report, as seen in Problem 2
• Auditing and compliance: Deliberately preserving every record, including duplicates, from multiple source systems, since a duplicate itself might be the exact anomaly an auditor is looking for, as seen in Problem 3
• Reshaping wide data into long data (unpivoting): Converting a table where each column represents a different category (like separate store columns) into a normalized row-per-category format, as seen in Problem 4
• Building an intersection when the database doesn't support INTERSECT directly: Using UNION ALL combined with GROUP BY and HAVING to find values common to multiple tables

Review Tips

Interviewers commonly test this topic alongside these ideas:

• "What's the exact difference between UNION and UNION ALL?" — Tests the core concept: deduplication versus simple concatenation, and the resulting performance difference.
• "Does UNION remove duplicates based on a primary key, or something else?" — Tests understanding that UNION compares the entire row, not just one column.
• "How would you find the intersection of two tables without INTERSECT?" — Tests a source-aware EXISTS or INNER JOIN approach.
• "Why might UNION ALL be preferred over UNION in a reporting pipeline, even if it could theoretically produce duplicate rows?" — Tests understanding of when duplicates are actually meaningful (like in audits) versus when they're noise to be removed.
• "What requirements must both SELECT statements satisfy to be combined with UNION?" — Tests knowledge of the matching column count and compatible data type rules.

A strong answer to any UNION question should explicitly mention that deduplication compares the whole row, not a single identifying column, since this is the detail that separates a surface-level understanding from a deeper one.

Common Mistakes to Avoid

Mistake 1: Assuming UNION deduplicates based on just one column, like an ID

Why it happens: In many real tables, the first column often is a unique ID, so people sometimes assume that's specifically what UNION checks.

Why it matters: UNION compares the entire row. Two rows with the same ID but a different value in any other column are not considered duplicates, and both will be kept in a UNION result.

Correct understanding: If you need to deduplicate based on a subset of columns (like just an ID, ignoring other differences), UNION alone won't do that, you'd need a different approach, like grouping by that specific column and picking one representative row per group.

Mistake 2: Using UNION when UNION ALL was actually intended

Why it happens: UNION is often taught first, so it becomes the automatic default choice, even in situations where preserving duplicates was actually the goal.

Wrong (for an audit requiring every record preserved):

SELECT sale_id, sale_date, amount FROM DomesticSales
UNION
SELECT sale_id, sale_date, amount FROM InternationalSales;

This would silently collapse a genuinely duplicated sale record down to a single row, potentially hiding exactly the kind of duplicate entry an audit is trying to catch.

Correct:

SELECT sale_id, sale_date, amount FROM DomesticSales
UNION ALL
SELECT sale_id, sale_date, amount FROM InternationalSales;

Mistake 3: Mismatched column counts or incompatible data types between SELECT statements

Why it happens: It's easy to forget that every SELECT being combined with UNION or UNION ALL must return exactly the same number of columns, in a compatible order.

Wrong:

SELECT user_id FROM ActiveUsers
UNION
SELECT user_id, status FROM ArchivedUsers;

This fails, since the first SELECT returns 1 column, and the second returns 2, the database cannot line these up correctly.

Correct: Always ensure both SELECT statements return the same number of columns, in the same logical order, even if the column names differ between the two SELECTs (the final result uses the first SELECT's column names regardless).

Mistake 4: Forgetting that ORDER BY only needs to appear once, at the very end

Why it happens: It might seem like each individual SELECT statement in a UNION chain should have its own ORDER BY.

Wrong (and often a syntax error, depending on the database):

SELECT order_id, order_date, amount FROM OnlineOrders ORDER BY amount DESC
UNION
SELECT order_id, order_date, amount FROM StoreOrders ORDER BY amount DESC;

Correct: Only one ORDER BY is needed, applied to the entire combined result set, placed after the final SELECT in the chain:

SELECT order_id, order_date, amount FROM OnlineOrders
UNION
SELECT order_id, order_date, amount FROM StoreOrders
ORDER BY amount DESC;

Mistake 5: Inferring intersection from duplicate counts without tracking the source

Why it happens: combining values with UNION ALL and filtering with HAVING COUNT(*) > 1 looks compact.

Why it fails: duplicate rows inside only one source can make the count greater than one even when the value is absent from the other source.

Correct: on MySQL 8.0.31 or newer, use INTERSECT. On older versions, use EXISTS or INNER JOIN so the logic explicitly requires a match from both sources.

Key Takeaways

• UNION combines two or more SELECT statements into one result set, and removes exact duplicate rows
• UNION ALL does the same combining, but keeps every row, including duplicates, and is faster since it skips deduplication entirely
• UNION decides duplicates by comparing the entire row, every column, not just a single ID or name column
• Choose UNION ALL by default when duplicates are either impossible (no overlap between sources) or meaningful (like in audits), and reserve UNION specifically for when a truly unique combined list is required
• Both SELECT statements in a UNION or UNION ALL must return the same number of columns, in a compatible, matching order
• The final combined result's column names come from the first SELECT statement in the chain
• MySQL supports INTERSECT from 8.0.31; older versions can use a source-aware EXISTS or INNER JOIN pattern
• Do not infer intersection from COUNT(*) > 1 unless each source is unique on the compared columns; internal duplicates make that shortcut unsafe
• ORDER BY, when used with UNION or UNION ALL, is written only once, at the very end of the entire combined query, applying to the full combined result set