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

Я испытываю очень запутанную ситуацию, которая заставляет меня подвергнуть сомнению все мое понимание объединений в SQL Server.

SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something 

Не дает таких же результатов, как:

 SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 WHERE t2.f3 > something 

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

Спасибо

Solutions Collecting From Web of "Почему и когда LEFT JOIN с условием в условии WHERE не эквивалентно тому же LEFT JOIN в ON?"

Предложение 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 больше не будет в результирующем наборе.

Оба они буквально разные.

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

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


Вот пример

Таблица 1

 ID Name 1 Stack 2 Over 3 Flow 

Таблица 2

 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 

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

 T1_ID Score 2 20 3 30 
  • Демоверсия SQLFiddle

потому что Score T1_ID равен только 10. Результат запроса

 ID Name Score 1 Stack NULL 2 Over 20 3 Flow 30 
  • Демоверсия SQLFiddle

Второй запрос отличается.

 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

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

 ID Name Score 2 Over 20 3 Flow 30 
  • Демоверсия SQLFiddle

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

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

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

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

 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'