UNION and UNION ALL in MySQL - Deep Dive

Overview - UNION and UNION ALL

What is it?

UNION and UNION ALL are commands in SQL used to combine the results of two or more SELECT queries into a single result set. UNION removes duplicate rows, showing only unique records, while UNION ALL includes all rows, even duplicates. These commands help gather data from multiple tables or queries into one list.

Why it matters

Without UNION and UNION ALL, combining data from different sources would require complex manual merging or multiple queries, making data analysis slow and error-prone. These commands simplify data retrieval, saving time and reducing mistakes when working with related data spread across tables.

Where it fits

Before learning UNION and UNION ALL, you should understand basic SELECT queries and how to filter data. After mastering these, you can explore JOINs to combine tables side-by-side and advanced set operations like INTERSECT and EXCEPT for more complex data comparisons.

Mental Model

Core Idea

UNION and UNION ALL stack query results vertically, with UNION removing duplicates and UNION ALL keeping all rows.

Think of it like...

Imagine you have two guest lists for a party. UNION is like combining the lists but crossing out any names that appear twice, so each guest is invited once. UNION ALL is like putting both lists together without crossing out duplicates, so some guests might get two invitations.

┌───────────────┐       ┌───────────────┐
│ SELECT Query1 │       │ SELECT Query2 │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │                       │
       ▼                       ▼
  ┌─────────────────────────────────┐
  │ UNION or UNION ALL combines rows │
  └──────────────┬──────────────────┘
                 │
       ┌─────────┴─────────┐
       │                   │
   UNION: removes       UNION ALL:
   duplicate rows       keeps all rows
       │                   │
       ▼                   ▼
  Result set          Result set
  with unique         with duplicates
  rows only           included

Build-Up - 7 Steps

1

Foundation

Basic SELECT Query Review

Concept: Understanding how to retrieve data from a single table using SELECT.

A SELECT query asks the database to show certain columns from a table. For example, SELECT name FROM employees; shows all employee names. This is the starting point before combining results from multiple queries.

Result

A list of names from the employees table.

Knowing how SELECT works is essential because UNION and UNION ALL combine the results of multiple SELECT queries.

2

Foundation

Combining Two Queries with UNION

Concept: Using UNION to merge two SELECT queries and remove duplicate rows.

If you have two SELECT queries, like one listing employees from department A and another from department B, you can combine them with UNION: SELECT name FROM employees WHERE dept = 'A' UNION SELECT name FROM employees WHERE dept = 'B'; This shows all unique employee names from both departments.

Result

A list of employee names from both departments without duplicates.

UNION helps create a single list from multiple sources while avoiding repeated entries.

3

Intermediate

Difference Between UNION and UNION ALL

🤔Before reading on: do you think UNION ALL removes duplicates like UNION, or keeps all rows including duplicates? Commit to your answer.

Concept: Understanding that UNION ALL keeps duplicates while UNION removes them.

UNION ALL combines results from queries but does not remove duplicates. For example, if an employee appears in both department lists, UNION ALL will show their name twice. This is faster because it skips the step of checking for duplicates.

Result

A combined list including all rows, even repeated ones.

Knowing when to use UNION ALL can improve performance and preserve all data, which is important for accurate counts or logs.

4

Intermediate

Column Compatibility in UNION Queries

🤔Before reading on: do you think the SELECT queries combined by UNION must have the same number of columns and types, or can they differ? Commit to your answer.

Concept: UNION requires each SELECT to have the same number of columns with compatible data types.

When using UNION or UNION ALL, each SELECT must return the same number of columns, and the columns must be similar types (like text with text, numbers with numbers). For example, SELECT name, age FROM employees UNION SELECT name, age FROM managers; works, but SELECT name FROM employees UNION SELECT salary FROM managers; may cause errors if types differ.

Result

Successful combination of queries with matching columns; error if columns mismatch.

Understanding column compatibility prevents errors and ensures meaningful combined results.

5

Intermediate

Ordering Results After UNION

Concept: How to sort the combined result set using ORDER BY after UNION or UNION ALL.

