Jawne i niejawne sprzężenia SQL


399

Czy jest jakaś różnica wydajności w jawnym a niejawnym sprzężeniu wewnętrznym? Na przykład:

SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;

vs.

SELECT a.*, b.*
FROM table a, table b
WHERE a.id = b.id;

11
Dobre pytanie. Jestem ciekawy, dlaczego w ogóle używane jest jawne łączenie. Czy bez tego nie można wykonać wszystkich zapytań?
andrew

6
użyj słowa kluczowego EXPLAIN, aby poznać różnicę w obu zapytaniach .. użyj DOŁĄCZ i zobacz różnicę. Jeśli spróbujesz w tabeli ponad 100
000

@andrew Moje pytanie dotyczyło w rzeczywistości tego, czy niejawne sprzężenie było formą „włamania” (jak w „Zapytaniu obejmującym więcej niż jedną tabelę, bez użycia sprzężenia? To włamanie, prawda?”)
Bobobobo

3
Są różne, niejawne łączenie zaskakuje cię od czasu do czasu w przypadku wartości zerowych; używaj jawnego łączenia i unikaj błędów, które pojawiają się, gdy „nic się nie zmieniło!”
BlackTigerX

1
Nie ma różnicy. ,jest CROSS JOINz luźniejsze wiążący INNER JOINjest CROSS JOINz ONniczym WHERE, ale mocniej wiążące. Dla wykonania ważne jest, w jaki sposób DBMS optymalizuje zapytania.
philipxy

Odpowiedzi:


132

Pod względem wydajności są dokładnie takie same (przynajmniej w SQL Server).

PS: Należy pamiętać, że IMPLICIT OUTER JOINskładnia jest przestarzała od SQL Server 2005. ( IMPLICIT INNER JOINSkładnia użyta w pytaniu jest nadal obsługiwana)

Przestarzała składnia JOIN „Old Style”: tylko część rzeczy


4
@lomaxx, dla jasności, czy możesz określić, która składnia 2 w pytaniu jest przestarzała?
J Wynia

8
Czy możesz dostarczyć dokumentację potwierdzającą? Brzmi to źle na wielu poziomach.
NotMe

21
Jak przestajesz używać standardu SQL?
David Crawshaw

7
@david Crenshaw, niejawne połączenie nie jest już standardem i nie było go przez 18 lat.
HLGEM

11
Tak zwane „niejawne połączenia” odmiany „wewnętrznej” lub „krzyżowej” pozostają w standardzie. SQL Server przestaje stosować „starym” składnię sprzężenia zewnętrznego (tj. *=I =*), która nigdy nie była Standardowa.
poniedziałek

129

Osobiście wolę składnię łączenia, ponieważ sprawia, że ​​bardziej jasne jest, że tabele są połączone i jak są połączone. Spróbuj porównać większe zapytania SQL, w których wybierasz spośród 8 różnych tabel, a masz tam wiele filtrów. Używając składni łączyć oddzielasz części, w których tabele są połączone, od części, w której filtrujesz wiersze.


4
Całkowicie się zgadzam, ale to trochę nie na temat. OP zapytał o wydajność.
villasv

56

W MySQL 5.1.51 oba zapytania mają identyczne plany wykonania:

mysql> explain select * from table1 a inner join table2 b on a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL         |  986 |       |
|  1 | SIMPLE      | a     | ref  | pid           | pid  | 4       | schema.b.pid |   70 |       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.02 sec)

mysql> explain select * from table1 a, table2 b where a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL         |  986 |       |
|  1 | SIMPLE      | a     | ref  | pid           | pid  | 4       | schema.b.pid |   70 |       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.00 sec)

table1ma 166208 wierszy; table2ma około 1000 wierszy.

To bardzo prosty przypadek; w żaden sposób nie dowodzi, że optymalizator zapytań nie pomyliłby się i nie wygenerował różnych planów w bardziej skomplikowanym przypadku.


To powinna być zaakceptowana odpowiedź. To prawda, plan jest taki sam (lub zbliżony do większych instrukcji), ale ilość rekordów będzie drastyczna, co spowoduje różnicę w wydajności.
SovietFrontier

37

Druga składnia ma niepożądaną możliwość łączenia krzyżowego: możesz dodawać tabele do części FROM bez odpowiedniej klauzuli WHERE. Jest to uważane za szkodliwe.


Co jeśli nazwy tabel w klauzuli from są generowane z tabel używanych w klauzuli where?
Jus12

możesz również wykonać połączenie krzyżowe z jawną składnią JOIN. ( stackoverflow.com/a/44438026/929164 ) prawdopodobnie oznacza to, że jest mniej rygorystyczny, a zatem bardziej podatny na błędy użytkownika.
Daniel Dubovski

15

Pierwsza udzielona odpowiedź wykorzystuje składnię zwaną ANSI, druga jest poprawna i będzie działać w dowolnej relacyjnej bazie danych.

Zgadzam się z grom, że powinieneś używać składni ANSI join. Jak powiedzieli, głównym powodem jest jasność. Zamiast mieć klauzulę where z wieloma predykatami, z których niektóre ograniczają wiersze zwracane za pomocą składni ANSI, wyraźnie oświadczasz, które warunki są używane do łączenia tabel, a które ograniczają wyniki.


5

