LEFT JOIN и WHERE в SQL: куда пропадают строки

Сначала определите результат и подготовьте данные

Требование: показать всех учеников и их успешные попытки, включая учеников без успехов. У Ada две успешные попытки, у Boris одна неуспешная, у Chen попыток нет. Эти случаи позволяют отличить неподходящее совпадение от полного отсутствия совпадений.

Запускайте подготовку и последующие запросы в одной сессии PostgreSQL: таблицы временные. Первичные ключи не допускают NULL. До написания запроса решите, нужна одна строка на попытку или на ученика — это разная детализация результата.

CREATE TEMP TABLE learners (
  id integer PRIMARY KEY,
  name text NOT NULL
);
CREATE TEMP TABLE attempts (
  id integer PRIMARY KEY,
  learner_id integer NOT NULL REFERENCES learners(id),
  status text NOT NULL
);
INSERT INTO learners VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO attempts VALUES
  (10, 1, 'passed'), (11, 1, 'passed'), (12, 2, 'failed');

Почему фильтр WHERE удаляет учеников

Этот запрос возвращает только попытки Ada с идентификаторами 10 и 11. У Boris есть присоединённая строка, но status равен failed. Для Chen правая сторона дополнена NULL, а NULL = 'passed' даёт неизвестный результат. WHERE сохраняет только истинные условия, поэтому оба ученика исчезают.

Для конкретно этого условия на правую сторону набор строк совпадает с INNER JOIN и таким фильтром. Это не означает, что любой WHERE превращает LEFT JOIN во внутреннее соединение: условие на левую таблицу или условие, принимающее NULL, может работать иначе.

SELECT l.name, a.id AS attempt_id
FROM learners l
LEFT JOIN attempts a ON a.learner_id = l.id
WHERE a.status = 'passed'
ORDER BY l.id, a.id;

• PostgreSQL: joins and table expressions

Перенесите условие в ON для сохранения всех учеников

Теперь совпадениями считаются только успешные попытки. Неуспешная попытка Boris исключается на этапе сопоставления, поэтому ему достаётся строка с NULL справа, как и Chen. У Ada по-прежнему два совпадения: LEFT JOIN не обещает одну строку на левую сущность.

Добавление OR a.id IS NULL к прежнему WHERE не равнозначно этому исправлению. Оно вернёт Chen, у которого не было попыток, но не Boris: его неуспешная попытка уже совпала в исходном соединении. Такой контрпример хорошо проверяет предлагаемый ремонт запроса.

SELECT l.name, a.id AS attempt_id
FROM learners l
LEFT JOIN attempts a
  ON a.learner_id = l.id AND a.status = 'passed'
ORDER BY l.id, a.id;

Считайте совпадения, а не сохранённую строку

Если нужна одна строка на ученика, агрегируйте на этом уровне. Запрос вернёт Ada 2, Boris 0, Chen 0. COUNT(a.id) пропускает NULL из дополненной строки. COUNT(*) посчитал бы саму сохранённую строку и ошибочно показал 1 для Boris и Chen.

Выбор a.id намеренный: для настоящей попытки этот первичный ключ не может быть NULL. Подсчёт по nullable-полю бизнеса мог бы пропустить реальные совпадения. Если позже добавится другая связь один-ко-многим, проверьте размножение строк перед тем, как считать новый результат количеством попыток.

SELECT l.name, COUNT(a.id) AS passed_attempts
FROM learners l
LEFT JOIN attempts a
  ON a.learner_id = l.id AND a.status = 'passed'
GROUP BY l.id, l.name
ORDER BY l.id;

• PostgreSQL: aggregate functions and COUNT

Ищите отсутствие успехов через NOT EXISTS

Если нужны только ученики без успешных попыток, NOT EXISTS прямо выражает этот вопрос. Запрос возвращает Boris и Chen по одному разу. Его смысл остаётся тем же, если у Ada появятся новые успешные попытки: нам важен факт отсутствия подходящей записи.

Не утверждайте, что эта форма всегда быстрее антисоединения через LEFT JOIN. План зависит от индексов, статистики и данных. Сначала докажите эквивалентность результатов, затем сравнивайте планы на одинаковых параметрах и реалистичных объёмах.

SELECT l.name
FROM learners l
WHERE NOT EXISTS (
  SELECT 1 FROM attempts a
  WHERE a.learner_id = l.id AND a.status = 'passed'
)
ORDER BY l.id;

• PostgreSQL: EXISTS subqueries

Как проверять внешний JOIN на собеседовании

При изменении внешнего соединения проверяйте три случая: справа нет записи, есть только неподходящие записи и есть несколько подходящих. Ожидаемый результат запишите до выполнения запроса. Единственное успешное совпадение не обнаружит ни исчезновение строк, ни ошибку агрегации.

Объясняйте решение в порядке: детализация результата, условие сопоставления, фильтрация строк, агрегация. Для общей подготовки используйте сборник SQL, а для следующего требования — ранжирования попыток внутри ученика — практику оконных функций.

• 100 вопросов по SQL, PostgreSQL и базам данных
• Практика оконных функций SQL в PostgreSQL

Коротко

Частые вопросы