UNION and UNION ALL

Set operations let you combine the result sets of two or more SELECT queries. Unlike JOINs, which combine columns side by side, set operations stack rows vertically -- appending one result set on top of another.

UNION: Combine and Deduplicate

UNION takes the results of two queries and stacks them together, removing duplicate rows:

Query A Result:       Query B Result:       UNION Result:
+-------+            +-------+             +-------+
| Alice |            | Alice |             | Alice |  (deduplicated)
| Bob   |            | Carol |             | Bob   |
| Carol |            | Diana |             | Carol |  (deduplicated)
+-------+            +-------+             | Diana |
                                           +-------+

Syntax

SELECT columns FROM table_a
UNION
SELECT columns FROM table_b;

Rules

Both SELECT statements must:

1. Return the same number of columns
2. Have compatible data types in corresponding column positions

The column names in the final result come from the first SELECT statement.

Basic Example: Combined Name List

Combine employee names and customer names into a single list:

SELECT name FROM employees
UNION
SELECT name FROM customers;

This returns every unique name that appears in either the employees or customers table. If someone is both an employee and a customer, their name appears only once.

UNION ALL: Combine Without Deduplication

UNION ALL stacks the result sets together but keeps all rows, including duplicates:

SELECT name FROM employees
UNION ALL
SELECT name FROM customers;

If "Alice" appears in both tables, she will appear twice in the results.

When to Use UNION vs UNION ALL

UNION ALL is faster because it skips the deduplication step (which requires sorting or hashing). Always prefer UNION ALL when you know there are no duplicates or when duplicates are acceptable.

Column Matching

The columns are matched by position, not by name:

-- Column 1 of each query is combined, Column 2 of each query is combined
SELECT name, email FROM employees
UNION
SELECT name, email FROM customers;

If the column names differ, the first query's names are used:

SELECT name AS person_name, email AS contact_email FROM employees
UNION
SELECT name, email FROM customers;
-- Result columns will be: person_name, contact_email

Be careful with position matching. This is valid SQL but produces nonsensical results:

-- DON'T DO THIS: name is combined with email!
SELECT name, email FROM employees
UNION
SELECT email, name FROM customers;  -- columns are swapped!

Adding a Source Column

When combining data from multiple tables, it is often useful to add a column identifying where each row came from:

SELECT name, email, 'employee' AS source
FROM employees
UNION ALL
SELECT name, email, 'customer' AS source
FROM customers
ORDER BY name;

This makes it easy to see whether each person is an employee, a customer, or both (if using UNION ALL).

Using UNION with Different Filters

UNION is great for combining different subsets of data:

-- High earners OR Engineering department
SELECT name, department, salary, 'high earner' AS category
FROM employees
WHERE salary > 85000

UNION

SELECT name, department, salary, 'engineering' AS category
FROM employees
WHERE department = 'Engineering';

An employee who is both a high earner AND in Engineering will appear only once (because UNION deduplicates). Use UNION ALL if you want them to appear in both categories.

UNION with ORDER BY

You can only have one ORDER BY clause, and it must come at the very end, after all the UNION parts:

SELECT name, email FROM employees
UNION
SELECT name, email FROM customers
ORDER BY name;  -- applies to the entire combined result

You cannot ORDER BY within individual SELECT statements in a UNION (unless you wrap them in subqueries).

UNION with LIMIT

Similarly, LIMIT applies to the entire combined result:

SELECT name FROM employees
UNION ALL
SELECT name FROM customers
ORDER BY name
LIMIT 10;  -- first 10 of the combined results

Practical Example: Contact Directory

Build a unified contact directory from employees and customers:

SELECT
  name,
  email,
  'Employee - ' || department AS role,
  'Internal' AS contact_type
FROM employees
WHERE is_active = true

UNION ALL

SELECT
  name,
  email,
  membership || ' member' AS role,
  'Customer' AS contact_type
FROM customers

ORDER BY contact_type, name;

Combining More Than Two Queries

You can chain multiple UNION or UNION ALL operations:

SELECT name, 'employee' AS type FROM employees
UNION ALL
SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'department' AS type FROM departments
ORDER BY type, name;

Try It Yourself

1. Create a combined list of all unique names from both the employees and customers tables. How many unique names are there?

2. Use UNION ALL with a source column to show all names and emails from both employees and customers, marking each row as 'employee' or 'customer'. Order by name.

3. Find names that appear in both the employees table and the customers table. Hint: you can use UNION ALL with GROUP BY and HAVING COUNT(*) > 1, or look ahead to the INTERSECT lesson.

4. Create a list of "high-value people" that includes employees with salary > 80000 and customers with membership = 'premium'. Include their name, email, and a description column.