Dlaczego i kiedy lewy JOIN with condition in WHERE nie jest równoznaczny z tym samym lewym JOIN in ON? [duplikat]

To pytanie ma już odpowiedź tutaj:

Doświadczam bardzo zagmatwanej sytuacji to sprawia, że kwestionuję całe moje zrozumienie łączy w SQL Server.

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

Nie daje takich samych wyników jak: 

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

Czy ktoś może pomóc, mówiąc, czy te dwa pytania mają być równoważne, czy nie?

Thx

Author: John Woo, 2013-03-29
Source

5 answers

Klauzula on jest używana, gdy join szuka pasujących wierszy. Klauzula where służy do filtrowania wierszy po zakończeniu łączenia.

Przykład z głosowaniem na prezydenta Disneya: 
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'

To wciąż powraca Romney mimo, że Donald nie głosowało na niego. Jeśli przeniesiesz warunek z {[2] } do where klauzuli: 

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

Romney nie będzie już w zestawie wyników.

 32
Author: Andomar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-29 15:29:53

Oba są dosłownie różne.

Pierwsze zapytanie wykonuje filtrowanie tabeli t2 przed połączeniem tabel. Tak więc wyniki zostaną dołączone do tabeli t1 w wyniku czego wszystkie rekordy t1 zostaną pokazane na liście.

Drugi filtruje wynik po zakończeniu łączenia tabel.

Oto przykład

Table1 

ID   Name
1    Stack
2    Over 
3    Flow

Table2 

T1_ID   Score
1       10
2       20
3       30

W pierwszym zapytaniu wygląda to tak: to, 

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

To, co robi przed dołączeniem do tabel, rekordy {[11] } są filtrowane najpierw przez wynik. Tak więc jedynymi rekordami, które zostaną dołączone do table1 są 

T1_ID   Score
2       20
3       30

Ponieważ Score z T1_ID jest tylko 10. Wynikiem zapytania jest 

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

Podczas gdy drugie zapytanie jest inne.

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

Najpierw dołącza do rekordów, czy ma pasujący rekord na drugiej tabeli lub nie. Więc wynik będzie 

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

I filtrowanie odbywa się b.Score >= 20. Tak więc końcowy wynik będzie 

ID   Name    Score
2    Over    20
3    Flow    30
 17
Author: John Woo,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-29 15:37:51

W pierwszym przypadku wyniki w {[0] } są filtrowane jako część połączenia.

W drugim przypadku może być więcej wierszy dostępnych od t2.

Zasadniczo zestaw rekordów połączonych w dwóch zapytaniach nie będzie taki sam.

 0
Author: Ryan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-29 15:21:19

To robi różnicę, ponieważ w drugim przypadku stosujesz where po lewej stronie

 0
Author: Brian,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-29 15:22:00 
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'
 0
Author: mr_eclair,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-29 15:41:47