Why does my join return more rows than I expected?

Usually the join column is not unique on one side, so each row matches several rows. Check the keys, and test the row count before and after the join.

Does it matter whether a condition is in ON or in WHERE?

For an INNER JOIN it makes no difference. For a LEFT JOIN a condition on the right table in WHERE removes the unmatched rows and turns the LEFT JOIN into an INNER JOIN. Put it in ON to keep them.

Which join is the same as VLOOKUP?

A LEFT JOIN: every row of the main table is kept, and the looked-up column is NULL where nothing matches, like #N/A. Unlike VLOOKUP it returns all matches, not just the first.

SQL joins explained, with one example of each

A join combines rows from two tables by matching a column. The join type decides what happens to rows that find no partner. All examples use the bakery database: 8 customers, and 12 orders, two of which were walk-in orders with no customer.

INNER JOIN: only the matches

Keeps a row only when both sides have a match. Customers without orders and orders without a customer both disappear.

SELECT c.last_name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Result: 10 rows: the orders that belong to a customer.

LEFT JOIN: keep everything on the left

Keeps every row of the left table. Where the right table has no match, its columns are NULL. Filtering on IS NULL finds the rows without a match.

SELECT c.last_name, COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.last_name
ORDER BY c.customer_id;

Result: All 8 customers; Kim and Meyer show 0 orders.

RIGHT JOIN: keep everything on the right

The mirror image of LEFT JOIN. Most people swap the table order and use LEFT JOIN instead, which reads more naturally.

SELECT o.order_id, c.last_name
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Result: All 12 orders; orders 4 and 11 have a NULL last name (walk-ins).

FULL JOIN: keep everything on both sides

Keeps unmatched rows from both tables. It is useful to compare two lists and see what is missing on either side.

SELECT c.last_name, o.order_id
FROM customers AS c
FULL JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id, c.last_name;

Result: 14 rows: 10 matches, 2 walk-in orders, and the customers Kim and Meyer with no order.

CROSS JOIN: every combination

Pairs each row of the first table with each row of the second. The row count is the product of the two. Use it on purpose, for example to build a grid of dates and categories.

SELECT COUNT(*) AS combinations
FROM customers
CROSS JOIN products;

Result: 72, which is 8 customers times 9 products.

Self join: a table with itself

Give the same table two aliases to compare rows of the same table, such as two customers in the same city.

SELECT a.first_name AS first_customer, b.first_name AS second_customer, a.city
FROM customers AS a
JOIN customers AS b ON a.city = b.city AND a.customer_id < b.customer_id
ORDER BY a.city;

Result: The pairs Emma and Hugo (Berlin), Ben and Dev (London), and Chloe and Farid (Paris).

Common questions

Why does my join return more rows than I expected?: Usually the join column is not unique on one side, so each row matches several rows. Check the keys, and test the row count before and after the join.
Does it matter whether a condition is in ON or in WHERE?: For an INNER JOIN it makes no difference. For a LEFT JOIN a condition on the right table in WHERE removes the unmatched rows and turns the LEFT JOIN into an INNER JOIN. Put it in ON to keep them.
Which join is the same as VLOOKUP?: A LEFT JOIN: every row of the main table is kept, and the looked-up column is NULL where nothing matches, like #N/A. Unlike VLOOKUP it returns all matches, not just the first.