Combining Results with UNION & UNION ALL0%

Combining Results with UNION & UNION ALL

Intermediate12 min read•Updated: 2026-09-12

Combining Result Sets with UNION vs UNION ALL

While SQL JOINs combine columns horizontally from multiple tables based on a relational key, SQL Set Operations combine rows vertically from multiple queries into a single unified result set. The most fundamental set operator in SQL is UNION.

1. The Mathematical Difference: `UNION` vs `UNION ALL`

 Query A Results: { Delhi, Mumbai, Pune }
 Query B Results: { Mumbai, Bangalore, Pune }
3 
 UNION (Deduplicated Mathematical Set):
 ===> { Bangalore, Delhi, Mumbai, Pune } (Duplicates removed via sorting/hashing!)
6 
 UNION ALL (Simple Concatenation):
 ===> { Delhi, Mumbai, Pune, Mumbai, Bangalore, Pune } (Keeps all duplicates! FAST!)

Feature	`UNION`	`UNION ALL`
Duplicate Rows	Removed (Performs automatic deduplication).	Retained (Keeps all duplicate occurrences).
Performance	Slower (Requires sorting or temporary hash table).	Significantly Faster (Simple row streaming).
Memory Consumption	Higher (Buffers all rows in RAM for deduplication).	Minimal (Streams rows directly to the client).
Primary Use Case	When unique distinct records are strictly required.	When datasets are known to be disjoint or duplicates are valid.

2. Syntax & Structural Rules of UNION

1SELECT column_1, column_2, ... FROM table_A
2UNION [ALL]
3SELECT column_1, column_2, ... FROM table_B;

The 3 Golden Rules of Set Operations:

1
Identical Column Count: Both SELECT queries must have the exact same number of columns!
2
Compatible Data Types: Columns in corresponding positions must have compatible data types (e.g., string to string, date to date).
3
Column Headers Determined by First Query: The column aliases and titles displayed in the final output grid are taken exclusively from the first SELECT query!

3. Practical Production Examples

Example 1: Unified Customer & Supplier Directory

Suppose your company has a retail_customers table and a wholesale_suppliers table:

1-- Combine all contacts into a single unified mailing list:
2SELECT 
3    company_name AS contact_name,
4    contact_email AS email,
5    'Supplier' AS entity_type
6FROM wholesale_suppliers
7 
8UNION ALL
9 
10SELECT 
11    full_name AS contact_name,
12    email,
13    'Customer' AS entity_type
14FROM retail_customers
15ORDER BY contact_name ASC;

Note

[!NOTE] When applying an ORDER BY clause to a UNION query, place a single ORDER BY at the very end of the entire statement. It sorts the final unified result set!

4. Emulating Archival Data Aggregations

In enterprise databases, historical data is often partitioned into active and archive tables:

1-- Calculate total sales across active and archived orders:
2SELECT order_id, total_amount, 'Active' AS source FROM orders_2026
3UNION ALL
4SELECT order_id, total_amount, 'Archive' AS source FROM orders_2025
5UNION ALL
6SELECT order_id, total_amount, 'Archive' AS source FROM orders_2024;

5. Best Practices & Common Pitfalls

Default to UNION ALL Unless Deduplication is Explicitly Required: 90% of junior developers write UNION by default when they actually mean UNION ALL. On large datasets (millions of rows), running UNION forces MySQL to create a giant temporary disk table to execute an expensive deduplication pass!
Data Type Alignment: If Query 1 has INT, VARCHAR and Query 2 has VARCHAR, INT, MySQL will attempt implicit type conversion, which can cause subtle truncation errors or slow down execution. Always align column types manually.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental difference between UNION and UNION ALL in SQL?

Question 2

Which query operator should be preferred for maximum query performance when combining two datasets that are known to have zero overlapping rows?

Question 3

Which of the following is a mandatory structural rule when combining queries using UNION?

Question 4

Which SELECT query determines the column header labels in the final result set of a UNION operation?

Question 5

Where must the ORDER BY clause be placed when sorting the combined output of a UNION query?

Next Lesson

Emulating INTERSECT in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SELF JOIN for Hierarchical Structures	Emulating INTERSECT in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Emulating INTERSECT in MySQL

Combining Results with UNION & UNION ALL

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Combining Result Sets with UNION vs UNION ALL

1. The Mathematical Difference: `UNION` vs `UNION ALL`

 Query A Results: { Delhi, Mumbai, Pune }
 Query B Results: { Mumbai, Bangalore, Pune }
