SQL Joins

Joins are how relational databases combine data from multiple tables into a single result set. Understanding exactly which rows are included — and which are excluded — in each join type is one of the most practically useful things to know about SQL.

All examples use two tables:

How Joins Work

At its core, a join builds a Cartesian product — every row from the left table paired with every row from the right table — and then filters down to rows where the join condition is true. The join type determines what happens to rows that have no match on the other side.

The join condition is specified with ON. The most common condition matches a foreign key to a primary key:

SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id;

INNER JOIN

Returns only rows where a match exists in both tables. Rows with no matching counterpart on either side are excluded entirely.

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

Bob and Dave have no orders and are excluded. INNER JOIN is the default — writing just JOIN is equivalent.

LEFT JOIN

Returns all rows from the left table and the matched rows from the right. Where no match exists, the right-side columns are NULL.

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

LEFT JOIN is the most commonly used outer join. Use it when you want all records from the primary table regardless of whether a related record exists — for example, finding users who have never placed an order:

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

RIGHT JOIN

The mirror of LEFT JOIN — returns all rows from the right table and matched rows from the left. Right-side rows with no match get NULL for the left columns.

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

In practice, RIGHT JOIN is rarely written — you can always rewrite it as a LEFT JOIN by swapping the table order, which is easier to read.

FULL OUTER JOIN

Returns all rows from both tables. Rows with no match on either side are included with NULLs for the missing columns. The result is the union of a LEFT JOIN and a RIGHT JOIN.

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

FULL OUTER JOIN is useful for reconciliation — finding rows in either table that have no counterpart in the other. Note: MySQL does not support FULL OUTER JOINnatively; emulate it with UNION of a LEFT and RIGHT JOIN.

Self Join

A self join joins a table to itself. It is not a distinct join type — it uses INNER or LEFT JOIN — but requires table aliases to distinguish the two "copies". Self joins are the natural way to query hierarchical or recursive data stored in a single table.

Example: employee–manager hierarchy

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

CROSS JOIN

Produces the Cartesian product of two tables — every combination of every row from both sides. If the left table has M rows and the right has N rows, the result has M × N rows. There is no join condition.

SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;

A cross join between a 3-row colours table and a 4-row sizes table yields 12 rows — all possible colour-size combinations. Use intentionally for generating test data or combination matrices. An accidental missing ON clause in older SQL produces the same result implicitly.

Choosing the Right Join