LEFT JOIN. Dziwny jakiś.

Zdarzyło mi się to w tworzonej aplikacji. Tam problem rozwiązałem inaczej - to co robiłem nie było najlepszym pomysłem, ale i tak dziwi mnie to, co wtedy mi wyszło. W aplikacji miałem do czynienia z około milionem rekordów. Na potrzeby tego wątku przygotowałem znacznie prostszy przykład. Ale nie dopatrujcie się w nim jakiejś logiki.

M$ SQL 2012 EN. Tworzę tabelę. Prostą. Dopisuję dwa rekordy - wystarczy, aby pokazać. Sprawdzam zwykłym SELECT co mam w tabeli i robię pierwszego 'LEFT JOIN', który zresztą daje mi takie rezultaty, jakich się spodziewałem - mam tylko rekordy z "lewej" tabeli. Modyfikuję tego 'JOIN' i niespodzianka - z lewej, dwurekordowej tabeli wchodzi tylko jeden rekord! A gdzie drugi?
Kod:

DECLARE @A TABLE (ROW_NO int, STAT int)
INSERT INTO @A VALUES(1, 1)
INSERT INTO @A VALUES(2, 1)

SELECT * FROM @A

SELECT    a1.ROW_NO AS ROW_NO1, a2.ROW_NO AS ROW_NO2, a1.STAT as STAT1, a2.STAT AS STAT2
FROM @A AS a1
LEFT JOIN @A AS a2 ON a1.ROW_NO + 1 = a2.ROW_NO   and (a1.STAT = 1) AND ((a2.STAT = 2) or (a2.STAT IS NULL))
WHERE                                                 (a1.STAT = 1) AND ((a2.STAT = 2) or (a2.STAT IS NULL))


SELECT    a1.ROW_NO AS ROW_NO1, a2.ROW_NO AS ROW_NO2, a1.STAT as STAT1, a2.STAT AS STAT2
FROM @A AS a1
LEFT JOIN @A AS a2 ON a1.ROW_NO + 1 = a2.ROW_NO --and (a1.STAT = 1) AND ((a2.STAT = 2) or (a2.STAT IS NULL))
WHERE                                                 (a1.STAT = 1) AND ((a2.STAT = 2) or (a2.STAT IS NULL)) 

i wyniki:

to, czego oczekiwałem:

oraz to, czego nie rozumiem:

Nie rozumiem tego - do tej pory myślałem, że rekordy z "lewej" tabeli przechodzą. A warunek drugiego LEFT JOIN jest przecież mniej restrykcyjny.

Żaden rekord nie spełił warunków, które sam napisałeś. Czego tu nie rozumiesz? Warunek drugiego left joina spełnia tylko jeden rekord (2, 1) ale w where go wykluczasz.

Dlaczego w where masz warunek stat = 2 jeżeli nie masz takiego rekordu w tabeli?

Haskell napisał(a):

Żaden rekord nie spełił warunków, które sam napisałeś. Czego tu nie rozumiesz? Warunek drugiego left joina spełnia tylko jeden rekord (2, 1) ale w where go wykluczasz.

Dlaczego w where masz warunek stat = 2 jeżeli nie masz takiego rekordu w tabeli?

Dzięki za odzew. Tak - żaden rekord nie spełnił warunków. To oczywiste - nawet o tym nie pisałem. Ale to jest lewy 'JOIN'. Jak dla mnie to mają wchodzić wszystkie rekordy z lewej tabeli. Najwyżej zostaną połączone z NULL'ami. Tak, jak w pierwszym wyniku. Tam, jak dla mnie, jest dobrze. To rozumiem.

Oczywiście borykałem się z bardziej złożonym problemem i jego logiką biznesową. Tam było milion rekordów. I były takie, które miały STAT wynoszące 2. I tu te 2 w warunku mi zostało. Ot tak. Pisałem, że tu nie ma logiki. No i pierwszy JOIN daje takie wyniki, jakich się spodziewałem. Dostałem 648 rekordów z miliona. I dobrze. Ale popatrzyłem i postanowiłem uprościć zapytanie. Po co taka sama logika wykluczająca rekordy w WHERE i jeszcze w JOIN, zapytałem siebie. I zakomentowałem, jak widać, kawałek warunku z JOIN. I dostałem 247 rekordów. Buuu...
No to cierpliwie przygotowałem powyższy przykład. Przecież nie będę tu wrzucał miliona rekordów.
Jeszcze inaczej - warunek WHERE nie zmienia się a warunek JOIN zmienia się na mniej restrykcyjny. Dlaczego pierwsze, bardziej restrykcyjne zapytanie, daje więcej rekordów? Daje 2 rekordy. A drugie, mniej restrykcyjne, daje 1?

