JOINs

Why join?

Relational databases split data into many small tables to avoid duplication (normalization). To answer real questions you have to stitch them back together:

users                  posts
id 
name              id
user_id
 
title
1  
Ada               1
1
 
Hello
2  
Linus             2
1
 
World
3  
Grace             3
2
 
Hi

"Show every post with its author's name" requires combining posts and users on posts.user_id = users.id.

INNER JOIN — only matched rows

SELECT u.name, p.title
FROM   users u
INNER JOIN posts p ON p.user_id = u.id;

Result: only users with posts (Ada, Linus). Grace has no posts → dropped.

INNER is the default — you can write just JOIN. Aliases (u, p) are universal practice.

LEFT JOIN — keep all rows from the left table

SELECT u.name, p.title
FROM   users u
LEFT JOIN posts p ON p.user_id = u.id;

Now Grace also appears, with p.title = NULL because she has no matches. This is the everyday tool for "show me everyone, plus their X if they have it".

name  | title
Ada   | Hello
Ada   | World
Linus | Hi
Grace | NULL    ← still shown

RIGHT JOIN — symmetrical mirror

SELECT u.name, p.title
FROM   users u
RIGHT JOIN posts p ON p.user_id = u.id;

Keeps every posts row, even orphans whose user_id doesn't match a user. In practice almost everyone uses LEFT JOIN and just swaps the table order — it's easier to read.

FULL OUTER JOIN — both sides

SELECT u.name, p.title
FROM   users u
FULL OUTER JOIN posts p ON p.user_id = u.id;

Returns matched rows + unmatched users + unmatched posts. NULLs fill in where there's no match. (MySQL doesn't support FULL OUTER directly — emulate with LEFT JOIN UNION RIGHT JOIN.)

CROSS JOIN — Cartesian product

SELECT u.name, t.tag
FROM   users u
CROSS JOIN tags t;

Every user paired with every tag. m × n rows. Useful for generating combinations (calendar matrices, all-pairs reports). Don't do it accidentally on big tables.

Self-joins

A table joined to itself — for hierarchies, "find pairs", and comparing rows:

-- Find employees and their managers (both rows live in 'employees')
SELECT e.name AS employee, m.name AS manager
FROM   employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Aliases are mandatory here — the engine can't tell which copy you mean otherwise.

USING vs ON

If the join columns share the same name, USING is shorter:

SELECT * FROM orders JOIN order_items USING (order_id);
-- equivalent to:
SELECT * FROM orders o JOIN order_items i ON i.order_id = o.order_id;

USING (col) collapses the duplicate column in the result. ON keeps both.

NATURAL JOIN — avoid

SELECT * FROM users NATURAL JOIN posts;

Joins on every column with the same name. Brittle — adding a column anywhere can silently change semantics. Don't use it.

Anti-join — "rows with no match"

-- All users with no posts
SELECT u.*
FROM   users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE  p.id IS NULL;

The WHERE p.id IS NULL filter, applied after a LEFT JOIN, keeps only the unmatched rows. Equivalent and often clearer:

SELECT * FROM users u
WHERE  NOT EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

Many tables in one query

SELECT u.name, p.title, c.text AS comment
FROM   users    u
JOIN   posts    p ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE  u.country = 'IN'
ORDER  BY u.name;

Joins chain naturally. Each ON clause references the tables already in scope.

Filter in ON vs WHERE

For INNER JOIN it usually doesn't matter — both filter rows. For LEFT JOIN it matters a lot:

-- ✅ Keep all users; only count published posts
SELECT u.name, COUNT(p.id) AS posts
FROM   users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'published'
GROUP  BY u.name;
-- ❌ Drops users with no published posts
SELECT u.name, COUNT(p.id) AS posts
FROM   users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE  p.status = 'published'   -- nullifies the LEFT JOIN
GROUP  BY u.name;

Rule of thumb: filters on the right side of a LEFT JOIN belong in ON, not WHERE.

Quick decision table