UNION and UNION ALL

UNION

Stacks results from two SELECT statements and removes duplicate rows. Example: a student in Spring and Fall appears once.

UNION ALL

Stacks results and keeps duplicates. Example: a student listed in both semesters appears twice.

UNION requirements

Both SELECT statements need the same number of columns, in the same order, with compatible data types.

UNION example

SELECT full_name FROM Students_Spring2024 UNION SELECT full_name FROM Students_Fall2024;

UNION ALL example

SELECT email FROM Students_Spring2024 UNION ALL SELECT email FROM Students_Fall2024;

ORDER BY with UNION

Put ORDER BY after the second SELECT. Example: UNION SELECT … ORDER BY full_name;

UNION duplicate rule

UNION checks the entire selected row. If one selected value differs, the rows are not duplicates.

UNION mistake

Selecting full_name and academic_year may keep the same student twice if their academic year changed.

Labeling UNION rows

Add a static column to identify the source. Example: SELECT full_name, 'Spring 2024' AS semester FROM Students_Spring2024.

When to use UNION

Use UNION when you want unique combined results. Example: all unique email addresses from Spring and Fall.

When to use UNION ALL

Use UNION ALL when every record matters. Example: all freshman records from both semesters, including repeats.