SQL Union All

SQL UNION ALL

The UNION ALL operator combines the result sets of two or more SELECT statements. Unlike UNION, it does NOT remove duplicate rows.

Basic Syntax

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

Example: Combine Customers and Suppliers

This query returns all contact names from both tables, including duplicates:

SELECT name AS contact_name, city
FROM customers
UNION ALL
SELECT name AS contact_name, city
FROM suppliers;

UNION vs UNION ALL

• UNION removes duplicate rows
• UNION ALL keeps duplicate rows

Performance Note

UNION ALL is generally faster than UNION because it does not need to check and remove duplicates.

Using ORDER BY

As with UNION, ORDER BY must appear at the end of the combined query.

SELECT name AS contact_name, city
FROM customers
UNION ALL
SELECT name AS contact_name, city
FROM suppliers
ORDER BY contact_name;

When to Use UNION ALL

• When duplicates are acceptable or expected
• When performance is important
• When combining large datasets

Common Mistakes

• Using UNION when duplicates are needed
• Placing ORDER BY inside the first SELECT instead of at the end
• Combining incompatible column types

Next Step

Continue with SQL GROUP BY to learn how to group rows and calculate summaries per category.