[MySql] JOIN a WHERE Opcje

[Petre]

Witajcie

pytam wujka google, ale nie daje mi jednoznacznej odpowiedzi.

Jaka jest różnica między np:

[SQL] pobierz, plaintext 
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID=12345
[SQL] pobierz, plaintext 

dokładnie rzecz ujmując chodzi mi o zastosowanie WHERE oraz AND za ON. Która wersja jest poprawniejsza i bardziej wydajna w przypadku większej ilości połączeń?

[nospor]

Drugie zapytanie zwróci ci wszystkie rekordy z ORDERS, podczas gdy pierwsze, tylko te rekordy, które spełniają warunek. Przecież to oczywiste

PIerwsze zapytanie połączy ci wszystkie pasujące rekordy z OrderLines. Drugie tylko z podanym ID. To przecież również oczywiste i widoczne

[Petre]

OK, a w przypadku zamiast LEFT JOIN jest tylko JOIN ?
Jest to ekwiwalentne rozwiązanie ?

[nospor]

Nie. LEFT JOIN zwraca rekordy z FROM nawet jak nie ma połączenia z LEFT JOIN.
JOIN zaś wymusza istnienie wiązania, w przeciwnym wypadku nawet z FROM nic nie pójdzie.

Czyli jakbyś dał JOIN zamiast LEFT JOIN, to oba zapytania dałyby ten sam wynik.

[Petre]

tak tak .

Chodzi mi o taki przypadek :

[SQL] pobierz, plaintext 
SELECT *
FROM Orders
JOIN OrderLines ON Orders.ID=OrderLines.OrderID
WHERE Orders.ID = 12345
[SQL] pobierz, plaintext 

oraz

[SQL] pobierz, plaintext 
SELECT *
FROM Orders
JOIN OrderLines ON Orders.ID=OrderLines.OrderID AND Orders.ID=12345
[SQL] pobierz, plaintext 

jak rozumiem w obu przypadkach z tabel zostaną zwrócone wartości spełniające warunki:
w Orders istnieje ID o numerze 12345
w OrderLines istnieje OrderId = 12345


Orders.ID | | OrderLines.OrderID || OrderLines.ID || OrderLines.Name
---------------------------------------------------------------
1 || 1 || 1 || test
1 || 1 || 2 || innePole
1 || 1 || 3 || drugiePole





wracając natomiast od początkowego przypadku i lekko go modyfikując chodziło raczej o :

[SQL] pobierz, plaintext 
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID=OrderLines.OrderID
WHERE Orders.ID = 12345
[SQL] pobierz, plaintext 

oraz

[SQL] pobierz, plaintext 
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID=OrderLines.OrderID AND Orders.ID=12345
[SQL] pobierz, plaintext 

W obu przypadkach zostaną zwrócone te same wartości . Mam rację ?


EDIT: widze , ze wyedytowales wiadomosc: ) czyli to pierwsze zalozenie sie zgadza.

Ten post edytował Petre 27.02.2013, 12:34:49

[nospor]

jak rozumiem w obu przypadkach z tabel zostaną zwrócone wartości spełniające warunki:
w Orders istnieje ID o numerze 12345
w OrderLines istnieje OrderId = 12345

Tak

W obu przypadkach zostaną zwrócone te same wartości . Mam rację ?

Nie. Przecież już ci to wyjasniłem w pierwszym poście. Jeśli nawet zostaną zwrócone te same wartosci, to tylko i wyłącznie przez przypadek, że akurat tak sie warunki złożyły, że oba zwracają to samo.

[Sephirus]

Dodam jedynie, że w niektórych przypadkach (jak użyjesz samo JOIN) jest różnica pomiędzy zastosowanie WHERE a dodaniem warunku do ON. Polega ona na wydajności i pamięciożerności gdyż MySQL przy joinach tworzy tabele tymczasowe:

jeśli masz tabelę A i B i je łączysz to otrzymujesz tak naprawdę tabelę C najpierw, która jest połączenie A i B. Jeśli zastosujesz warunek w ON tabela C będzie mniejsza bo od razu zostanie wygenerowana z takim warunkiem. Jeśli wrzucisz warunek w WHERE to zostanie wygenerowana tabela C bez tego ograniczenia czyli o wiele większa. Dopiero potem na niej zostają zastosowane warunki z WHERE.

