оставленное соединение превращается во внутреннее объединение

Предложение where выполняется после соединения. Это не имеет значения для внутренних соединений, но имеет значение для внешних соединений.

Сократить пример

SELECT b.bar,c.foobar FROM tableTwo AS b LEFT JOIN tableThree AS c ON b.pk=c.fk WHERE c.foobar='somethingelse'

Raw data                  Outer Join Result        Select Result
b.pk c.fk c.foorbar       b.pk c.fk c.foorbar      c.foorbar
1    1    something       1    1    something      <not in result set>
1    1    somethingelse   1    1    somethingelse  somethingelse


SELECT b.bar,c.foobar FROM tableTwo AS b LEFT JOIN tableThree AS c ON b.pk=c.fk AND c.foobar='somethingelse'

Raw data                  Outer Join Result        Select Result
b.pk c.fk c.foorbar       b.pk c.fk c.foorbar      c.foorbar
1    1    something       1    null null           null
1    1    somethingelse   1    1    somethingelse  somethingelse

Причина, по которой вы это видите, заключается в том, что левое соединение устанавливает для всех столбцов c значение NULL для тех строк, которые не существуют в c (т.е. не может быть соединен). Это означает, что сравнение c.foobar = 'somethingelse' неверно, поэтому эти строки не возвращаются.

В случае, когда вы перемещаете c.foobar = 'somethingelse' в условие соединения, это соединение по-прежнему возвращает эти строки (хотя и со значениями NULL), когда условие не выполняется.

Объединения выполняют свою работу, затем происходит удаление записей, где c.foobar <> 'somethingelse'.

Эффект выглядит как внутреннее соединение, но на самом деле это не так.

Левое соединение возвращает все из левой таблицы (tableTwo в вашем примере) и любые совпадающие строки из таблицы справа (tableThree в вашем примере). Когда вы фильтруете что-то в правой части левого соединения (например, tableThree) и не учитываете несовпадающие значения, вы фактически требуете, чтобы значение существовало и чтобы это значение было «чем-то», что является эквивалентом внутреннего присоединиться. Если вы пытаетесь найти все строки tableTwo, в которых нет строки в tableThree со значением foobar, равным 'something', вы можете переместить фильтрацию в предложение on:

Select a.foo, b.bar, c.foobar
From tableOne As a
    Inner Join tableTwo as b
        On b.fk = a.pk
    Left Join tableThree as c
        On c.fk = b.pk
            And c.foobar = 'something'
Where a.foo = 'something'
    And c.pk Is Null

Последнее добавление, c.pk Is Null фильтрует значения, которые не имеют значения tableThree со значением foobar «something». Если вы просто хотите увидеть значения tableThree, когда они имеют значение foobar «something» (и null в противном случае), затем удалите добавленный мной дополнительный фильтр c.pk Is Null .

Он не превращает ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ СОЕДИНЕНИЕ, хотя эффект может быть таким же. Когда вы устанавливаете условие WHERE

AND c.foobar = 'somethingelse'

, вы избавляетесь от всех случаев, когда LEFT JOIN TO действует так же, как и он. В этом случае некоторые значения для c.foobar будут NULL. Установка этого параметра в условии JOIN по-прежнему разрешает несоответствующие результаты LEFT JOIN, ограничивая только то, что возвращается для результатов C.

1-й случай (, где c.foobar находится в предложении where ) фильтрация с помощью c.foobar происходит после того, как соединения были выполнены ( они появляются правильно ), поэтому вы эффективно отфильтровываете все результирующие записи, в которых нет 'somethingelse' ..

2-й случай c.foobar является частью соединения, поэтому оно оценивается во время соединения и просто управляет выводом соединения, а не окончательным набором записей (возвращает null в случае сбоя соединения) ..

LEFT JOIN производит NULL там, где нет совпадающих строк. В этом случае c.foobar будет NULL для несовпадающих строк. Но ваше предложение WHERE ищет конкретное значение: 'somethingelse', и поэтому отфильтрует все значения NULL. Поскольку INNER JOIN также не дает значений NULL с правой стороны, они выглядят одинаково. Вы можете добавить 'OR c.foobar IS NULL', чтобы разрешить возвращение нулевых значений.

Когда вы перемещаете условие в предложение ON, оно становится частью сопоставления строк JOIN, а не финальным фильтром. Сопоставление соединения может завершиться неудачно, и внешнее соединение затем возвращает NULL в случаях, когда c.foobar имеет значение NULL или не somethingelse.

См.

• IS NULL
• Соединения SQL