Skip to content
SQLSimplified
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

Loading playground environment...

Step-by-Step Breakdown

  1. First SELECT: pulls employee names and labels them 'Employee'.
  2. UNION ALL: appends the second query's rows without de-duplication.
  3. 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:

Loading playground environment...

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
join
Use INNER JOIN to attach department names to employee records, keeping only rows that match on both sides.

Cite this resource

SQLSimplified. "UNION: Stacking Result Sets Example". Available at: https://sqlsimplified.online/examples/union-combined