INNER JOIN

INNER JOIN оставляет только сочетания строк, для которых условие ON истинно. Если соответствия нет хотя бы с одной стороны, строка не попадает в результат.

Исходные таблицы

customer:

id	name
1	Анна
2	Борис
3	Вера

purchase:

id	customer_id	total
10	1	900
11	1	400
12	2	700

SELECT c.id, c.name, p.id AS purchase_id, p.total
FROM customer AS c
INNER JOIN purchase AS p
    ON p.customer_id = c.id
ORDER BY p.id;

Результат содержит три покупки. Анна повторится дважды, потому что одной строке customer соответствуют две строки purchase. Вера исчезнет: у неё нет покупки.

Кардинальность

Join не «приклеивает одну строку» автоматически. Для каждой подходящей пары создаётся строка результата:

• one-to-one — не более одной пары на строку;
• one-to-many — родитель повторяется;
• many-to-many — число строк может быстро вырасти;
• условие без нужного ключа способно создать почти декартово произведение.

Поэтому после join проверяют не только значения, но и ожидаемое количество строк.

ON и WHERE

Для inner join фильтр правой таблицы часто эквивалентен в обоих местах:

FROM customer c
JOIN purchase p
  ON p.customer_id = c.id
 AND p.total >= 500

FROM customer c
JOIN purchase p ON p.customer_id = c.id
WHERE p.total >= 500

Оптимизатор обычно строит похожий план. Но ON яснее отделяет условие связи, а WHERE — фильтр итоговых строк. Для outer join перенос между ними меняет смысл.

NULL

Сравнение NULL = NULL не даёт TRUE, поэтому две неопределённые величины не соединяются обычным равенством. Если требуется специальная null-safe семантика, используют возможности конкретной СУБД или явно описывают условие.

Когда нужен только факт существования

Если столбцы правой таблицы не нужны, EXISTS лучше выражает полусоединение:

SELECT c.id, c.name
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM purchase p
    WHERE p.customer_id = c.id
);

Так покупатель возвращается один раз независимо от числа покупок.

На собеседовании

Почему после join стало больше строк? У одной строки нашлось несколько соответствий; SQL возвращает все пары.

Чем join отличается от подзапроса EXISTS? Join формирует пары и может вернуть столбцы обеих сторон; EXISTS проверяет только наличие хотя бы одной строки.

Связанные заметки: Многотабличные запросы операция JOIN, OUTER JOIN, Подзапросы.