SQL UNION

The FULL JOIN lesson used UNION to glue a LEFT JOIN and a RIGHT JOIN together without fully explaining it. UNION doesn't combine tables side by side the way a JOIN does, it stacks two separate query results on top of each other into one, and removes any rows that turn out to be exact duplicates across both.

Learning Objectives

• Explain how UNION differs from a JOIN: stacking rows, not combining columns.
• Follow UNION's requirement that both SELECTs return the same number of columns with compatible types.
• Predict when UNION removes a row as a duplicate.

Basic UNION

Two complete SELECT statements, separated by UNION. Both must return the same number of columns, in a compatible order and type, first_name/department_id in both here, column names in the final result come from the first SELECT. Anyone matching either condition, in Engineering or earning over 90000, appears in the result exactly once, even if they match both.

SELECT first_name, department_id
FROM employees
WHERE department_id = 1

UNION

SELECT first_name, department_id
FROM employees
WHERE salary > 90000;

UNION Removes Duplicate Rows

This is UNION's defining behavior: after stacking both result sets, it compares every row and removes exact duplicates, same values in every column. It's conceptually similar to SELECT DISTINCT, just applied across two stacked queries instead of one.

-- Ada is in Engineering AND earns over 90000
-- She still appears in the UNION result exactly one time, not two

The Column Rule

Every database in this course enforces this the same way: both SELECTs need the same number of columns, and each corresponding column needs a compatible type (text with text, number with number). The column names used in the final output come from the first SELECT alone, a column alias on the second SELECT doesn't change anything.

SELECT first_name, salary FROM employees
UNION
SELECT name FROM departments;
-- Error: both SELECTs must return the same number of columns

UNION vs JOIN

Try It Yourself

Write a query returning the first_name of everyone in Engineering (department_id 1) or Research (department_id 2), using UNION.

SELECT first_name FROM employees WHERE department_id = 1
UNION
SELECT first_name FROM employees WHERE department_id = 2;

SELECT first_name FROM employees WHERE department_id IN (1, 2);
-- Same result, one query. UNION is for combining genuinely different SELECTs, not a substitute for IN.

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Reach for UNION only when genuinely combining two different queries, not as a workaround for something IN or OR already does in one query.
• Give both SELECTs the same column aliases, for clarity, even though only the first SELECT's aliases actually appear in the output.
• Remember UNION's deduplication has a real cost on large result sets, UNION ALL (next lesson) skips it when duplicates genuinely don't matter.

Interview Questions

Summary

UNION stacks the results of two or more SELECT statements into one, removing exact duplicate rows, and requires every SELECT involved to return the same number of compatible columns. It solves a different problem than JOIN: combining query results vertically, not combining tables' columns side by side.

What's Next?

UNION ALL next, identical to UNION except it keeps duplicate rows instead of removing them, and is faster for exactly that reason.