SQL UNION & UNION ALL: Combining Query Result Sets

While SQL Joins combine columns horizontally across tables, the UNION and UNION ALL set operators combine query results vertically by stacking rows from two or more SELECT statements into a single unified result set.

The 3 Strict Rules of UNION

• Rule 1: Both queries must project the exact same number of columns.

• Rule 2: Columns in corresponding positions must share compatible data types.

• Rule 3: Column header names are inherited from the first SELECT statement.

UNION (Deduplicated) vs UNION ALL (Fast & All Rows)

-- 1. UNION: Combines customer and supplier cities, eliminating duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- 2. UNION ALL: Preserves all occurrences without deduplication overhead (FAST!)
SELECT student_id, first_name, 'Undergraduate' AS academic_level FROM students
UNION ALL
SELECT student_id, first_name, 'Postgraduate' AS academic_level FROM alumni;

Congratulations! You have completed Module 6 (Joins & Set Operations). Now let us enter the advanced realm for senior software engineers starting with SQL Subqueries & Nested Queries!