Version history

A join combines rows from two tables based on a related column. The join type decides what happens to rows that have no match.

Sample tables

Say we have users and orders, where each order has a user_id.

INNER JOIN

Only rows that match on both sides.

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

A user with no orders does not appear. An order with no matching user does not appear.

LEFT JOIN

Every row from the left table, plus matches from the right. Where there is no match, the right columns are NULL.

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

This is how you find users with no orders:

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

RIGHT JOIN and FULL JOIN

• RIGHT JOIN is a LEFT JOIN with the tables flipped. Most people just rewrite it as a LEFT JOIN for readability.
• FULL OUTER JOIN keeps unmatched rows from both sides, filling NULLs where there is no partner.

Mental picture

The classic bug

Filtering a LEFT JOIN in the WHERE clause on the right table quietly turns it into an INNER JOIN, because a NULL fails the comparison. If you want to keep unmatched rows, put the condition in the ON clause instead of WHERE.