Jawne i niejawne sprzężenia SQL

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;

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

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.

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.

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.

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.

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.

@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.

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.

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.

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.

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.

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.