3 
 UNION (Deduplicated Mathematical Set):
 ===> { Bangalore, Delhi, Mumbai, Pune } (Duplicates removed via sorting/hashing!)
6 
 UNION ALL (Simple Concatenation):
 ===> { Delhi, Mumbai, Pune, Mumbai, Bangalore, Pune } (Keeps all duplicates! FAST!)

Feature	`UNION`	`UNION ALL`
Duplicate Rows	Removed (Performs automatic deduplication).	Retained (Keeps all duplicate occurrences).
Performance	Slower (Requires sorting or temporary hash table).	Significantly Faster (Simple row streaming).
Memory Consumption	Higher (Buffers all rows in RAM for deduplication).	Minimal (Streams rows directly to the client).
Primary Use Case	When unique distinct records are strictly required.	When datasets are known to be disjoint or duplicates are valid.

2. Syntax & Structural Rules of UNION

1SELECT column_1, column_2, ... FROM table_A
2UNION [ALL]
3SELECT column_1, column_2, ... FROM table_B;

The 3 Golden Rules of Set Operations:

1
Identical Column Count: Both SELECT queries must have the exact same number of columns!
2
Compatible Data Types: Columns in corresponding positions must have compatible data types (e.g., string to string, date to date).
3
Column Headers Determined by First Query: The column aliases and titles displayed in the final output grid are taken exclusively from the first SELECT query!

3. Practical Production Examples

Example 1: Unified Customer & Supplier Directory

Suppose your company has a retail_customers table and a wholesale_suppliers table:

1-- Combine all contacts into a single unified mailing list:
2SELECT 
3    company_name AS contact_name,
4    contact_email AS email,
5    'Supplier' AS entity_type
6FROM wholesale_suppliers
7 
8UNION ALL
9 
10SELECT 
11    full_name AS contact_name,
12    email,
13    'Customer' AS entity_type
14FROM retail_customers
15ORDER BY contact_name ASC;

Note

[!NOTE] When applying an ORDER BY clause to a UNION query, place a single ORDER BY at the very end of the entire statement. It sorts the final unified result set!

4. Emulating Archival Data Aggregations

In enterprise databases, historical data is often partitioned into active and archive tables:

1-- Calculate total sales across active and archived orders:
2SELECT order_id, total_amount, 'Active' AS source FROM orders_2026
3UNION ALL
4SELECT order_id, total_amount, 'Archive' AS source FROM orders_2025
5UNION ALL
6SELECT order_id, total_amount, 'Archive' AS source FROM orders_2024;

5. Best Practices & Common Pitfalls

Default to UNION ALL Unless Deduplication is Explicitly Required: 90% of junior developers write UNION by default when they actually mean UNION ALL. On large datasets (millions of rows), running UNION forces MySQL to create a giant temporary disk table to execute an expensive deduplication pass!
Data Type Alignment: If Query 1 has INT, VARCHAR and Query 2 has VARCHAR, INT, MySQL will attempt implicit type conversion, which can cause subtle truncation errors or slow down execution. Always align column types manually.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental difference between UNION and UNION ALL in SQL?

Question 2

Which query operator should be preferred for maximum query performance when combining two datasets that are known to have zero overlapping rows?

Question 3

Which of the following is a mandatory structural rule when combining queries using UNION?

Question 4

Which SELECT query determines the column header labels in the final result set of a UNION operation?

Question 5

Where must the ORDER BY clause be placed when sorting the combined output of a UNION query?

Next Lesson

Emulating INTERSECT in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SELF JOIN for Hierarchical Structures	Emulating INTERSECT in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Combining Result Sets with UNION vs UNION ALL

1. The Mathematical Difference: UNION vs UNION ALL

2. Syntax & Structural Rules of UNION

The 3 Golden Rules of Set Operations:

3. Practical Production Examples

Example 1: Unified Customer & Supplier Directory

4. Emulating Archival Data Aggregations

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Emulating INTERSECT in MySQL

Related Lessons

Practice Quiz

Loading Page Content...

Combining Result Sets with UNION vs UNION ALL

1. The Mathematical Difference: UNION vs UNION ALL

2. Syntax & Structural Rules of UNION

The 3 Golden Rules of Set Operations:

3. Practical Production Examples

Example 1: Unified Customer & Supplier Directory

4. Emulating Archival Data Aggregations

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Emulating INTERSECT in MySQL

Related Lessons

Practice Quiz

1. The Mathematical Difference: `UNION` vs `UNION ALL`

1. The Mathematical Difference: `UNION` vs `UNION ALL`