🔗 SQL `UNION` – Combine Results from Multiple Queries

The UNION operator in SQL is used to merge the results of two or more SELECT queries into a single result set, eliminating duplicate rows by default.

It’s an essential SQL concept for data integration, multi-source querying, and building consolidated views across tables.

📘 What is SQL UNION?

UNION is a set operator that combines rows from multiple SELECT statements into one unified result set.

It automatically removes duplicate records unless you use UNION ALL.

🧾 SQL UNION Syntax

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

✅ Rules:

Each SELECT query must have the same number of columns
The data types of corresponding columns must be compatible
Column names in the final result are taken from the first SELECT statement

✅ Example: Combining Employee Tables

Suppose you have two tables:

full_time_employees

name	department
Alice	IT
Bob	HR

contract_employees

name	department
Charlie	IT
Alice	IT

You want a combined list of all employees:

SELECT name, department FROM full_time_employees
UNION
SELECT name, department FROM contract_employees;

🧾 Result:

name	department
Alice	IT
Bob	HR
Charlie	IT

Notice: “Alice” appears only once because UNION removes duplicates.

🔄 Want to Keep Duplicates? Use `UNION ALL`

SELECT name, department FROM full_time_employees
UNION ALL
SELECT name, department FROM contract_employees;

🧾 Result with duplicates:

name	department
Alice	IT
Bob	HR
Charlie	IT
Alice	IT

🧠 When to Use SQL UNION

✅ Combine rows from multiple tables with similar structures
📊 Consolidate historical and current records
📁 Merge data from archived and active sources
💼 Build unified views across systems or timeframes

⚠️ Common Mistakes to Avoid

Mistake	Solution
Mismatched number of columns	Ensure each `SELECT` has the same number of columns
Incompatible column data types	Use `CAST()` or `CONVERT()` to align data types
Assuming duplicate rows are retained	Use `UNION ALL` instead of `UNION`
Forgetting column aliases in final output	Use column aliases in the first query if needed

🧮 Real-Life Use Cases

Combine online vs offline sales reports
Merge support ticket data from multiple systems
Aggregate leads from various marketing sources
Create master lists of users or products from multiple regions

🔍 SQL UNION vs JOIN – What’s the Difference?

Feature	`UNION`	`JOIN`
Rows or columns	Combines rows	Combines columns
Structure	Requires same number of columns	Requires keys to match rows
Use case	Merging similar data from multiple tables	Enriching data with related information

📝 Summary

SQL UNION merges results from multiple queries into one
Removes duplicates by default
Use UNION ALL if you want to retain all rows
Make sure the number and types of columns match
Great for combining reports, records, or views across similar structures

UNION

🔗 SQL UNION – Combine Results from Multiple Queries