Prze LEFT (RIGHT) JOIN jest rónica, gdzie dasz warunek - czy we WHERE, czy w JOIN. Jeśli dasz we WHERE, to przefiltrowane będziesz miał rekordset wynikowy, jeśli zaś w JOIN, to dołączany, czyli tak jakbyś zrobił:

SELECT * FROM tab1 LEFT JOIN (SELECT * FROM tab1 WHERE id>10) tab2 ON tab1.id=tab2.id
ORDER BY tab1.id

tu będziesz miał pierwszych 10 rekordów pustych (zakładając, że id jest ciągłe i od 1). I to jest to samo co:

SELECT * FROM tab1 LEFT JOIN tab1 tab2 ON tab1.id=tab2.id AND tab2.id>10
ORDER BY tab1.id

ale nie to samo co:

SELECT * FROM tab1 LEFT JOIN tab1 tab2 ON tab1.id=tab2.id 
WHERE tab2.id>10
ORDER BY tab1.id

Marcin.Miga napisał(a):

Prze LEFT (RIGHT) JOIN jest rónica, gdzie dasz warunek - czy we WHERE, czy w JOIN. Jeśli dasz we WHERE, to przefiltrowane będziesz miał rekordset wynikowy, jeśli zaś w JOIN, to dołączany, czyli tak jakbyś zrobił:

SELECT * FROM tab1 LEFT JOIN (SELECT * FROM tab1 WHERE id>10) tab2 ON tab1.id=tab2.id
ORDER BY tab1.id

tu będziesz miał pierwszych 10 rekordów pustych (zakładając, że id jest ciągłe i od 1). I to jest to samo co:

SELECT * FROM tab1 LEFT JOIN tab1 tab2 ON tab1.id=tab2.id AND tab2.id>10
ORDER BY tab1.id

ale nie to samo co:

SELECT * FROM tab1 LEFT JOIN tab1 tab2 ON tab1.id=tab2.id 
WHERE tab2.id>10
ORDER BY tab1.id

Dzięki za odzew.

U mnie warunek WHERE jest w obu zapytaniach. U Ciebie pojawia się dopiero w ostatnim. Wycięty z JOIN. Czyżbyś sugerował, że gdy dołożę do Twojego drugiego zapytania WHERE z trzeciego, to ten dołożony WHERE zostanie zignorowany i zapytanie dlatego właśnie nadal zwróci więcej rekordów? Więcej niż on pozwala zwrócić w trzecim zapytaniu?

A powiedz mi, które masz rekordy spełniające w JOIN te warunki: (a2.STAT = 2) OR (a2.STAT IS NULL) ?. Zwłąszcza ten drugi...

Marcin.Miga napisał(a):

A powiedz mi, które masz rekordy spełniające w JOIN te warunki: (a2.STAT = 2) OR (a2.STAT IS NULL) ?. Zwłąszcza ten drugi...

Nie mam takich. To oczywiste. Ale oba rekordy w tabeli spełniają warunek LEFT. No bo po prostu są po lewej. A z czym się łączą. No z tym, co określa warunek w JOIN. Czyli u mnie z niczym. Mamy NULL'e. Tu w obu wynikowych rekordach.
Przynajmniej ja tak rozumiem LEFT JOIN. No a jak już połączenie jest zrobione - wiemy, co będzie w rekordach - to możemy je jeszcze filtrować warunkiem w WHERE. I do wyniku z pierwszego mojego LEFT JOIN nie mam zastrzeżeń. WHERE nie wywalił wynikowych rekordów mimo, że żaden nie spełnia jego warunku - każdy jeden pochodził z innego rekordu z lewej tabeli i nie może być usunięty. Mimo, że JOIN z niczym tego nie połączył.

