UNION ALL with duplicates in SQL - Step-by-Step Execution

Concept Flow - UNION ALL with duplicates

Start: Two SELECT queries

↓

Execute first SELECT

↓

Execute second SELECT

↓

Combine all rows from both results

↓

Output combined rows including duplicates

↓

End

UNION ALL runs two queries and combines all their rows, keeping duplicates.

Execution Sample

SQL

SELECT name FROM fruits1
UNION ALL
SELECT name FROM fruits2;

This query combines all rows from fruits1 and fruits2 tables, including duplicates.

Execution Table

Step	Action	Query Result	Combined Result
1	Execute SELECT from fruits1	["Apple", "Banana", "Apple"]	["Apple", "Banana", "Apple"]
2	Execute SELECT from fruits2	["Banana", "Cherry"]	["Apple", "Banana", "Apple", "Banana", "Cherry"]
3	Combine results with UNION ALL	N/A	["Apple", "Banana", "Apple", "Banana", "Cherry"]
4	Output combined rows including duplicates	N/A	["Apple", "Banana", "Apple", "Banana", "Cherry"]

💡 All rows from both queries combined; duplicates are kept because UNION ALL does not remove duplicates.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
fruits1_result	empty	["Apple", "Banana", "Apple"]	["Apple", "Banana", "Apple"]	["Apple", "Banana", "Apple"]
fruits2_result	empty	empty	["Banana", "Cherry"]	["Banana", "Cherry"]
combined_result	empty	["Apple", "Banana", "Apple"]	["Apple", "Banana", "Apple", "Banana", "Cherry"]	["Apple", "Banana", "Apple", "Banana", "Cherry"]

Key Moments - 2 Insights

Why do duplicates appear in the final result when using UNION ALL?

UNION ALL combines all rows from both queries without removing duplicates, as shown in execution_table rows 3 and 4.

What is the difference between UNION and UNION ALL regarding duplicates?

UNION removes duplicates by default, but UNION ALL keeps all rows including duplicates, as demonstrated by the combined_result in variable_tracker.

Visual Quiz - 3 Questions

Look at the execution_table, what is the combined_result after Step 2?

A ["Banana", "Cherry"]

B ["Apple", "Banana", "Cherry"]

C ["Apple", "Banana", "Apple", "Banana", "Cherry"]

D ["Apple", "Banana"]

💡 Hint

Check the 'Combined Result' column in execution_table row for Step 2.

At which step does the query combine results from both SELECT statements?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for the step where 'Combine results with UNION ALL' happens in execution_table.

If we replaced UNION ALL with UNION, what would happen to duplicates in the combined_result?

A Duplicates would stay the same

B Duplicates would be removed

C Duplicates would increase

D Query would fail

💡 Hint

Recall the key_moments explanation about UNION vs UNION ALL behavior.

Concept Snapshot

UNION ALL combines results of two queries
Includes all rows from both queries
Duplicates are NOT removed
Syntax: SELECT ... FROM ... UNION ALL SELECT ...
Use when you want all data including duplicates

Full Transcript

UNION ALL runs two SELECT queries and combines all their rows into one result. Unlike UNION, UNION ALL does not remove duplicates, so if the same row appears in both queries, it will appear multiple times in the final output. The execution steps show first running each SELECT, then combining their results by appending all rows together. This is useful when you want to keep every row from both queries without filtering duplicates.