JOIN в SQL: свести две таблицы

Один журнал знает, кто заходил. Другой — кто платил. По отдельности оба молчат. Правду скажет не журнал, а их пересечение — то, что совпало в обоих.

Свести два журнала на одного человека — JOIN. По общему ключу строка одного файла находит пару в другом.

SELECT v.person_id, v.time, p.amount
FROM visits v
JOIN bills p
  ON v.person_id = p.person_id
 WHERE v.time = '22:40';

Кого INNER JOIN выбрасывает молча?

Это INNER: остаются только те, у кого пара нашлась в обоих журналах. Кто заходил, но не платил — выпадает вовсе. Иногда именно это и нужно знать: кто не оставил следа во втором журнале.

Для этого — LEFT: оставить всех из первого журнала, а где пары нет — поставить пустоту вместо ответа.

SELECT v.person_id, p.amount
FROM visits v
LEFT JOIN bills p
  ON v.person_id = p.person_id;
-- payment=NULL там, где во втором журнале нет строки

«Сирота без пары — не пустое место. Это тот, кто был здесь и не заплатил, не подписал, не отметился нигде ещё.»

Почему LEFT JOIN вдруг ведёт себя как INNER?

И вот капкан, в который садится каждый второй: если после LEFT JOIN добавить в WHERE условие на столбец из правого журнала, LEFT молча превращается обратно в INNER — WHERE выполняется уже после соединения и выбрасывает как раз тех сирот, ради которых LEFT и брали.

-- было задумано: все посетители + null там, где не платили
SELECT * FROM visits v
LEFT JOIN bills p ON v.person_id=p.person_id
WHERE p.amount > 0;
-- условие на p убило LEFT — снова только те, кто заплатил

Условие на правую таблицу — в ON. В WHERE для анти-джойна оставляют только проверку на пустоту: «пары не нашлось вовсе».

...LEFT JOIN bills p ON v.person_id=p.person_id
WHERE p.person_id IS NULL;
-- ровно те, кто заходил и не заплатил ни разу

«JOIN не удивляет. Он делает то, что вы просили. Беда всегда в том, где именно вы поставили условие.»

На работе так сводят два несвязанных отчёта в один — кто в системе учёта и кто в системе доступа — и ищут разницу между ними. Это и есть самый большой прыжок ремесла: с одной таблицы на связь между двумя.

Два журнала, которые по отдельности молчат, лежат в архиве прямо сейчас. Сведите их — и станет видно, кто был там дважды.