You cannot put ORDER BY inside each SELECT in a UNION. Instead, place ORDER BY after the last SELECT to sort the entire combined result. For example: SELECT name FROM employees UNION SELECT name FROM managers ORDER BY name ASC; sorts all names alphabetically.

Result

A sorted list of unique names from both queries.

Knowing where to place ORDER BY helps control the final output order after combining queries.

6

Advanced

Performance Considerations Between UNION Types

🤔Before reading on: do you think UNION or UNION ALL is generally faster? Commit to your answer.

Concept: UNION ALL is faster because it skips duplicate removal, which costs extra processing.

UNION removes duplicates by sorting or hashing results, which takes time and memory. UNION ALL simply appends results, so it is faster and uses fewer resources. Use UNION ALL when you know duplicates are not a problem or when duplicates are meaningful.

Result

Faster query execution with UNION ALL compared to UNION.

Understanding performance differences helps write efficient queries for large datasets.

7

Expert

Subtle Effects of NULLs in UNION Operations

🤔Before reading on: do you think NULL values are treated as equal or different when UNION removes duplicates? Commit to your answer.

Concept: UNION treats NULLs as equal when removing duplicates, so rows with NULL in the same columns are considered duplicates.

If two rows differ only by NULL values in some columns, UNION will keep only one of them. This can cause unexpected data loss if NULLs represent unknown or different values. UNION ALL keeps all rows including those with NULLs.

Result

UNION result set with one row for duplicates including NULLs; UNION ALL with all rows.

Knowing how NULLs affect duplicate removal prevents subtle bugs in data analysis.

Under the Hood

When you run a UNION query, the database executes each SELECT separately, then combines the results. For UNION, it sorts or hashes the combined rows to find and remove duplicates before returning the final list. UNION ALL skips this step and simply concatenates the results. Internally, this duplicate removal can involve temporary storage and extra CPU work.

Why designed this way?

UNION was designed to provide a simple way to merge query results while ensuring uniqueness, which is often needed in reporting and data analysis. UNION ALL was added later to offer a faster option when duplicates are acceptable or desired. This separation balances correctness and performance.

┌───────────────┐   ┌───────────────┐
│  SELECT #1    │   │  SELECT #2    │
└──────┬────────┘   └──────┬────────┘
       │                   │
       ▼                   ▼
  ┌─────────────────────────────┐
  │  Combine all rows together   │
  └──────────────┬──────────────┘
                 │
        ┌────────┴─────────┐
        │                  │
    UNION:           UNION ALL:
    Remove duplicates  Keep all rows
    (sort/hash step)   (no extra step)
        │                  │
        ▼                  ▼
  Final unique set    Final full set

Myth Busters - 4 Common Misconceptions

Quick: Does UNION ALL remove duplicate rows or keep them all? Commit to yes or no.

Common Belief: UNION ALL removes duplicates just like UNION.

Reality: UNION ALL keeps all rows, including duplicates, without filtering.

Why it matters: Using UNION ALL when duplicates matter can cause incorrect counts or repeated data in reports.

Quick: Can you put ORDER BY inside each SELECT in a UNION query? Commit to yes or no.

Common Belief: You can sort each SELECT individually inside a UNION query.

Reality: ORDER BY applies only once after all SELECTs are combined, not inside each SELECT.

Why it matters: Trying to order inside each SELECT causes syntax errors or unexpected results.

Quick: Do NULL values count as different when UNION removes duplicates? Commit to yes or no.

Common Belief: Rows with NULLs in the same columns are treated as different and both kept.

Reality: UNION treats NULLs as equal, so duplicates with NULLs are removed.

Why it matters: This can lead to losing rows that seem different but have NULLs, causing data loss.

Quick: Do the SELECT queries combined by UNION need to have the same number of columns? Commit to yes or no.

Common Belief: SELECT queries in UNION can have different numbers of columns.

Reality: All SELECT queries must have the same number of columns with compatible types.

Why it matters: Mismatched columns cause errors and prevent query execution.

Expert Zone

1

UNION's duplicate removal uses sorting or hashing internally, and the choice depends on the database engine and data size.

2

Column names in the final result come from the first SELECT query, which can confuse if later SELECTs have different aliases.

3

