Явные против неявных объединений SQL
Есть ли разница в эффективности в явном и неявном внутреннем соединении? Например:
SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;
против
SELECT a.*, b.*
FROM table a, table b
WHERE a.id = b.id;
11 ответов
По производительности они одинаковы (по крайней мере, в SQL Server).
PS: знать, что IMPLICIT OUTER JOIN синтаксис устарел начиная с SQL Server 2005. (IMPLICIT INNER JOIN синтаксис, используемый в вопросе, все еще поддерживается)
Устаревание синтаксиса JOIN "старого стиля": только частичная вещь
Лично я предпочитаю синтаксис объединения, так как он проясняет, что таблицы объединяются и как они объединяются. Попробуйте сравнить большие запросы SQL, где вы выбираете из 8 разных таблиц, и у вас есть много фильтрации в где. Используя синтаксис объединения, вы разделяете части, в которых объединяются таблицы, на части, где вы фильтруете строки.
На MySQL 5.1.51 оба запроса имеют идентичные планы выполнения:
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)
table1 имеет 166208 рядов; table2 имеет около 1000 строк.
Это очень простой случай; это никоим образом не доказывает, что оптимизатор запросов не запутается и не создаст другие планы в более сложном случае.
Второй синтаксис имеет нежелательную возможность перекрестного соединения: вы можете добавлять таблицы в часть FROM без соответствующего предложения WHERE. Это считается вредным.
В первом ответе, который вы дали, используется так называемый синтаксис объединения ANSI, а другой действителен и будет работать в любой реляционной базе данных.
Я согласен с Grom, что вы должны использовать синтаксис соединения ANSI. По их словам, главная причина в ясности. Вместо того, чтобы иметь предложение where с большим количеством предикатов, некоторые из которых объединяют таблицы, а другие ограничивают строки, возвращаемые с помощью синтаксиса соединения ANSI, вы четко и ясно даете понять, какие условия используются для объединения ваших таблиц, а какие - для ограничения Результаты.
@lomaxx: Просто чтобы уточнить, я почти уверен, что оба вышеуказанных синтаксиса поддерживаются SQL Serv 2005. Однако нижеприведенный синтаксис НЕ поддерживается
select a.*, b.*
from table a, table b
where a.id *= b.id;
В частности, внешнее соединение (*=) не поддерживается.
С точки зрения производительности, они точно такие же (по крайней мере, в SQL Server), но имейте в виду, что они не поддерживают этот синтаксис объединения, и он не поддерживается sql server2005 "из коробки".
Я думаю, что вы думаете об устаревших операторах *= и =* против "внешнего соединения".
Я только что проверил два приведенных формата, и они правильно работают в базе данных SQL Server 2008. В моем случае они дали одинаковые планы выполнения, но я не мог с уверенностью сказать, что это всегда будет правдой.
В некоторых базах данных (особенно в Oracle) порядок объединений может существенно повлиять на производительность запросов (если имеется более двух таблиц). В одном приложении мы имели буквально разницу в два порядка в некоторых случаях. Использование синтаксиса внутреннего соединения дает вам контроль над этим - если вы используете правильный синтаксис подсказок.
Вы не указали, какую базу данных вы используете, но вероятность подсказывает SQL Server или MySQL там, где это не имеет никакого значения.
По сути, разница между ними заключается в том, что один написан по-старому, а другой написан по-современному. Лично я предпочитаю современный скрипт, использующий внутреннее, левое, внешнее и правое определения, потому что они более понятны и делают код более читабельным.
При работе с внутренними объединениями нет никакой реальной разницы в удобочитаемости, однако это может усложниться при работе с левым и правым объединениями, поскольку в более старом методе вы получите что-то вроде этого:
SELECT *
FROM table a, table b
WHERE a.id = b.id (+);
Выше приведен старый способ написания левого соединения, в отличие от следующего:
SELECT *
FROM table a
LEFT JOIN table b ON a.id = b.id;
Как вы можете видеть визуально, современный способ написания скрипта делает запрос более читабельным. (Кстати, то же самое касается правильных объединений и немного более сложного для внешних объединений).
Возвращаясь к основной теме, для компилятора SQL не имеет значения, как написан запрос, поскольку он обрабатывает их одинаково. Я видел смесь обоих в базах данных Oracle, в которые записывалось много людей, как старших, так и младших. Опять же, все сводится к тому, насколько читаем сценарий и команда, с которой вы работаете.
Как заявил Ли Колдуэлл, оптимизатор запросов может создавать различные планы запросов на основе того, что функционально выглядит как один и тот же оператор SQL. Для дальнейшего прочтения, посмотрите на следующие два сообщения в блоге:-
Одна публикация от команды оптимизатора Oracle
Еще одна публикация из блога "Структурированные данные"
Я надеюсь, что вы найдете это интересным.
Производительность мудрая, это не должно иметь никакого значения. Явный синтаксис объединения кажется мне чище, поскольку он четко определяет отношения между таблицами в предложении from и не загромождает предложение where.
По моему опыту, использование синтаксиса кросс-объединения с условием где-то часто приводит к поврежденному мозгу плану выполнения, особенно если вы используете продукт Microsoft SQL. Например, способ, которым SQL Server пытается оценить количество строк в таблице, дико ужасен. Использование синтаксиса внутреннего соединения дает вам некоторый контроль над тем, как выполняется запрос. Таким образом, с практической точки зрения, учитывая атавистическую природу современных технологий баз данных, вы должны идти с внутренним объединением.