SQL UNION vs UNION ALL: Merge SELECT Results in SQL Like a Pro

SQL gives us tools not just to query data, but also to combine it across tables and filters. The UNION operator helps you merge the results of multiple SELECT statements into one cohesive set. Whether you’re working with partitioned tables or pulling records from various sources, UNION and UNION ALL are essential tools.

How SQL UNION Works

UNION stacks the results of two or more SELECT statements, removing duplicates. UNION ALL keeps everything — even exact matches.

Syntax

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

To include all rows, even duplicates:

SELECT column1 FROM archive_2022
UNION ALL
SELECT column1 FROM archive_2023;

The number and types of columns must match across all SELECT statements.

Real-World Use Cases

• Partitioned Data Merging

Merging rows from time-based partitions:

SELECT * FROM logs_2022
UNION ALL
SELECT * FROM logs_2023;

• Cross-System Sync

SELECT id, name FROM old_db.customers
UNION
SELECT user_id, full_name FROM new_db.clients;

• Ordering, Errors, and Column Naming

• Final result gets column names from the first SELECT.

• Apply ORDER BY after all SELECT queries.

• Mismatched column counts or incompatible types will break the query.

Best Practices

• Use UNION ALL for speed unless duplicates matter.

• Always match column count and compatible types.

• Use aliases to ensure column names are consistent.

• Don’t use ORDER BY inside sub-SELECTs.

• Benchmark performance when dealing with large datasets.

FAQ

When should I use UNION ALL?

Use it when you expect or want duplicates and need better performance.

Can I UNION more than two SELECTs?

Yes. You can chain as many as you like:

SELECT ... FROM table1
UNION
SELECT ... FROM table2
UNION
SELECT ... FROM table3;

What if data types differ?

Use CAST() or CONVERT() to align them. Otherwise, the DBMS may throw an error.

Can UNION be used for deduplication?

Yes — if two queries return overlapping rows, UNION keeps only distinct ones.

Conclusion

Whether you’re querying multiple tables or merging filtered subsets, UNION and UNION ALL are reliable tools. They help keep your queries clean, scalable, and modular.

Read the SQL UNION Operator: How To Combine Result Sets for more info.