Ale dlaczego wywalił z drugiego zestawu? Gdybym w drugim JOIN nie dał WHERE (można np zakomentować) to też bym dostał dwa rekordy - jeden nawet by spełniał warunek w JOIN. Drugi by się pojawił bo jest rekord po lewej. No ale z warunkiem WHERE ten spełniający warunek JOIN został usunięty. Dlaczego ten? Przecież oba nie spełniają warunku WHERE. No to dlaczego jeden z nich znikł. I w sumie ten "lepszy" jakby? Ten "połączony"? Albo dlaczego nie znikły oba?

No i przepraszam. Właśnie zauważyłem, że budując ostatnią tabelkę wyniku nie usunąłem pierwszego z wynikowych wierszy poprzedniej tabeli. Tam powinno być oczywiście:

ROW_NO1 | ROW_NO2 | STAT1 | STAT2
---------------- | -------------------
2 | NULL | 1 | NULL

dziadu napisał(a):

Marcin.Miga napisał(a):

A powiedz mi, które masz rekordy spełniające w JOIN te warunki: (a2.STAT = 2) OR (a2.STAT IS NULL) ?. Zwłąszcza ten drugi...

Nie mam takich. To oczywiste. Ale oba rekordy w tabeli spełniają warunek LEFT.

No nie. Warunek w JOIN na tabelę lewą też działa...
Warunek a1.ROW_NO + 1 = a2.ROW_NO wybierze ci z (a1.ROW_NO=1, a2.ROW_NO=2), a że jest LEFT, to dorzuci (a1.ROW_NO=2, NULL)
Ale kolejny warunek a1.STAT = 1 od razu ten drugi rekord odrzuci. Tu warunek JOIN zadziałał jak WHERE

Marcin.Miga napisał(a):

dziadu napisał(a):

Marcin.Miga napisał(a):

A powiedz mi, które masz rekordy spełniające w JOIN te warunki: (a2.STAT = 2) OR (a2.STAT IS NULL) ?. Zwłąszcza ten drugi...

Nie mam takich. To oczywiste. Ale oba rekordy w tabeli spełniają warunek LEFT.

No nie. Warunek w JOIN na tabelę lewą też działa...
Warunek a1.ROW_NO + 1 = a2.ROW_NO wybierze ci z (a1.ROW_NO=1, a2.ROW_NO=2), a że jest LEFT, to dorzuci (a1.ROW_NO=2, NULL)

Z tym się zgadzam

Ale kolejny warunek a1.STAT = 1 od razu ten drugi rekord odrzuci. Tu warunek JOIN zadziałał jak WHERE

A tu chyba chciałeś coś innego napisać. Oba rekordy tabeli źródłowej mają STAT = 1. Nie ma podstaw do odrzucania. Dodam jeszcze, że (STAT=2) OR (STAT IS NULL) jest zawsze fałszem i jeśli to miałoby odrzucać, no to chyba wszystkie rekordy wynikowe.

PS. Dzięki za wytrwałość.

Właśnie ten warunek odrzuca: (a2.STAT = 2) OR (a2.STAT IS NULL), a konkretnie ta część: (a2.STAT IS NULL)
filtrowanie WHERE filtruje już sam wynik.
zapytanie:

SELECT    a1.ROW_NO AS ROW_NO1, a2.ROW_NO AS ROW_NO2, a1.STAT AS STAT1, a2.STAT AS STAT2
FROM @A AS a1
LEFT JOIN @A AS a2 ON (a1.ROW_NO + 1 = a2.ROW_NO)

zwraca:

ROW_NO1 (a1.row_no) | ROW_NO2 (a2.row_no) | STAT1 (a1.stat)| STAT2 (a2.stat)
---------------- | -------------------
1 | 2 | 1 | 1
2 | NULL | 1 | NULL

a teraz dodajemy warunki:

• a1.stat=1 - ten warunek spełniają oba rekordy
• (a2.stat=2) or (a2.stat is null) - żaden z 2 wierszy nie ma statusu równego 2, ale za to status drugiego jest null.

Tak właśnie działa LEFT / RIGHT JOIN - wypełnia podrzędną tabelę (dla left tą z prawej, dla right tą z lewej) NULL'ami.
I takie działanie pozwala za pomocą LEFT JOIN wybrać wszystkie rekordy nie mające powiązań w tabeli podrzędnej - zamiast NOT IN lub NOT EXISTS

Wreszcie do mnie dotarło. Wielkie dzięki.