Pointer Walk

SQL Joins

Two small tables and six joins. Which rows survive inner, left, right, full, cross and self joins.

DatabasesBeginner10 min read

Data lives in separate tables

A database does not store an order as "Ana bought a book". It stores customers in one table and orders in another, and each order carries the id of its customer. That way Ana's name is written once, however many orders she has.

To get "Ana bought a book" back, the two tables have to be put together. That is a join: for each row on one side, find the matching rows on the other.

Every join answers the same two questions:

  1. Which rows match? Usually given by ON orders.customer_id = customers.id.
  2. What happens to rows that match nothing? This is the only difference between the join types.

Two tables, five joins

The tables below are set up to have one of each kind of row. Cy is a customer with no orders. The gift order has no customer. Ana has two orders.

Switch between the joins and watch which rows are crossed out.

INNER JOINInteractive
Join

customers

idname
1Ana
2Ben
3Cy

orders

idcustomer_iditem
101book
111pen
122lamp
13NULLgift
SELECT c.name, o.item
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id

Result

nameitem
Anabook
Anapen
Benlamp

3 rows. Only pairs that match. A row with no partner on the other side is dropped.

INNER JOIN: only the matches

Keep a pair only when both sides exist. Cy disappears, because he has no order. The gift disappears, because it has no customer.

This is the default: writing just JOIN means INNER JOIN.

LEFT JOIN: keep everything on the left

Every row of the first table survives. Where there is no match, the columns from the second table are filled with NULL.

Cy is back, with NULL for the item. Use this for "all customers, and their orders if they have any".

Example

Finding rows with no match

A left join is also how you find what is missing. Customers who have never ordered:

sql
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The join keeps Cy with NULLs in the order columns, and the WHERE keeps only such rows.

RIGHT JOIN: keep everything on the right

The mirror image. Every order survives, and the gift order appears with NULL for the customer.

Any right join can be rewritten as a left join by swapping the two tables. Most people only ever write left joins, so that "the table I care about" always comes first.

FULL JOIN: keep everything

Matched pairs, plus the leftovers from both sides. Cy appears with no item and the gift appears with no customer. Useful for comparing two lists to see what is in one, the other, or both.

CROSS JOIN: every combination

No matching condition at all. Each row on the left is paired with every row on the right: 3 customers × 4 orders = 12 rows.

It is occasionally what you want, for example every size combined with every colour. More often it appears by accident, when a join condition is forgotten, and a query that should return a thousand rows returns a billion.

Self join: a table joined to itself

The sixth kind is not a new rule. It is any of the joins above, with the same table on both sides. It is used when rows refer to other rows in the same table.

sql
SELECT worker.name, boss.name AS manager
FROM employees worker
LEFT JOIN employees boss ON boss.id = worker.manager_id;

The table is given two different names so the query can tell the two roles apart. The left join keeps the person at the top, who has no manager.

Side by side

JoinRows with no match on the leftRows with no match on the rightResult here
INNERDroppedDropped3 rows
LEFTKept, with NULLsDropped4 rows
RIGHTDroppedKept, with NULLs4 rows
FULLKept, with NULLsKept, with NULLs5 rows
CROSSNo matching: every pairing12 rows

Key takeaways

  • A join puts rows from two tables together where a condition matches.
  • The join types differ only in what happens to rows that match nothing.
  • INNER drops them. LEFT keeps the left table's. RIGHT keeps the right table's. FULL keeps both.
  • CROSS pairs every row with every row and has no condition.
  • A self join is a table joined to itself under two names.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    8 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress