DiagramDB
DiagramDB / SQL Join Types

SQL Join Types

A join combines rows from two tables where a condition matches. Which unmatched rows survive is the only difference between the join types. Toggle them below and watch it happen on real rows.

Try each join on the same two tables

customers
idname
1Ana
2Ben
3Chloé
4Dev
orders
idcustomer_idtotal
10142
11118
12375
13730
customersorders
SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Only pairs where o.customer_id = c.id. Ben and Dev have no orders, and order 13 points at a customer that doesn’t exist, so all three disappear. Ana appears twice because she has two orders.

Result: 3 rows
nameo.idtotal
Ana1042
Ana1118
Chloé1275

Rows shaded magenta exist only because of an outer join; the other side is NULL.

Seeing a result change as you flip a switch is often what makes an idea stick. On ahaboo, a distinct project, you can adjust marginal tax rates and see your effective rate move in response.

The join types in one sentence each

  • INNER JOIN: rows that match on both sides. The default JOIN.
  • LEFT JOIN: every row from the first table, with matches or NULLs. The one you’ll use most after INNER.
  • RIGHT JOIN: the mirror image; rewrite it as a LEFT JOIN for readability.
  • FULL OUTER JOIN: all rows from both sides. Not available in MySQL.
  • CROSS JOIN: the Cartesian product, with no condition.
  • SELF JOIN: a table joined to itself with two aliases, e.g. employees to their managers: FROM employees e LEFT JOIN employees m ON m.id = e.manager_id.
  • Anti join: rows with no match, via LEFT JOIN … WHERE right.id IS NULL or NOT EXISTS. Beware NOT IN with a subquery that can return NULL: it returns no rows at all.

ON vs WHERE in outer joins

For an inner join it makes no difference whether a filter sits in ON or WHERE. For a LEFT JOIN it does: a filter in ON decides which right-side rows match, while a filter in WHERE runs afterwards and throws away the NULL rows. LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid' keeps customers with no paid orders; moving the status check to WHERE silently drops them.

Joins follow the foreign keys in your schema. Draw them with the ER diagram generator to see which joins make sense, and check any join you write with the SQL validator.

Questions

What are the types of SQL joins?

INNER JOIN (only matching rows), LEFT OUTER JOIN (all rows from the left table plus matches), RIGHT OUTER JOIN (all rows from the right table plus matches), FULL OUTER JOIN (all rows from both, matched where possible), CROSS JOIN (every combination) and a SELF JOIN (a table joined to itself, using any of the above).

Is JOIN the same as INNER JOIN?

Yes. JOIN on its own means INNER JOIN in every major database. Likewise LEFT JOIN is short for LEFT OUTER JOIN.

Why did my LEFT JOIN return fewer rows than expected?

A condition on the right table in the WHERE clause (for example WHERE o.status = 'paid') removes the NULL rows the LEFT JOIN kept, turning it into an inner join. Move that condition into the ON clause instead.

Why did my join return more rows than the table has?

Joins multiply rows: if one customer has three orders, the customer appears three times. That is correct behaviour. If it is unintended, the join key is not unique on one side; aggregate first or join on a more specific key.

Does MySQL support FULL OUTER JOIN?

No. MySQL and older MariaDB versions lack FULL OUTER JOIN. Emulate it with a LEFT JOIN UNION a RIGHT JOIN (or a LEFT JOIN plus the unmatched rows of the other side). SQLite added RIGHT and FULL joins in version 3.39.

Are Venn diagrams accurate for joins?

Only roughly. Venn diagrams show which rows are kept, but not that matching rows can multiply. The result table on this page shows the real rows, which is why it is next to the diagram.

Related tools