join

UNION: Stacking Result Sets

Use UNION to combine employees and department heads into a single name list with a role label.

Overview

UNION (and UNION ALL) stack two queries with identical column counts into one result set. UNION removes duplicate rows; UNION ALL keeps them and is faster. This is ideal for reporting the same shape of data from different sources — for example, a directory of people and their role.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

First SELECT — pulls employee names and labels them 'Employee'.
UNION ALL — appends the second query's rows without de-duplication.
Both queries must return the same number of columns with compatible types; we pad departments with an empty last_name to match the shape.

UNION vs UNION ALL

Use UNION ALL unless you specifically need duplicates removed — UNION performs an extra de-duplication pass that can be surprisingly expensive.

Variations

Combine completed and cancelled order counts per status using UNION:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

Mismatched column counts. Both sides must return the same number of columns or the query fails.
Different column order. UNION matches by position, not name — put columns in the same order on both sides.
Assuming UNION keeps duplicates. It doesn't; reach for UNION ALL when you want every row.

Related examples

Common Table Expressions (CTEs) With WITH

cte

Use a WITH clause to compute total spend per customer as a named, reusable result set, then filter and join against it.

INNER JOIN: Combining Rows From Two Related Tables