ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПО ПРОГНОЗУ
Для простоты предположим, что все соответствующие поля NOT NULL,
Ты можешь сделать:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
AND (some other conditions)
Или еще:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1 INNER JOIN table2
ON table1.foreignkey = table2.primarykey
WHERE
(some other conditions)
Эти две работы работают одинаково в MySQL?
10 ответов
INNER JOIN это синтаксис ANSI, который вы должны использовать.
Как правило, он считается более читабельным, особенно когда вы объединяете множество таблиц.
Он также может быть легко заменен OUTER JOIN всякий раз, когда возникает необходимость.
WHERE синтаксис более ориентирован на реляционную модель.
Результат двух таблиц JOINed - это декартово произведение таблиц, к которым применяется фильтр, который выбирает только те строки с сопоставлением соединяющих столбцов.
Это легче увидеть с WHERE синтаксис.
Что касается вашего примера, в MySQL (и в SQL в целом) эти два запроса являются синонимами.
Также обратите внимание, что MySQL также имеет STRAIGHT_JOIN пункт.
Используя этот пункт, вы можете контролировать JOIN порядок: какая таблица сканируется во внешнем цикле, а какая во внутреннем цикле.
Вы не можете контролировать это в MySQL, используя WHERE синтаксис.
Другие отмечают, что INNER JOIN помогает читабельности человека, и это главный приоритет; Согласен. Позвольте мне объяснить, почему синтаксис объединения более читабелен.
Основной запрос SELECT:
SELECT stuff
FROM tables
WHERE conditions
Предложение SELECT сообщает нам, что мы получаем обратно; Предложение FROM сообщает нам, откуда мы его получаем, а предложение WHERE сообщает нам, какие мы получаем.
JOIN - это утверждение о таблицах, о том, как они связаны друг с другом (концептуально, фактически, в единую таблицу). Любые элементы запроса, которые управляют таблицами - откуда мы получаем материал - семантически принадлежат предложению FROM (и, конечно, именно туда идут элементы JOIN). Помещение соединяющих элементов в предложение WHERE объединяет " где" и " откуда"; Вот почему синтаксис JOIN является предпочтительным.
Применение условных операторов в ON / WHERE
Здесь я объяснил о шагах обработки логического запроса.
Ссылка: внутри Microsoft® SQL Server™ 2005 T-SQL-запросов
Издатель: Microsoft Press
Дата публикации: 7 марта 2006 г.
Печать ISBN-10: 0-7356-2313-9
Печать ISBN-13: 978-0-7356-2313-2
Страницы: 640
Внутри Microsoft® SQL Server™ 2005 T-SQL-запросов
(8) SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
Первый заметный аспект SQL, который отличается от других языков программирования, - это порядок обработки кода. В большинстве языков программирования код обрабатывается в том порядке, в котором он написан. В SQL первое предложение, которое обрабатывается, это предложение FROM, в то время как предложение SELECT, которое появляется первым, обрабатывается почти последним.
Каждый шаг генерирует виртуальную таблицу, которая используется в качестве входных данных для следующего шага. Эти виртуальные таблицы недоступны для вызывающей стороны (клиентское приложение или внешний запрос). Только таблица, сгенерированная на последнем шаге, возвращается вызывающей стороне. Если определенное предложение не указано в запросе, соответствующий шаг просто пропускается.
Краткое описание этапов обработки логических запросов
Не беспокойтесь слишком сильно, если описание шагов пока не имеет особого смысла. Они предоставляются в качестве ссылки. Разделы, которые следуют после примера сценария, будут охватывать шаги более подробно.
ОТ: Декартово произведение (перекрестное соединение) выполняется между первыми двумя таблицами в предложении FROM, и в результате создается виртуальная таблица VT1.
ON: фильтр ON применяется к VT1. Только строки, для которых
<join_condition>ИСТИНА вставлены в VT2.OUTER (соединение): если указано OUTER JOIN (в отличие от CROSS JOIN или INNER JOIN), строки из сохраненной таблицы или таблиц, для которых не найдено совпадение, добавляются в строки из VT2 как внешние строки, генерируя VT3. Если в предложении FROM появляется более двух таблиц, шаги с 1 по 3 применяются повторно между результатом последнего соединения и следующей таблицей в предложении FROM, пока не будут обработаны все таблицы.
ГДЕ: ГДЕ фильтр применяется к VT3. Только строки, для которых
<where_condition>ИСТИНА вставлены в VT4.GROUP BY: строки из VT4 упорядочены в группы на основе списка столбцов, указанного в предложении GROUP BY. VT5 генерируется.
КУБ | ROLLUP: супергруппы (группы групп) добавляются в строки из VT5, генерируя VT6.
HAVING: фильтр HAVING применяется к VT6. Только группы, для которых
<having_condition>ИСТИНА вставлены в VT7.SELECT: список SELECT обрабатывается, генерируя VT8.
DISTINCT: повторяющиеся строки удаляются из VT8. VT9 генерируется.
ORDER BY: строки из VT9 сортируются в соответствии со списком столбцов, указанным в предложении ORDER BY. Курсор генерируется (VC10).
TOP: указанное количество или процент строк выбирается с начала VC10. Таблица VT11 генерируется и возвращается вызывающей стороне.
Следовательно, (INNER JOIN) ON будет фильтровать данные (количество данных VT будет само здесь уменьшено) перед применением предложения WHERE. Последующие условия соединения будут выполняться с отфильтрованными данными, что повышает производительность. После этого только условие WHERE будет применять условия фильтра.
(Применение условных операторов в ON / WHERE не будет иметь большого значения в нескольких случаях. Это зависит от количества таблиц, к которым вы присоединились, и количества строк, доступных в каждой из таблиц объединения)
Синтаксис неявного соединения ANSI более старый, менее очевидный и не рекомендуется.
Кроме того, реляционная алгебра обеспечивает взаимозаменяемость предикатов в WHERE пункт и INNER JOINда еще INNER JOIN запросы с WHERE предложения могут иметь предикаты, переупорядоченные оптимизатором.
Я рекомендую вам писать запросы наиболее читабельным способом.
Иногда это включает в себя создание INNER JOIN относительно "неполным" и положить некоторые из критериев в WHERE просто сделать списки критериев фильтрации более легкими в обслуживании.
Например, вместо:
SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
ON ca.CustomerID = c.CustomerID
AND c.State = 'NY'
INNER JOIN Accounts a
ON ca.AccountID = a.AccountID
AND a.Status = 1
Написать:
SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
AND a.Status = 1
Но это зависит, конечно.
Неявные объединения (это то, что называется вашим первым запросом) становятся намного более запутанными, трудными для чтения и сложными для обслуживания, когда вам нужно начать добавлять больше таблиц в ваш запрос. Представьте, что вы выполняете один и тот же запрос и тип соединения для четырех или пяти разных таблиц... это кошмар.
Использование явного соединения (ваш второй пример) гораздо удобнее для чтения и обслуживания.
Я также укажу, что использование старого синтаксиса более подвержено ошибкам. Если вы используете внутренние объединения без предложения ON, вы получите синтаксическую ошибку. Если вы используете более старый синтаксис и забудете одно из условий соединения в предложении where, вы получите перекрестное соединение. Разработчики часто исправляют это, добавляя отдельное ключевое слово (вместо того, чтобы исправлять соединение, потому что они все еще не понимают, что само соединение нарушено), которое может решить проблему, но значительно замедлит запрос.
Кроме того, для техобслуживания, если у вас есть перекрестное соединение в старом синтаксисе, как специалист по сопровождению узнает, если вы хотели иметь такой (есть ситуации, когда необходимо перекрестное объединение), или если это был несчастный случай, который следует исправить?
Позвольте мне указать вам на этот вопрос, чтобы понять, почему неявный синтаксис плох, если вы используете левые соединения. Sybase * = к стандарту Ansi с 2 разными внешними таблицами для одной внутренней таблицы
Плюс (личная напыщенная речь здесь), стандарту, использующему явные объединения, более 20 лет, что означает, что неявный синтаксис объединения был устаревшим в течение этих 20 лет. Вы бы написали код приложения с использованием синтаксиса, который устарел в течение 20 лет? Почему вы хотите написать код базы данных?
Стандарт SQL:2003 изменил некоторые правила приоритета, поэтому оператор JOIN имеет приоритет над соединением "запятая". Это может фактически изменить результаты вашего запроса в зависимости от того, как он настроен. Это вызывает некоторые проблемы у некоторых людей, когда MySQL 5.0.12 перешел на соблюдение стандарта.
Так что в вашем примере ваши запросы будут работать одинаково. Но если вы добавили третью таблицу: SELECT ... FROM table1, table2 ПРИСОЕДИНЯЙТЕСЬ к table3 ON ... WHERE ...
До MySQL 5.0.12 сначала объединялись table1 и table2, а затем table3. Теперь (5.0.12 и далее) сначала объединяются table2 и table3, а затем table1. Это не всегда меняет результаты, но может и вы можете даже не осознавать этого.
Я никогда больше не использую синтаксис "запятая", выбирая второй пример. В любом случае, это намного более читабельно, условия JOIN связаны с JOIN, а не разделены на отдельный раздел запроса.
Они имеют различный человекочитаемый смысл.
Однако, в зависимости от оптимизатора запросов, они могут иметь одинаковое значение для машины.
Вы должны всегда кодировать, чтобы быть читаемым.
То есть, если это встроенные отношения, используйте явное соединение. если вы сопоставляете слабо связанные данные, используйте предложение where.
Я знаю, что вы говорите о MySQL, но в любом случае: в Oracle 9 явные объединения и неявные объединения будут генерировать разные планы выполнения. AFAIK, который был решен в Oracle 10+: такой разницы больше нет.
Синтаксис соединения ANSI определенно более переносим.
Я собираюсь обновить Microsoft SQL Server, и я бы также упомянул, что синтаксис =* и *= для внешних объединений в SQL Server не поддерживается (без режима совместимости) для SQL Server 2005 и более поздних версий.
Если вы часто программируете динамические хранимые процедуры, вам понравится второй пример (с использованием where). Если у вас есть различные входные параметры и много морфинга, то это единственный выход. В противном случае они оба будут выполнять один и тот же план запроса, поэтому очевидной разницы в классических запросах нет.
У меня есть два балла за неявное соединение (второй пример):
- Скажите базе данных, что вы хотите, а не то, что она должна делать.
- Вы можете записать все таблицы в четкий список, не загроможденный условиями соединения. Тогда вам будет намного проще прочитать, какие таблицы все упомянуты. Все условия входят в часть WHERE, где они также выстраиваются одно под другим. Использование ключевого слова JOIN смешивает таблицы и условия.