Using UNION ALL with DISTINCT on the combined result can mimic UNION but with more control over when duplicates are removed.

When NOT to use

Avoid UNION when you need to combine tables side-by-side; use JOINs instead. Also, if you want to keep duplicates for accurate counts or logs, prefer UNION ALL. For complex set operations like finding common rows, use INTERSECT or EXISTS queries.

Production Patterns

In real systems, UNION ALL is often used for log aggregation where duplicates matter, while UNION is used in reporting to avoid double counting. Developers also use UNION to merge partitioned data or results from different sources into a single view.

Connections

JOIN

JOIN combines tables side-by-side (horizontal), UNION stacks results vertically.

Understanding UNION alongside JOIN helps grasp different ways to combine data: adding rows versus adding columns.

Set Theory

UNION corresponds to the union operation in set theory, combining elements from sets.

Knowing set theory clarifies why UNION removes duplicates and how it relates to mathematical sets.

Data Deduplication in Storage Systems

Both UNION and data deduplication remove repeated data to save space or improve clarity.

Recognizing this connection helps understand the importance and cost of removing duplicates in different fields.

Common Pitfalls

#1 Trying to combine SELECT queries with different numbers of columns.

Wrong approach:

SELECT name FROM employees UNION SELECT name, age FROM managers;

Correct approach:

SELECT name, NULL as age FROM employees UNION SELECT name, age FROM managers;

Root cause:

Misunderstanding that UNION requires matching column counts and compatible types.

#2 Using ORDER BY inside each SELECT in a UNION query.

Wrong approach:

SELECT name FROM employees ORDER BY name UNION SELECT name FROM managers ORDER BY name;

Correct approach:

SELECT name FROM employees UNION SELECT name FROM managers ORDER BY name;

Root cause:

Not knowing that ORDER BY applies only once after UNION combines all results.

#3 Expecting UNION ALL to remove duplicates like UNION.

Wrong approach:

SELECT name FROM employees UNION ALL SELECT name FROM managers; -- expecting unique names only

Correct approach:

SELECT name FROM employees UNION SELECT name FROM managers; -- removes duplicates

Root cause:

Confusing the behavior of UNION ALL with UNION regarding duplicates.

Key Takeaways

UNION and UNION ALL combine results from multiple SELECT queries into one list, stacking rows vertically.

UNION removes duplicate rows to show unique results, while UNION ALL keeps all rows including duplicates.

All SELECT queries combined by UNION or UNION ALL must have the same number of columns with compatible data types.

ORDER BY applies only once after all SELECTs are combined, not inside each individual SELECT.

Choosing between UNION and UNION ALL affects query performance and result accuracy, especially with duplicates and NULL values.

Practice

1. What is the main difference between UNION and UNION ALL in MySQL?

easy

A. UNION sorts the result, UNION ALL does not.

B. UNION keeps all rows including duplicates, UNION ALL removes duplicates.

C. UNION only works with numeric columns, UNION ALL works with all data types.

D. UNION removes duplicate rows, UNION ALL keeps all rows including duplicates.

Solution

1. Step 1: Understand UNION behavior

   UNION combines results from two queries and removes duplicate rows to return only unique rows.

2. Step 2: Understand UNION ALL behavior

   UNION ALL combines results but keeps all rows, including duplicates, without filtering.

3. Final Answer:

   UNION removes duplicate rows, UNION ALL keeps all rows including duplicates. -> Option D

4. Quick Check:

   Duplicates removed = UNION, duplicates kept = UNION ALL [OK]

Hint: Remember: UNION = unique rows, UNION ALL = all rows [OK]

Common Mistakes:

• Thinking UNION ALL removes duplicates
• Confusing sorting behavior with UNION
• Assuming UNION only works on numeric data

2. Which of the following is the correct syntax to combine two SELECT queries using UNION ALL in MySQL?

easy

A. SELECT * FROM table1 UNION ALL SELECT * FROM table2;

B. SELECT * FROM table1 UNIONALL SELECT * FROM table2;

C. SELECT * FROM table1 UNION, ALL SELECT * FROM table2;

D. SELECT * FROM table1 UNION; ALL SELECT * FROM table2;