Oczywiście w zależności od silnika/wersji itd MySQL w niektórych (prostych) przypadkach sam "kapnie się", że w WHERE ma warunek, który powinien być w ON ale liczyć na to to przesada.

Problem wydaje się trywialny i niegroźny ale przy łączeniu naprawdę obszernych tabel tabela tymczasowa może być naprawdę ogromna, nie mówiąc już o tym że joinów może być więcej niż dwa. Sam spotkałem sie z tym problemem gdy MySQL krzyczał dla zapytania, że zabrakło mu miejsca na utworzenie tabeli tymczasowej (na tabelach o wielu milionach rekordów - nawet nie tak strasznie dużo). Rozwiązaniem okazało się właśnie przeniesienie warunków do ON oraz jeszcze mocniejsze ograniczenie tabeli tymczasowej poprzez większą liczbę bardziej precyzujących warunków.

Tym też się różnią te zapytania (dla samego INNER JOIN - dla LEFT nie ma to takiego znaczenia w tym konkretnym przypadku).

Co do reszty zgadzam się w stu procentach z Kubusiowym moderatorem

[Petre]

Tak

Nie. Przecież już ci to wyjasniłem w pierwszym poście. Jeśli nawet zostaną zwrócone te same wartosci, to tylko i wyłącznie przez przypadek, że akurat tak sie warunki złożyły, że oba zwracają to samo.

Wziales pod uwage zmieniony warunek polaczenia ON ? odwrocilem kolejnosc na Orders.ID=OrderLines.OrderId

[nospor]

No i co z tego że odwróciłeś kolejność? Wyjasnienie na temat LEFT JOIN podałem ci w pierwszym poście i nic tego nie zmienia...

Drugie zapytanie zwróci ci wszystkie rekordy z ORDERS, podczas gdy pierwsze, tylko te rekordy, które spełniają warunek. Przecież to oczywiste

LEFT JOIN zwraca rekordy z FROM nawet jak nie ma połączenia z LEFT JOIN.

Połącz w końcu te dwa fakty

Powód edycji: [nospor]:

[Petre]

Dodam jedynie, że w niektórych przypadkach (jak użyjesz samo JOIN) jest różnica pomiędzy zastosowanie WHERE a dodaniem warunku do ON. Polega ona na wydajności i pamięciożerności gdyż MySQL przy joinach tworzy tabele tymczasowe:

jeśli masz tabelę A i B i je łączysz to otrzymujesz tak naprawdę tabelę C najpierw, która jest połączenie A i B. Jeśli zastosujesz warunek w ON tabela C będzie mniejsza bo od razu zostanie wygenerowana z takim warunkiem. Jeśli wrzucisz warunek w WHERE to zostanie wygenerowana tabela C bez tego ograniczenia czyli o wiele większa. Dopiero potem na niej zostają zastosowane warunki z WHERE.

Oczywiście w zależności od silnika/wersji itd MySQL w niektórych (prostych) przypadkach sam "kapnie się", że w WHERE ma warunek, który powinien być w ON ale liczyć na to to przesada.

Problem wydaje się trywialny i niegroźny ale przy łączeniu naprawdę obszernych tabel tabela tymczasowa może być naprawdę ogromna, nie mówiąc już o tym że joinów może być więcej niż dwa. Sam spotkałem sie z tym problemem gdy MySQL krzyczał dla zapytania, że zabrakło mu miejsca na utworzenie tabeli tymczasowej (na tabelach o wielu milionach rekordów - nawet nie tak strasznie dużo). Rozwiązaniem okazało się właśnie przeniesienie warunków do ON oraz jeszcze mocniejsze ograniczenie tabeli tymczasowej poprzez większą liczbę bardziej precyzujących warunków.

Tym też się różnią te zapytania (dla samego INNER JOIN - dla LEFT nie ma to takiego znaczenia w tym konkretnym przypadku).

Co do reszty zgadzam się w stu procentach z Kubusiowym moderatorem

Właśnie staram się zoptymalizować zapytania do bazy . Poprzednicy wrzucili wszędzie WHERE bez jakichkolwiek joinów. Muszę naprawić błędy.
Bardzo przydatna informacja rozróżniająca oba typy.

No i co z tego że odwróciłeś kolejność? Wyjasnienie na temat LEFT JOIN podałem ci w pierwszym poście i nic tego nie zmienia...

Połącz w końcu te dwa fakty

