Joins, and What Each One Actually Returns

A report totals orders by customer and the numbers are wrong in one direction: too low. Every order is counted, every amount is right, and the three customers who spent the most are missing. Nothing in the query looks wrong. The JOIN keyword is doing what it is defined to do, keeping only rows where both sides matched, and the missing customers are the ones whose rows did not.

An inner join is a filter that does not announce itself as one. To keep rows with no partner you have to say so, and the word you add changes which side keeps its rows and what appears in the columns that had no match. The rest of this lesson is the four ways that can go, and the one clause placement that silently converts an outer join back into an inner one.

The dataset continues from the previous lesson: five customers, seven orders, seven order lines. Order 1004 has no line items, and orders 1004, 1006 and 1007 do not appear in the line table at all, which is deliberate, because an outer join with nothing to pad teaches nothing.

An inner join keeps only the rows that matched

A join takes two row sets and produces one by pairing rows that satisfy the condition. An inner join discards every row that found no partner, so the row count of the result is not the row count of either input.

CREATE TABLE line_item (
  order_id   INT NOT NULL,
  line_no    INT NOT NULL,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  unit_cents INT NOT NULL,
  PRIMARY KEY (order_id, line_no),
  CONSTRAINT fk_line_item_order FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
) ENGINE=InnoDB;

INSERT INTO line_item (order_id, line_no, product_id, qty, unit_cents) VALUES
 (1001, 1, 7, 2, 1200), (1001, 2, 9, 1, 4500),
 (1002, 1, 7, 1, 1200), (1003, 1, 9, 2, 4500),
 (1005, 1, 7, 5, 1200), (1005, 2, 9, 3, 4500),
 (1006, 1, 7, 1, 1200);

Seven orders and seven lines, and the inner join returns seven rows:

SELECT o.order_id, o.status, i.line_no
FROM orders o
JOIN line_item i ON i.order_id = o.order_id
ORDER BY o.order_id, i.line_no;

+----------+----------+---------+
| order_id | status   | line_no |
+----------+----------+---------+
|     1001 | paid     |       1 |
|     1001 | paid     |       2 |
|     1002 | paid     |       1 |
|     1003 | pending  |       1 |
|     1005 | paid     |       1 |
|     1005 | paid     |       2 |
|     1006 | pending  |       1 |
+----------+----------+---------+

Order 1004 is missing, and order 1007 with it. Both exist in orders, neither has a line, and the inner join has no way to represent an order with no lines. There is no partial row and no warning. For "which orders have lines" that result is correct. For "what does each order contain" it is a lie of omission, because an order that contains nothing is an answer.

An outer join keeps one side and pads the other with NULL

LEFT JOIN keeps every row from the left table, whether or not it matched. Where no partner exists, the columns from the right table are filled with NULL. That NULL is not a value someone stored; it is the absence of a row, materialised so the result can stay rectangular.

SELECT o.order_id, o.status, i.line_no, i.qty
FROM orders o
LEFT JOIN line_item i ON i.order_id = o.order_id
ORDER BY o.order_id, i.line_no;

+----------+----------+---------+------+
| order_id | status   | line_no | qty  |
+----------+----------+---------+------+
|     1001 | paid     |       1 |    2 |
|     1001 | paid     |       2 |    1 |
|     1002 | paid     |       1 |    1 |
|     1003 | pending  |       1 |    2 |
|     1004 | refunded |    NULL | NULL |
|     1005 | paid     |       1 |    5 |
|     1005 | paid     |       2 |    3 |
|     1006 | pending  |       1 |    1 |
|     1007 | paid     |    NULL | NULL |
+----------+----------+---------+------+

Nine rows: the seven matched pairs stay, and two rows appear for the orders with no lines. The row count grew because the left side is the one being preserved. RIGHT JOIN preserves line_item and lands the NULLs on the order columns. Since every line here has an order, a right join returns the same seven rows as the inner join: RIGHT JOIN is usually a left join written the other way round.

A FULL OUTER JOIN preserves both sides. MySQL has no such clause, so the emulation is a left join unioned with the unmatched right rows; SQLite 3.51 supports FULL OUTER JOIN directly, which makes the emulation easy to check:

DROP TABLE IF EXISTS legacy_order;
CREATE TABLE legacy_order (
  order_id    INT NOT NULL,
  customer_id INT NOT NULL,
  note        VARCHAR(40) NOT NULL
);

