SQL Joins & Set Operations

SQL Joins & Set Operations

A join combines rows from two tables into one result, pairing rows that satisfy a condition, usually "this table's foreign key equals that table's primary key". Relational databases split data into separate tables to avoid duplication (see Normalization), and joins are how you put it back together when you query.

Set operations (UNION, INTERSECT, EXCEPT) do something different: they stack or compare the rows of two queries with the same columns, rather than widening rows side by side.

The example data

employees                          departments
| id | name    | dept_id |         | id | name        |
|----|---------|---------|         |----|-------------|
| 1  | Alice   | 10      |         | 10 | Engineering |
| 2  | Bob     | 20      |         | 20 | Marketing   |
| 3  | Charlie | NULL    |         | 30 | Finance     |

Note the two "unmatched" cases, which are what distinguish the join types: Charlie has no department, and Finance has no employees.

INNER JOIN: only rows that match on both sides

SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id;       -- JOIN means INNER JOIN

Charlie disappears (his dept_id is NULL, and NULL = 10 is never true), and so does Finance (no employee points to it).

LEFT JOIN: every row from the left, matches where they exist

SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Use it when the left-side rows must all appear even without a match, for example "all customers, with their orders if any".

RIGHT JOIN is the mirror image (every department, employees if any). In practice people swap the table order and write a LEFT JOIN, which reads more naturally.

FULL OUTER JOIN keeps unmatched rows from both sides: Alice, Bob, Charlie + NULL, and NULL + Finance. It's useful for reconciliation, e.g. comparing two lists and seeing what's missing from each. MySQL doesn't support it directly; emulate it with a LEFT JOIN ... UNION ... RIGHT JOIN.

The WHERE-vs-ON trap with outer joins

This is the single most common join bug, and a favourite interview question:

-- Intended: all employees, and their department only if it's Engineering
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Engineering';      -- ❌ returns only Alice

WHERE runs after the join. For Bob and Charlie, d.name is 'Marketing' or NULL, so the WHERE filters them out, and the LEFT JOIN silently behaves like an INNER JOIN. Put conditions on the optional side in the ON clause:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id AND d.name = 'Engineering';   -- ✅ all 3 employees

Rule of thumb: filters on the right table of a LEFT JOIN belong in ON; filters on the left table belong in WHERE.

Self join: a table joined to itself

-- employees has manager_id pointing at another employee's id
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Aliases (e, m) make one physical table act as two logical ones. It's used for hierarchies and for comparing rows within one table.

CROSS JOIN: every combination

SELECT s.size, c.colour FROM sizes s CROSS JOIN colours c;   -- 3 sizes × 4 colours = 12 rows

It's useful for generating combinations (all product variants, a calendar × store grid to fill in missing days). Forgetting the ON condition in older comma-style joins (FROM a, b) produces an accidental cross join, which is a classic cause of results multiplying out of control.

Anti-join and semi-join: "rows with / without a match"

-- Anti-join: employees with NO department
SELECT e.name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

-- Equivalent LEFT JOIN form
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

-- Semi-join: departments that HAVE at least one employee (each listed once)
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

Beware NOT IN with NULLs: WHERE id NOT IN (SELECT dept_id FROM employees) returns nothing if any dept_id is NULL, because x NOT IN (…, NULL) is never true. NOT EXISTS doesn't have this problem, which is why it's the safer default.

Joins that multiply rows

Joining a one-to-many relationship repeats the "one" side once per match. Joining two independent one-to-many relationships multiplies them:

-- A customer with 3 orders and 4 addresses → 12 rows, and SUM(order.total) is 4× too high
SELECT c.id, SUM(o.total)
FROM customers c
JOIN orders o    ON o.customer_id = c.id
JOIN addresses a ON a.customer_id = c.id
GROUP BY c.id;

The fix is to aggregate each child table before joining (in a subquery or CTE), or to not join the table you don't need. Totals that come out suspiciously large usually mean this.

Set operations

These combine the rows of two queries that return the same number and types of columns:

SELECT email FROM customers
UNION                     -- all distinct emails from either (removes duplicates)
SELECT email FROM newsletter_subscribers;

SELECT email FROM customers
UNION ALL                 -- keep duplicates; faster because no de-duplication step
SELECT email FROM newsletter_subscribers;

SELECT email FROM customers
INTERSECT                 -- emails in both
SELECT email FROM newsletter_subscribers;

SELECT email FROM customers
EXCEPT                    -- in customers but not subscribers (Oracle: MINUS)
SELECT email FROM newsletter_subscribers;

Prefer UNION ALL whenever duplicates are impossible or acceptable. UNION has to sort or hash the whole result to remove them.

How the database executes a join

The optimizer picks an algorithm per join, visible in EXPLAIN:

• Nested loop: for each row on one side, look up matches on the other. It's excellent when one side is small and the other has an index on the join column.
• Hash join: build a hash table from the smaller input, then probe it with the larger one. Good for large, unindexed equality joins.
• Merge join: walk two inputs already sorted on the join key.

Practical consequences: index foreign-key columns (many databases don't do it automatically), filter early so fewer rows are joined, and read the plan with EXPLAIN ANALYZE when a join is slow.

Follow-up questions this topic invites — and their answers

Q: What's the difference between putting a condition in ON versus WHERE? A: For INNER JOIN there's no difference in results. For an outer join, ON conditions decide which rows match, and unmatched left rows still appear with NULLs. WHERE conditions filter the final result, so a WHERE on the right table's columns removes the NULL rows and turns the LEFT JOIN into an INNER JOIN.

Q: Why might NOT IN return no rows at all? A: If the subquery returns any NULL, value NOT IN (..., NULL) evaluates to unknown for every row, never true, so nothing is returned. Use NOT EXISTS, or filter NULLs out of the subquery.

Q: UNION vs UNION ALL? A: UNION removes duplicate rows, which costs a sort or hash of the combined result. UNION ALL just appends. Use UNION ALL unless you actually need de-duplication, because it's faster and more predictable.

Q: How would you find duplicate emails in a users table? A: Group and filter: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;. To see the full rows, join back on those emails, or use COUNT(*) OVER (PARTITION BY email) as a window function and filter where it's greater than 1.

Q: Is a subquery slower than a join? A: Not inherently. Modern optimizers often rewrite EXISTS/IN subqueries into semi-joins and produce the same plan. Choose the form that states intent most clearly (EXISTS for "has any", a join when you need columns from both tables), then check EXPLAIN if performance matters.