Pod względem wydajności są one dokładnie takie same (przynajmniej w SQL Server), ale należy pamiętać, że przestają obowiązywać tę składnię złączeń i nie jest obsługiwany przez sql server2005 po wyjęciu z pudełka.

Myślę, że myślisz o przestarzałych operatorach * = i = * vs.

Właśnie przetestowałem dwa podane formaty i działają one poprawnie w bazie danych SQL Server 2008. W moim przypadku przyniosły one identyczne plany wykonania, ale nie mogłem śmiało powiedzieć, że zawsze tak będzie.


5

@lomaxx: Aby wyjaśnić, jestem całkiem pewien, że obie powyższe składnie są obsługiwane przez SQL Serv 2005. Poniższa składnia NIE jest jednak obsługiwana

select a.*, b.*  
from table a, table b  
where a.id *= b.id;

W szczególności łączenie zewnętrzne (* =) nie jest obsługiwane.


2
Szczerze mówiąc, nie używałbym tego nawet w SQL Server 2000, składnia * = często daje złe odpowiedzi. Czasami interpretuje je jako połączenia krzyżowe.
HLGEM,

2

W niektórych bazach danych (zwłaszcza Oracle) kolejność połączeń może mieć ogromny wpływ na wydajność zapytań (jeśli są więcej niż dwie tabele). W jednej aplikacji mieliśmy dosłownie dwa rzędy różnicy wielkości w niektórych przypadkach. Używanie wewnętrznej składni łączenia daje ci kontrolę nad tym - jeśli użyjesz właściwej składni wskazówek.

Nie określiłeś, której bazy danych używasz, ale prawdopodobieństwo sugeruje, że SQL Server lub MySQL nie ma tam żadnej różnicy.


1
Leigh, możesz także użyć podpowiedzi w sprzężeniach niejawnych.
SquareCog

1
W Oracle niezwykle rzadko zdarza się, że kolejność łączenia wpływa w znaczący sposób na plan wykonania. Zobacz ten artykuł napisany przez Jonathana Lewisa o wyjaśnienia.
Jon Heller

1

Jak stwierdził Leigh Caldwell, optymalizator zapytań może tworzyć różne plany zapytań w oparciu o to, co funkcjonalnie wygląda jak ta sama instrukcja SQL. Więcej informacji na ten temat można znaleźć w następujących dwóch postach na blogu: -

Jeden post od zespołu Oracle Optimizer Team

Kolejny post z bloga „Structured Data”

Mam nadzieję, że uważasz to za interesujące.


Mike, różnica, o której mówią, polega na tym, że musisz upewnić się, że jeśli określisz jawne połączenie, określisz warunek łączenia, a nie filtr. Zauważysz, że w przypadku zapytań poprawnych semantycznie plan wykonania jest taki sam.
SquareCog

1

Pod względem wydajności nie powinno to mieć znaczenia. Wyraźna składnia łączenia wydaje mi się bardziej przejrzysta, ponieważ wyraźnie określa relacje między tabelami w klauzuli from i nie zaśmieca klauzuli where.


0

Zasadniczo różnica między nimi polega na tym, że jedno jest napisane w stary sposób, a drugie w nowoczesny sposób. Osobiście wolę nowoczesny skrypt wykorzystujący definicje wewnętrzne, lewe, zewnętrzne, prawe, ponieważ są one bardziej objaśniające i sprawiają, że kod jest bardziej czytelny.

W przypadku złączeń wewnętrznych nie ma też żadnej rzeczywistej różnicy w czytelności, jednak może się to komplikować w przypadku złączeń lewej i prawej, ponieważ w starszej metodzie można uzyskać coś takiego:

SELECT * 
FROM table a, table b
WHERE a.id = b.id (+);

Powyżej jest stary sposób, w jaki zapisywane jest lewe złączenie, w przeciwieństwie do następujących:

SELECT * 
FROM table a 
LEFT JOIN table b ON a.id = b.id;

Jak widać wizualnie, nowoczesny sposób pisania skryptu sprawia, że ​​zapytanie jest bardziej czytelne. (Nawiasem mówiąc, to samo dotyczy prawych złączeń i nieco bardziej skomplikowane w przypadku złączeń zewnętrznych).

Wracając do płyty kotła, nie ma znaczenia dla kompilatora SQL, w jaki sposób zapytanie jest pisane, ponieważ obsługuje je w ten sam sposób. Widziałem mieszankę obu w bazach danych Oracle, w których zapisywało się wiele osób, zarówno starszych, jak i młodszych. Ponownie sprowadza się to do czytelności skryptu i zespołu, z którym się rozwijasz.


-1

Z mojego doświadczenia wynika, że ​​stosowanie składni krzyżowania z klauzulą ​​„często tam, gdzie” często tworzy plan wykonania z uszkodzonym mózgiem, szczególnie jeśli używasz produktu Microsoft SQL. Na przykład sposób, w jaki SQL Server próbuje oszacować liczbę wierszy tabeli, jest strasznie okropny. Korzystanie ze składni sprzężenia wewnętrznego daje ci kontrolę nad sposobem wykonywania zapytania. Z praktycznego punktu widzenia, biorąc pod uwagę atawistyczny charakter obecnej technologii baz danych, musisz iść z wewnętrznym złączeniem.


5
Czy masz na to jakiś dowód? Ponieważ przyjęta odpowiedź mówi inaczej.
cimmanon,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.