Solution

1. Step 1: Check correct keyword spacing

   The correct keyword is UNION ALL with a space between UNION and ALL.

2. Step 2: Validate SQL syntax

   The syntax must be: SELECT ... UNION ALL SELECT ...; without commas or semicolons between the two SELECTs.

3. Final Answer:

   SELECT * FROM table1 UNION ALL SELECT * FROM table2; -> Option A

4. Quick Check:

   Correct keyword and syntax = SELECT * FROM table1 UNION ALL SELECT * FROM table2; [OK]

Hint: Use space between UNION and ALL, no commas [OK]

Common Mistakes:

• Writing UNIONALL as one word
• Adding commas between SELECT statements
• Using semicolons between SELECTs

3. Given two tables:
Table A:
id | name
1 | Alice
2 | Bob

Table B:
id | name
2 | Bob
3 | Carol

What will be the result of this query?

SELECT id, name FROM A UNION SELECT id, name FROM B;

medium

A. 2 | Bob3 | Carol

B. 1 | Alice2 | Bob2 | Bob3 | Carol

C. 1 | Alice2 | Bob3 | Carol

D. 1 | Alice3 | Carol

Solution

1. Step 1: Combine rows from both tables

   Table A has (1, Alice) and (2, Bob). Table B has (2, Bob) and (3, Carol).

2. Step 2: Apply UNION behavior

   UNION removes duplicates, so (2, Bob) appears only once in the result.

3. Final Answer:

   1 | Alice
   2 | Bob
   3 | Carol -> Option C

4. Quick Check:

   Duplicates removed, unique rows shown [OK]

Hint: UNION removes duplicates, so repeated rows appear once [OK]

Common Mistakes:

• Including duplicate rows with UNION
• Confusing UNION with UNION ALL output
• Ignoring duplicate row removal

4. Consider this query:

SELECT id FROM table1
UNION ALL
SELECT id FROM table2 ORDER BY id;

What is wrong with this query?

medium

A. ORDER BY must be applied only once at the end of the UNION ALL query.

B. UNION ALL cannot be used with ORDER BY.

C. Each SELECT must have its own ORDER BY clause.

D. ORDER BY should come before UNION ALL.

Solution

1. Step 1: Understand ORDER BY with UNION ALL

   ORDER BY applies to the entire combined result, not to individual SELECTs.

2. Step 2: Identify correct placement

   ORDER BY must be placed after the last SELECT and UNION ALL, not inside one SELECT.

3. Final Answer:

   ORDER BY must be applied only once at the end of the UNION ALL query. -> Option A

4. Quick Check:

   ORDER BY after UNION ALL, not inside SELECT [OK]

Hint: Place ORDER BY after all UNION ALL parts [OK]

Common Mistakes:

• Putting ORDER BY inside one SELECT only
• Trying ORDER BY before UNION ALL
• Assuming UNION ALL disallows ORDER BY

5. You have two tables:
Employees:
id | name
1 | John
2 | Jane
3 | Mike

Contractors:
id | name
2 | Jane
4 | Anna

You want to list all people from both tables, including duplicates, and sort by name ascending.
Which query achieves this correctly?

hard

A. SELECT id, name FROM Employees UNION SELECT id, name FROM Contractors ORDER BY name ASC;

B. SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC;

C. SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY id ASC;

D. SELECT id, name FROM Employees UNION ALL ORDER BY name ASC SELECT id, name FROM Contractors;

Solution

1. Step 1: Choose UNION ALL to keep duplicates

   Since duplicates like Jane (id 2) should appear twice, use UNION ALL.

2. Step 2: Apply ORDER BY after UNION ALL

   ORDER BY must come after the full UNION ALL to sort combined results by name ascending.

3. Final Answer:

   SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC; -> Option B

4. Quick Check:

   UNION ALL + ORDER BY at end = SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC; [OK]

Hint: Use UNION ALL for duplicates, ORDER BY after all SELECTs [OK]

Common Mistakes:

• Using UNION instead of UNION ALL (removes duplicates)
• Placing ORDER BY inside one SELECT
• Incorrect syntax with ORDER BY placement