INSERT INTO legacy_order VALUES
 (1001, 1, 'migrated'),
 (1099, 99, 'unknown customer');

SELECT c.customer_id AS cust, l.order_id AS legacy_order, l.note
FROM customer c
LEFT JOIN legacy_order l ON l.customer_id = c.customer_id
UNION
SELECT c.customer_id, l.order_id, l.note
FROM customer c
RIGHT JOIN legacy_order l ON l.customer_id = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY cust IS NULL, cust, legacy_order;

+------+--------------+------------------+
| cust | legacy_order | note             |
+------+--------------+------------------+
|    1 |         1001 | migrated         |
|    2 |         NULL | NULL             |
|    3 |         NULL | NULL             |
|    4 |         NULL | NULL             |
|    5 |         NULL | NULL             |
| NULL |         1099 | unknown customer |
+------+--------------+------------------+

Two kinds of padding in one result. Customers 2 to 5 have no legacy order, so the right-hand columns are NULL; order 1099 has a customer id that no customer has, so the left-hand columns are NULL. UNION rather than UNION ALL is safe here because the second branch's WHERE c.customer_id IS NULL contributes only unmatched rows.

Putting the filter in WHERE turns an outer join back into an inner one

This is the mistake the lesson exists for. Filtering the null-padded side in WHERE removes the rows the outer join went to the trouble of keeping, because a NULL cannot satisfy a comparison. The query still runs and still returns rows, and is now an inner join wearing an outer join's keyword.

SELECT c.customer_id, c.email, o.order_id, o.status
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id, o.order_id;

+-------------+-----------------+----------+--------+
| customer_id | email           | order_id | status |
+-------------+-----------------+----------+--------+
|           1 | ana@example.com |     1001 | paid   |
|           1 | ana@example.com |     1002 | paid   |
|           1 | ana@example.com |     1007 | paid   |
|           2 | bo@example.com  |     1005 | paid   |
|           3 | cai@example.com |     NULL | NULL   |
|           4 | dee@example.com |     NULL | NULL   |
|           5 | eve@example.com |     NULL | NULL   |
+-------------+-----------------+----------+--------+

Seven rows. Customers 3, 4 and 5 keep their place with NULL order columns. That is what a left join is for: the status condition is part of the match, so a customer whose only orders are pending still appears, with nothing on the right.

Now move the same condition into WHERE:

SELECT c.customer_id, c.email, o.order_id, o.status
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_id, o.order_id;

+-------------+-----------------+----------+--------+
| customer_id | email           | order_id | status |
+-------------+-----------------+----------+--------+
|           1 | ana@example.com |     1001 | paid   |
|           1 | ana@example.com |     1002 | paid   |
|           1 | ana@example.com |     1007 | paid   |
|           2 | bo@example.com  |     1005 | paid   |
+-------------+-----------------+----------+--------+

Four rows, and three customers have vanished. The left join produced the padded rows, then WHERE evaluated NULL = 'paid' on each of them, got unknown, and discarded them. The two statements differ by four rows and nothing else.

The rule that follows is mechanical. A condition that decides whether a row on the optional side is a match belongs in ON. A condition that decides whether a preserved row survives belongs in WHERE. "Every customer who has at least one paid order" is the WHERE version, and it would be clearer as an inner join.

A join without a condition multiplies instead of matching

Write JOIN and forget the ON, and MySQL returns the cross product. Five customers times seven orders is 35 rows, each customer paired with every order, and nothing in the output says the pairing is meaningless.

SELECT COUNT(*) AS accidental_pairs FROM customer c JOIN orders o;

SELECT COUNT(*) AS deliberate_pairs FROM customer CROSS JOIN orders;

+------------------+
| accidental_pairs |
+------------------+
|               35 |
+------------------+

+------------------+
| deliberate_pairs |
+------------------+
|               35 |
+------------------+

Both return 35, which is the point: the two statements are the same statement. A cross join is occasionally what you want, and when it is, write CROSS JOIN so the next reader knows the multiplication was intended. The failure mode is what happens downstream: a cross join in a chain of three tables multiplies again, and a SUM over the result is wrong by a factor nobody notices until the invoice is sent.

Two habits catch it early. Give every join an explicit ON or USING, and compare the row count of the join against the row counts of its inputs before you aggregate anything.

A self join relates rows of one table to each other

Nothing requires the two sides of a join to be different tables. A self join gives one table two aliases and relates its rows to each other. The question it answers is which rows pair with which: orders from the same customer, employees under the same manager.

