SQL UNION and UNION ALL: Combine Query Results (Examples)

SQL Tutorial · Chapter 24 of 32

The SQL UNION operator combines the result sets of two or more SELECT statements into a single list of rows. UNION removes duplicate rows, while UNION ALL keeps every row, including duplicates. Both queries must return the same number of columns with compatible data types, in the same order.

SQL UNION Syntax

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

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

Column names in the final result come from the first SELECT. Any ORDER BY must appear once, at the very end, and applies to the combined result.

Sample Tables

We use the same Customers table as the rest of this course, plus a small Suppliers table with the same kind of columns.

Example 1: UNION Removes Duplicates

List every city where we have either a customer or a supplier. Mumbai and Sydney appear in both tables, but UNION returns each city once.

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Eight source rows become six result rows because the duplicate Mumbai and Sydney entries are collapsed.

Example 2: UNION ALL Keeps Every Row

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

All eight rows are returned. UNION ALL is faster because the database skips the sort-and-deduplicate step, so prefer it whenever duplicates are impossible or acceptable.

Example 3: Label Each Source with a Literal Column

Adding a constant text column tells you which table each row came from. This is a common pattern for building a single contact list.

SELECT Name, City, 'Customer' AS Type FROM Customers
UNION ALL
SELECT Name, City, 'Supplier' AS Type FROM Suppliers
ORDER BY Type, Name;

Example 4: UNION with WHERE Filters

Each SELECT can have its own WHERE clause. Here we list Indian customers together with Australian suppliers.

SELECT Name, Country FROM Customers WHERE Country = 'India'
UNION
SELECT Name, Country FROM Suppliers WHERE Country = 'Australia';

UNION vs JOIN

A JOIN combines tables sideways, adding columns by matching rows on a key. UNION stacks results vertically, adding rows. Use JOIN when tables are related by a key; use UNION when two tables hold the same kind of information.

Works in MySQL, SQL Server, PostgreSQL, SQLite

UNION and UNION ALL behave identically in all four databases. Related set operators differ: INTERSECT and EXCEPT work in SQL Server, PostgreSQL and SQLite, and MySQL added them in version 8.0.31. Oracle uses MINUS instead of EXCEPT. In MySQL, wrap each SELECT in parentheses if you need a per-query LIMIT.

Common Mistakes

• Column count mismatch: every SELECT must return the same number of columns, otherwise you get an error such as “each UNION query must have the same number of columns”.
• Incompatible types: pairing a text column with a date column fails or produces implicit conversions. Use CAST to align types.
• Using UNION when UNION ALL is meant: UNION silently drops legitimate duplicate rows, which can under-count totals.
• ORDER BY in each SELECT: only the final ORDER BY is allowed; sort the combined result once.

Practice Exercise

1. Return a single list of all distinct countries from both tables, sorted alphabetically.
2. Count how many rows a UNION ALL of both Name columns would return, using a subquery.

-- 1
SELECT Country FROM Customers
UNION
SELECT Country FROM Suppliers
ORDER BY Country;
-- India, UK, Spain, Singapore, Australia, Germany (6 rows)

-- 2
SELECT COUNT(*) AS TotalNames FROM (
  SELECT Name FROM Customers
  UNION ALL
  SELECT Name FROM Suppliers
) AS AllNames;
-- TotalNames = 8

Related Chapters

• SQL FULL JOIN – combine all rows from two related tables side by side
• SQL SELECT DISTINCT – the deduplication UNION performs automatically
• SQL INSERT INTO – next chapter, adding rows to a table
• SQL from Scratch: full course overview

FAQ

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result; UNION ALL keeps every row. UNION ALL is faster because no deduplication is needed.

Can I use UNION on tables with different column names?

Yes. Only the number of columns and their data types must match. The result uses the column names from the first SELECT statement.

How do I sort a UNION result?

Put one ORDER BY clause at the very end of the whole statement. It sorts the combined rows and can reference column names or aliases from the first SELECT.

Chapter 24 of 32 · SQL from Scratch: all 32 chapters