Левое соединение превращается во внутреннее соединение

SELECT
a.foo
b.bar
c.foobar
FROM tableOne AS a
INNER JOIN tableTwo AS b ON a.pk = b.fk
LEFT JOIN tableThree AS c ON b.pk = c.fk
WHERE a.foo = 'something'
AND c.foobar = 'somethingelse'

Имея предложение where и предложение where, похоже, превращают левое соединение во внутреннее соединение. Поведение, которое я вижу, это если в tableThree нет "somethingelse", будет возвращено 0 строк.

Если я перейду c.foobar = 'somethingelse' в предложение join, то сохраненное соединение будет действовать как левое соединение.

    SELECT
    a.foo
    b.bar
    c.foobar
    FROM tableOne AS a
    INNER JOIN tableTwo AS b ON a.pk = b.fk
    LEFT JOIN tableThree AS c ON b.pk = c.fk
    AND c.foobar = 'somethingelse'
    WHERE a.foo = 'something'

Может кто-нибудь указать мне на какую-то документацию, описывающую, почему это происходит? Чрезвычайно важно

Ответ 1

Это из-за вашего предложения WHERE.

Всякий раз, когда вы указываете значение с правой стороны левого соединения в предложении WHERE (которое равно NOT NULL), вы обязательно устраняете все значения NULL и по существу становятся INNER JOIN.

Если вы пишете, AND c.foobar = 'somethingelse' OR c.foobar IS NULL, который решит проблему.

Вы также можете переместить часть c.foobar в предикат соединения, и это тоже решит проблему.

Ответ 2

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

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

Ответ 3

Предложение "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

Ответ 4

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

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

Ответ 5

A Left Join возвращает все из левой таблицы (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" (и nulls в противном случае), затем удалите дополнительный фильтр, добавленный мной c.pk Is Null.

Ответ 6

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

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

Ответ 7

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

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

См

Ответ 8

Он не превращает LEFT JOIN в INNER JOIN, считая, что эффект может оказаться таким же. Когда вы устанавливаете условие WHERE

И c.foobar = 'somethingelse'

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