SELECT a.order_id AS first_order, b.order_id AS second_order, a.customer_id
FROM orders a
JOIN orders b
  ON b.customer_id = a.customer_id
 AND b.order_id > a.order_id
ORDER BY a.order_id, b.order_id;

+-------------+--------------+-------------+
| first_order | second_order | customer_id |
+-------------+--------------+-------------+
|        1001 |         1002 |           1 |
|        1001 |         1007 |           1 |
|        1002 |         1007 |           1 |
|        1003 |         1005 |           2 |
|        1004 |         1006 |           4 |
+-------------+--------------+-------------+

The b.order_id > a.order_id is doing two jobs. It prevents a row from pairing with itself, and it prevents the mirror pair from appearing as well, so a pair of orders shows up once rather than twice. Drop it and the count goes to 17, because every order now pairs with all seven orders from its own customer, including itself. Drop the whole ON clause and the join becomes a cross product, which is the next section. That asymmetry between > and <> is the difference between a combination and a permutation, and it is the reason self joins written without thinking about the inequality produce double-counted reports.

The same technique with a NULL check gives an anti join: left join the table to itself, then keep the rows where the right-hand copy is NULL. On this dataset that returns the orders with no lines, the two rows the inner join dropped at the start. It is worth knowing because it works on every engine, and it is also the shape that NOT EXISTS usually replaces with something shorter.

USING and ON name the same join differently

When both sides name the join column identically, USING (column) is shorter than ON and it changes the result set in one visible way: the join column appears once instead of twice.

SELECT * FROM orders JOIN line_item USING (order_id) WHERE orders.order_id = 1001;

SELECT * FROM orders JOIN line_item ON line_item.order_id = orders.order_id
WHERE orders.order_id = 1001;

USING:  order_id, customer_id, status, placed_at, total_cents,
        line_no, product_id, qty, unit_cents

ON:     order_id, customer_id, status, placed_at, total_cents,
        order_id, line_no, product_id, qty, unit_cents

Nine columns against ten. With USING, the merged column is unqualified and belongs to both tables, which also means USING only works when the column names match. Where the parent key is orders.order_id and the child column is line_item.order_id it works; where the parent key is orders.id and the child column is order_id, MySQL rejects the statement with Unknown column 'order_id' in 'from clause'.

My preference is ON almost everywhere: it is explicit about the columns being compared, it survives a rename on one side, and it is the only option for the non-equality joins where the interesting queries live. USING earns its place when the join column is genuinely the same concept on both sides.

SELECT c.customer_id, c.email, o.order_id, i.line_no
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN line_item i ON i.order_id = o.order_id
UNION
SELECT c.customer_id, c.email, o.order_id, i.line_no
FROM customer c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN line_item i ON i.order_id = o.order_id
WHERE c.customer_id IS NULL
ORDER BY customer_id IS NULL, customer_id, order_id, line_no;

+-------------+-----------------+----------+---------+
| customer_id | email           | order_id | line_no |
+-------------+-----------------+----------+---------+
|           1 | ana@example.com |     1001 |       1 |
|           1 | ana@example.com |     1001 |       2 |
|           1 | ana@example.com |     1002 |       1 |
|           1 | ana@example.com |     1007 |    NULL |
|           2 | bo@example.com  |     1003 |       1 |
|           2 | bo@example.com  |     1005 |       1 |
|           2 | bo@example.com  |     1005 |       2 |
|           3 | cai@example.com |     NULL |    NULL |
|           4 | dee@example.com |     1004 |    NULL |
|           4 | dee@example.com |     1006 |       1 |
|           5 | eve@example.com |     NULL |    NULL |
+-------------+-----------------+----------+---------+

SELECT c.customer_id, c.email, o.order_id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id, o.order_id;

SELECT o.order_id
FROM orders o
LEFT JOIN line_item i ON i.order_id = o.order_id
WHERE i.order_id IS NULL
ORDER BY o.order_id;

+----------+
| order_id |
+----------+
|     1004 |
|     1007 |
+----------+

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM line_item i WHERE i.order_id = o.order_id
)
ORDER BY o.order_id;

SELECT a.order_id AS first_order, b.order_id AS second_order, a.customer_id
FROM orders a
JOIN orders b
  ON b.customer_id = a.customer_id
 AND b.order_id > a.order_id
ORDER BY a.order_id, b.order_id;