[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ń?

[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 (IMG:style_emoticons/default/wink.gif)

[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 (IMG:style_emoticons/default/wink.gif)

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 (IMG:style_emoticons/default/questionmark.gif)




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