Почему и когда LEFT JOIN с условием в условии WHERE не эквивалентно тому же LEFT JOIN в ON?

Ответ 1

Предложение on используется, когда join ищет соответствующие строки. Предложение where используется для фильтрации строк после завершения соединения.

Пример с голосами Диснея для президента:

declare @candidates table (name varchar(50));
insert @candidates values 
    ('Obama'), 
    ('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values 
    ('Mickey Mouse', 'Romney'),
    ('Donald Duck', 'Obama');

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
        and v.voter = 'Donald Duck'

Это все равно возвращает Romney, хотя Donald не голосовал за него. Если вы переместите условие из on в предложение where:

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
where   v.voter = 'Donald Duck'

Romney больше не будет в наборе результатов.

Ответ 2

Оба являются буквально разными.

В первом запросе выполняется фильтрация таблицы t2 до объединения таблиц. Таким образом, результаты будут объединены в таблицу t1, в результате все записи t1 будут показаны в списке.

Вторая фильтрация из общего результата после присоединения таблиц.

Здесь пример

Таблица1

ID   Name
1    Stack
2    Over 
3    Flow

Table2

T1_ID   Score
1       10
2       20
3       30

В первом запросе это выглядит так:

SELECT  a.*, b.Score
FROM    Table1 a
        LEFT JOIN Table2 b
           ON a.ID = b.T1_ID AND
              b.Score >= 20

Что он делает, перед присоединением к таблицам, записи table2 сначала фильтруются по счету. Таким образом, единственные записи, которые будут объединены в таблице 1, будут

T1_ID   Score
2       20
3       30

• SQLFiddle Demo

потому что Score of T1_ID - всего 10. Результат запроса

ID   Name    Score
1    Stack   NULL
2    Over    20
3    Flow    30

• SQLFiddle Demo

Пока второй запрос отличается.

SELECT  a.*, b.Score
FROM    Table1 a
        LEFT JOIN Table2 b
           ON a.ID = b.T1_ID
WHERE   b.Score >= 20

Сначала он записывает записи, имеет ли соответствующая запись в другой таблице или нет. Таким образом, результат будет

ID   Name    Score
1    Stack   10
2    Over    20
3    Flow    30

• SQLFiddle Demo

и фильтрация происходит b.Score >= 20. Таким образом, конечный результат будет

ID   Name    Score
2    Over    20
3    Flow    30

• SQLFiddle Demo

Ответ 3

В первом случае результаты в t2 фильтруются как часть соединения.

Во втором случае в t2 может быть больше строк.

По существу, набор записей, соединенных в два запроса, не будет одинаковым.

Ответ 4

Это имеет значение, потому что во втором случае вы применяете, где ПОСЛЕ того, что левое соединение

Ответ 5

CREATE TABLE Company
(
CompanyId TinyInt Identity Primary Key,
CompanyName Nvarchar(50) NULL
)
GO

INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO

CREATE TABLE Candidate
(
CandidateId tinyint identity primary key,
FullName nvarchar(50) NULL,
CompanyId tinyint REFERENCES Company(CompanyId)
)
GO

INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO

SELECT * from Company c
SELECT * from Candidate c

-- A simple left outer Join
SELECT * FROM Company c LEFT OUTER JOIN Candidate c2
ON c.CompanyId = c2.CompanyId

--Left Outer Join ON and AND condition fetches 5 rows wtih NULL value from right side table 
SELECT * FROM Company c LEFT OUTER JOIN Candidate c2
ON c.CompanyId = c2.CompanyId
AND c.CompanyName = 'DELL' 

--Left Outer Join ON and where clause fetches only required rows
SELECT * FROM Company c LEFT OUTER JOIN Candidate c2
ON c.CompanyId = c2.CompanyId
AND c.CompanyName = 'DELL' 
WHERE c.CompanyName='IBM'