JOIN vs UNION & Set Operators

While both JOIN and UNION allow you to combine data from multiple tables, they do so in completely different ways.

JOIN (Horizontal Combination)

Joins combine columns from different tables. Use it when you want to see related data side-by-side.

Users: {id, name}  +  Orders: {id, user_id, amount}
     → Result: {user.name, order.amount}

UNION (Vertical Combination)

Unions combine rows from different tables. Use it when you want to stack results from one table on top of another.

Q1_Results: {name, sales}  +  Q2_Results: {name, sales}
     → Result: {name, sales}  -- all rows from both quarters stacked

UNION vs UNION ALL

-- UNION: removes duplicates
SELECT name FROM Customers_A
UNION
SELECT name FROM Customers_B;

-- UNION ALL: keeps duplicates
SELECT name FROM Customers_A
UNION ALL
SELECT name FROM Customers_B;

Requirements for UNION

Both queries must have:

1. The same number of columns
2. Compatible data types in the same order
3. Column names from the first query determine output column names

INTERSECT

Returns rows that appear in both result sets:

SELECT name FROM Customers_A
INTERSECT
SELECT name FROM Customers_B;

EXCEPT (or MINUS in Oracle)

Returns rows from the first query that are not in the second:

SELECT name FROM Customers_A
EXCEPT
SELECT name FROM Customers_B;

Order of Operations

When combining set operators, use parentheses to control precedence. Without parentheses, INTERSECT binds more tightly than UNION and EXCEPT in most databases.

Practical Use Cases

• UNION: Combining monthly sales tables, merging archived data with live data.
• INTERSECT: Finding customers who bought in both campaigns.
• EXCEPT: Finding products that were never sold, identifying discrepancies between two systems.

SELECT name, email FROM Customers_A
UNION
SELECT name, email FROM Customers_B;

SELECT name, email FROM Customers_A
INTERSECT
SELECT name, email FROM Customers_B;

SELECT name, email FROM Customers_A
EXCEPT
SELECT name, email FROM Customers_B;

-- Using UNION on the same table effectively removes duplicates
SELECT name FROM employees
UNION
SELECT name FROM employees;

-- If 'John' exists in both, he will appear twice in the result
SELECT name FROM Table_1
UNION ALL
SELECT name FROM Table_2;

SELECT product_id, sales_amount FROM sales_2023
UNION ALL
SELECT product_id, sales_amount FROM sales_2024
ORDER BY product_id;