PostgreSQL: ON против WHERE во внешних соединениях - когда перемещение условия меняет результат

Контекст: как PostgreSQL обрабатывает ON и WHERE во внешних соединениях

PostgreSQL строит табличное выражение запроса как конвейер: предложение FROM порождает промежуточную виртуальную таблицу, а WHERE, GROUP BY и HAVING затем её преобразуют. Внутри FROM условие квалифицированного соединения находится в предложении ON (или USING), и оно определяет, какие строки из двух источников считаются совпавшими. Для LEFT OUTER JOIN PostgreSQL сначала выполняет внутреннее соединение, а затем добавляет по одной строке для каждой строки левой стороны, которая ни с чем не совпала, заполняя столбцы правой стороны значениями NULL. Это означает, что предложение ON делает две вещи: оно удаляет несовпадающие комбинации и добавляет строки с расширением NULL для несовпавших левых строк.

WHERE такой роли не имеет. Он выполняется после того, как FROM построил соединённую таблицу, и просто устраняет строки, не удовлетворяющие его условию поиска. Документация явно фиксирует этот порядок: ограничение в предложении ON обрабатывается до соединения, а ограничение в предложении WHERE - после соединения. Для внутренних соединений этот порядок невидим в результате, но для внешних - нет, поскольку предикат WHERE на столбце правой стороны может отбросить именно те строки с расширением NULL, которые внешнее соединение должно было сохранить.

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';; num | name | num | value;-----+------+-----+-------; 1 | a | 1 | xxx;(1 row)

Рекомендация: где размещать условия и компромиссы

Практическое правило: если условие ограничивает допускающую NULL (правую) сторону внешнего соединения, а вы всё ещё хотите получать несовпавшие строки сохраняемой стороны, поместите его в ON. Если условие предназначено для фильтрации итогового результата, включая отбрасывание строк с расширением NULL, поместите его в WHERE. Условия на сохраняемой (левой) стороне LEFT JOIN в обоих местах ведут себя одинаково с точки зрения того, какие левые строки выживут, но размещение их в ON делает соединение самодостаточным и избегает сюрпризов при последующем изменении типа соединения. Для внутренних соединений документация отмечает, что условие соединения может быть записано в WHERE или в предложении JOIN, и выбор - главным образом вопрос стиля; пусть решают читаемость и соглашения команды. Заметьте, что сами внешние соединения должны записываться в предложении FROM; варианта только через WHERE для них не существует.

Компромисс - семантическая ясность против краткости. Смешивание предикатов, не относящихся к соединению, в ON может затруднить чтение запроса, но перенос такого предиката в WHERE молча превращает запрос «сохранить несовпавшие строки» в результат, подобный внутреннему соединению. При ревью или рефакторинге рассматривайте любое перемещение предиката через границу ON/WHERE внешнего соединения как семантическое изменение, а не косметическое.

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';; num | name | num | value;-----+------+-----+-------; 1 | a | 1 | xxx; 2 | b | |; 3 | c | |;(3 rows)

Конкретная иллюстрация: LEFT JOIN с условием на t2.value

Пример из документации использует две таблицы, где t1 содержит строки (1,a), (2,b), (3,c), а t2 - (1,xxx) и (3,yyy). С ограничением внутри ON соединение совпадает только со строкой t2 со значением 'xxx'; строки 2 и 3 из t1 не находят подходящего совпадения и возвращаются со значениями NULL в столбцах t2, что даёт три строки. С тем же ограничением в WHERE соединение сначала совпадает только по num, порождая строки для пар (1,xxx) и (3,yyy), затем WHERE отбрасывает каждую строку, где t2.value не равно 'xxx' - и он также отбрасывает строки с расширением NULL, поскольку NULL = 'xxx' не является истиной. Выживает только одна строка. Приведённые ниже иллюстративные выводы следуют примеру документации; выполните их на собственных данных, чтобы убедиться.

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx'; -- returns 3 rows: (1,a,1,xxx), (2,b,NULL,NULL), (3,c,NULL,NULL) SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx'; -- returns 1 row: (1,a,1,xxx)

Границы применимости

Различие имеет значение только для внешних соединений LEFT, RIGHT и FULL, поскольку только их предложения ON одновременно и удаляют, и добавляют строки. Для INNER JOIN предложения ON и WHERE эквивалентны, и размещение - вопрос стиля. Приведённый здесь совет касается только семантики результата; он ничего не говорит о том, какой план или индекс выберет планировщик, и вопросы производительности по-прежнему требуют измерения фактического плана. Примеры отражают документацию PostgreSQL 18; синтаксис JOIN является стандартным SQL, но проверьте поведение в других СУБД, прежде чем на него полагаться. Также помните, что JOIN связывает сильнее, чем запятая в списке FROM, поэтому смешанные списки из запятых и JOIN могут изменить то, на какие таблицы может ссылаться условие ON.

FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition -- the condition can reference T1 here,;-- but not in the comma-list equivalent;FROM T1, T2 INNER JOIN T3 ON condition