UNION & UNION ALL

Often in SQL, we need to combine results from multiple SELECT queries into a single result set.

UNION and UNION ALL are used exactly for this purpose.

What is UNION?

The UNION operator combines the result of two or more SELECT statements into one result set.

Important behavior of UNION:

• Removes duplicate rows
• Combines results vertically (row-wise)
• Each SELECT must return the same structure

Basic UNION Syntax

SELECT column1, column2
FROM table1

UNION

SELECT column1, column2
FROM table2;
  

Rules for Using UNION

All SELECT statements must:

• Have the same number of columns
• Have compatible data types
• Return columns in the same order

Column names in the final output come from the first SELECT.

Example Tables

online_customers

+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
+----+----------+
  

store_customers

+----+----------+
| id | name     |
+----+----------+
| 3  | Charlie  |
| 2  | Bob      |
+----+----------+
  

UNION Example

Combine customer names from both tables.

SELECT name FROM online_customers
UNION
SELECT name FROM store_customers;
  

Result:

• Alice
• Bob
• Charlie

Bob appears only once because UNION removes duplicates.

What is UNION ALL?

UNION ALL also combines results, but it does not remove duplicates.

This makes UNION ALL:

• Faster than UNION
• Useful when duplicates matter
• Better for large datasets

UNION ALL Syntax

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
  

UNION ALL Example

SELECT name FROM online_customers
UNION ALL
SELECT name FROM store_customers;
  

Result:

• Alice
• Bob
• Charlie
• Bob

Duplicates are preserved.

UNION vs UNION ALL

Using ORDER BY with UNION

ORDER BY must be applied at the end of the entire UNION query.

SELECT name FROM online_customers
UNION
SELECT name FROM store_customers
ORDER BY name;
  

Common Beginner Mistakes

• Using ORDER BY inside individual SELECTs
• Mismatched column counts
• Confusing UNION with JOIN
• Using UNION when UNION ALL is needed

UNION vs JOIN (Important)

UNION combines rows from different queries.

JOIN combines columns from related tables.

They solve completely different problems.

When Should You Use UNION?

Use UNION when:

• Tables have similar structure
• You need combined results
• Duplicates should be removed

Use UNION ALL when performance matters and duplicates are acceptable.

What’s Next?

In the next lesson, we will explore Indexes, which help speed up SQL queries.