Nie wiem czemu, ale ciągle myślałem, ze LEFT JOIN mogę dowolnie obracać (sterowac) za pomocą ON i że to on warunkuje to połączenie, a nie kolejność tabel ... przepraszam .


Ok to coś z kwiatków.

Jak rozumiem pj.id_lang=1 musi pozostać w WHERE aby był brany pod uwagę inaczej jeżeli dodam go do

[SQL] pobierz, plaintext 
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND p.stan=1 AND p.detal=1
[SQL] pobierz, plaintext 

nie będzie miał wpływu na wyniki zapytania.

Chcę wyrzucić informacje o produkcie.
Każdy produkt ma połączenie z 5 wersjami językowymi w tabeli produkty_jezyki (połączenie id_prod).
Produkt może być w wyprzedazy (tabela wyprzedaze , połączenie id_prod)
Produkt nalezy do zestawu (ze wzgledu, iz jest to typ polaczenia WIELE do WIELE zastosowalem tabele laczaca Zestawy -> zestawy_produkty ->produkty)
Produkt moze byc w promocji (promocja moze dotyczyc wszystkich wersji jezykowych lub tylko wybranych)

[SQL] pobierz, plaintext 
SELECT r.id_kat, p.id_gr_prod, p.data_add AS data_add,p.id_rodz, w.stan wyprzedaz, w.ilosc, p.m_nowosc,p.id_prod,p.zdj,  pj.nazwa, pj.opis, pj.cena_brutto, pj.cena_hurt_brutto, pj.cena_hurt_netto, p.symbol,
pr.id_prom, IF (p.pozycja=0 OR p.pozycja IS NULL,'last',p.pozycja) AS pozycja 
FROM produkty p
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND p.stan=1 AND p.detal=1
JOIN rodzaje r ON p.id_rodz=r.id_rodz
JOIN zestawy_produkty zp ON p.id_prod = zp.id_prod AND zp.id_zest=69
LEFT JOIN promocje pr ON pr.id_prod_l=pj.id_prod_l 
LEFT JOIN wyprzedaz w ON p.id_prod=w.id_prod
 
WHERE pj.id_lang=1;
 
GROUP BY p.id_prod
ORDER BY p.pozycja ASC
[SQL] pobierz, plaintext 

Jak oceniacie to zapytanie




Ten post edytował Petre 27.02.2013, 13:09:59

[nospor]

Jak rozumiem pj.id_lang=1 musi pozostać w WHERE aby był brany pod uwagę inaczej jeżeli dodam go do
[SQL] pobierz, plaintext

LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND p.stan=1 AND p.detal=1

nie będzie miał wpływu na wyniki zapytania.

Jak będzie w LEFT JOIN, to będzie miał tylko wpływ na LEFT JOIN. Całość z FROM zignoruje ten warunek

Ale można to inaczej obejsc:
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND p.stan=1 AND p.detal=1 and pj.id_lang=1;
...
WHERE pj.id_lang is not null

[Petre]

Jak będzie w LEFT JOIN, to będzie miał tylko wpływ na LEFT JOIN. Całość z FROM zignoruje ten warunek

Ale można to inaczej obejsc:
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND p.stan=1 AND p.detal=1 and pj.id_lang=1;
...
WHERE pj.id_lang is not null

tak tylko pj.id_lang może przyjąć wartość od 1 do 5 (w zaleznosci od wersji jezykowej , nie zaznaczylem tego predzej), a ja akurat potrzebuje 1.

Puchatku, gdzie mieszkasz w Stumilowym Lesie ? Nie wiem na jaki adres wysłać skrzynkę z małym conieco

Ten post edytował Petre 27.02.2013, 13:17:23

[nospor]

O... małe co nieco... łechczesz mój brzuszek tymi słowami

[sazian]

ja bym dał część warunku z pierwszego LEFT JOIN do WHERE, a warunek z WHERE do pierwszego LEFT JOIN

[SQL] pobierz, plaintext 
FROM produkty p
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND  pj.id_lang=1
WHERE  p.stan=1 AND p.detal=1
[SQL] pobierz, plaintext 

chociaż mogę się mylić nie znając dokładniej sytuacji

warto by też pomyśleć o wywaleniu GROUP BY, może da się do zastąpić jakimś warunkiem ? GROUP BY ma to do siebie że jest dosyć wolny i zasobożerny

zrób EXPLAIN tego zapytania i zobacz co wyjdzie, co jest bardziej optymalne