JOINs: combining tables

INNER, LEFT and RIGHT joins, table aliases, multi-table joins and finding unmatched rows.

Real applications spread data across many tables: customers in one, orders in another, products in a third. JOINs combine them in a single query by matching rows on related columns, usually a foreign key matched to a primary key.

Sample data#

SQL

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(30)
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status      VARCHAR(10) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE products (
    id    INT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL
);

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    qty        INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders (id),
    FOREIGN KEY (product_id) REFERENCES products (id)
);

INSERT INTO customers VALUES (1, 'Ada', 'London'), (2, 'Alan', 'Manchester'), (3, 'Grace', 'New York');
INSERT INTO orders VALUES (101, 1, 'paid'), (102, 1, 'pending'), (103, 2, 'paid');
INSERT INTO products VALUES (1, 'Keyboard', 50.00), (2, 'Mouse', 20.00), (3, 'Monitor', 200.00);
INSERT INTO order_items VALUES (101, 1, 1), (101, 2, 2), (102, 3, 1), (103, 2, 1);
-- Grace has no orders yet

INNER JOIN: only matching rows#

SQL

SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

Output

+------+----------+---------+
| name | order_id | status  |
+------+----------+---------+
| Ada  |      101 | paid    |
| Ada  |      102 | pending |
| Alan |      103 | paid    |
+------+----------+---------+

The ON clause is the matching rule. Each customer row is paired with every order row whose customer_id equals its id. Grace is missing because she has no matching order. That is what inner means: only the intersection. Plain JOIN is the same as INNER JOIN.

Table aliases (c, o) keep queries short, and they're required when both tables have a column with the same name (id). Get into the habit of prefixing every column with its alias in joins.

LEFT JOIN: keep everything on the left#

SQL

SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

Output

+-------+----------+---------+
| name  | order_id | status  |
+-------+----------+---------+
| Ada   |      101 | paid    |
| Ada   |      102 | pending |
| Alan  |      103 | paid    |
| Grace |     NULL | NULL    |
+-------+----------+---------+

Every customer appears. For Grace, the order columns are NULL. LEFT JOIN is the same as LEFT OUTER JOIN.

Finding rows without a match (an anti-join) is a classic LEFT JOIN trick:

SQL

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

Output

+-------+
| name  |
+-------+
| Grace |
+-------+

ON vs WHERE in outer joins#

This is the most common outer-join bug. Suppose you want every customer together with their paid orders:

SQL

-- Wrong: the WHERE removes Grace's NULL row, so it acts like an INNER JOIN
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

Output

+------+------+
| name | id   |
+------+------+
| Ada  |  101 |
| Alan |  103 |
+------+------+

SQL

-- Right: filter the joined table inside ON
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

Output

+-------+------+
| name  | id   |
+-------+------+
| Ada   |  101 |
| Alan  |  103 |
| Grace | NULL |
+-------+------+

Rule of thumb: conditions on the optional (right-hand) table belong in ON, and conditions on the preserved (left-hand) table belong in WHERE.

RIGHT JOIN#

RIGHT JOIN is the mirror image: it keeps every row from the right-hand table. A RIGHT JOIN B gives the same rows as B LEFT JOIN A, and most people write LEFT JOINs because they read naturally from left to right.

SQL

SELECT c.name, o.id AS order_id
FROM orders AS o
RIGHT JOIN customers AS c ON o.customer_id = c.id;

Output

+-------+----------+
| name  | order_id |
+-------+----------+
| Ada   |      101 |
| Ada   |      102 |
| Alan  |      103 |
| Grace |     NULL |
+-------+----------+

Joining many tables#

Chain joins to follow relationships. Here is every order line with customer, product and line total:

SQL

SELECT o.id AS order_id, c.name, p.title, oi.qty, oi.qty * p.price AS line_total
FROM orders AS o
JOIN customers   AS c  ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products    AS p  ON p.id = oi.product_id
ORDER BY o.id, p.title;

Output

+----------+------+----------+-----+------------+
| order_id | name | title    | qty | line_total |
+----------+------+----------+-----+------------+
|      101 | Ada  | Keyboard |   1 |      50.00 |
|      101 | Ada  | Mouse    |   2 |      40.00 |
|      102 | Ada  | Monitor  |   1 |     200.00 |
|      103 | Alan | Mouse    |   1 |      20.00 |
+----------+------+----------+-----+------------+

order_items is a junction table: it turns the many-to-many relationship between orders and products into two one-to-many relationships.

Joins with aggregates#

Total spent per customer, including customers who've spent nothing:

SQL

SELECT c.name,
       COUNT(DISTINCT o.id)               AS orders,
       COALESCE(SUM(oi.qty * p.price), 0) AS spent
FROM customers AS c
LEFT JOIN orders      AS o  ON o.customer_id = c.id
LEFT JOIN order_items AS oi ON oi.order_id = o.id
LEFT JOIN products    AS p  ON p.id = oi.product_id
GROUP BY c.id, c.name
ORDER BY spent DESC;

Output

+-------+--------+--------+
| name  | orders | spent  |
+-------+--------+--------+
| Ada   |      2 | 290.00 |
| Alan  |      1 |  20.00 |
| Grace |      0 |   0.00 |
+-------+--------+--------+

Note COUNT(DISTINCT o.id). Order 101 has two items, so after joining items it appears on two rows, and plain COUNT(o.id) would count it twice. Joins multiply rows, so watch your counts and sums whenever you join a one-to-many relationship.

Choosing the right join#

Goal	Use
Only rows that match on both sides	`INNER JOIN`
All rows from A, plus matches from B	`A LEFT JOIN B`
Rows in A with no match in B	`A LEFT JOIN B ... WHERE B.key IS NULL` (or `NOT EXISTS`)
Every combination of A and B	`CROSS JOIN` (next lesson)

Common mistakes#

Forgetting the ON clause. In MySQL, JOIN without ON is a cross join, and every row pairs with every row.
Ambiguous columns. SELECT id ... with two tables that both have id fails with "Column 'id' in field list is ambiguous". Prefix with aliases.
Filtering the outer table in WHERE. That silently turns LEFT JOINs into inner joins.
Double counting after one-to-many joins. Use COUNT(DISTINCT ...), or aggregate in a subquery before joining.
Joining on columns without indexes. Foreign-key columns should be indexed (InnoDB does this automatically for declared foreign keys).

What's next#

Next you'll learn the remaining join shapes: joining a table to itself, CROSS JOIN, USING and how to get a FULL OUTER JOIN in MySQL.

Sample data#

INNER JOIN: only matching rows#

LEFT JOIN: keep everything on the left#

ON vs WHERE in outer joins#

RIGHT JOIN#

Joining many tables#

Joins with aggregates#

Choosing the right join#

Common mistakes#

What's next#

Quick quiz