ВНУТРЕННЕЕ ПРИСОЕДИНЯЙТЕСЬ К МЕСТУ

813

Для простоты предположим, что все соответствующие поля 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?

  • 0
    @ Марко: вот оно
  • 0
    возможный дубликат SQL левого соединения против нескольких таблиц в строке FROM?
Показать ещё 3 комментария
Теги:
join
inner-join

10 ответов

589
Лучший ответ

INNER JOIN - это синтаксис ANSI, который вы должны использовать.

Обычно считается более читаемым, особенно когда вы присоединяетесь к множеству таблиц.

Его также можно легко заменить на OUTER JOIN всякий раз, когда возникает необходимость.

Синтаксис WHERE более ориентирован на реляционную модель.

Результат двух таблиц JOIN ed - это декартово произведение таблиц, к которым применяется фильтр, который выбирает только те строки, в которых сопоставляются столбцы.

Проще увидеть это с помощью синтаксиса WHERE.

Что касается вашего примера, то в MySQL (и в SQL вообще) эти два запроса являются синонимами.

Также обратите внимание, что MySQL также имеет предложение STRAIGHT_JOIN.

Используя это предложение, вы можете управлять порядком JOIN: какая таблица сканируется во внешнем цикле и какая из них находится во внутреннем цикле.

Вы не можете управлять этим в MySQL с помощью синтаксиса WHERE.

  • 7
    Спасибо, Кассной. У вас есть много деталей в ваших ответах; Справедливо ли говорить, что «да, эти запросы эквивалентны, но вы должны использовать внутреннее соединение, потому что оно более читабельно и его легче модифицировать»?
  • 7
    @allyourcode: для Oracle , SQL Server , MySQL и PostgreSQL - да. Для других систем, наверное, тоже, но вам лучше проверить.
Показать ещё 12 комментариев
149

Другие указали, что INNER JOIN помогает человеку читать и что главный приоритет; Согласен. Позвольте мне объяснить, почему синтаксис соединения более читабельен.

Основной запрос SELECT:

SELECT stuff
FROM tables
WHERE conditions

Предложение SELECT сообщает нам , что мы возвращаемся; предложение FROM сообщает нам , где мы получаем его, а предложение WHERE сообщает нам , которые мы получаем.

JOIN - это утверждение о таблицах, как они связаны друг с другом (концептуально, фактически, в одну таблицу). Любые элементы запроса, которые управляют таблицами - откуда мы получаем материал - семантически, относятся к предложению FROM (и, конечно же, в том случае, когда идут элементы JOIN). Ввод элементов соединения в предложение WHERE объединяет , который и где-из; что предпочтительнее использовать синтаксис JOIN.

  • 6
    Спасибо за разъяснение, почему внутреннее соединение предпочтительнее Карла. Я думаю, что ваши ответы были неявными в других, но явные обычно лучше (да, я фанат Python).
  • 2
    Семантика ON и WHERE означает, что для JOINs после последнего OUTER JOIN не имеет значения, какой вы используете. Хотя вы характеризуете ON как часть JOIN, это также фильтрация после декартового произведения. И ON, и WHERE фильтруют декартово произведение. Но перед последним ВНЕШНИМ СОЕДИНЕНИЕМ необходимо использовать либо ВКЛ, либо подвыбор с ГДЕ. (СОЕДИНЕНИЯ не являются парами столбцов «on». Любые две таблицы могут быть СОЕДИНЕНЫ при любом условии. Это просто способ интерпретировать СОЕДИНЕНИЯ О равенстве столбцов конкретно.)
113

Применение условных операторов в ON/WHERE

Здесь я объяснил о шагах обработки логических запросов.


Справка: внутри SQL-запросов SQL Server 2005 T-SQL

Издатель: Microsoft Press
Паб Дата: 07 марта 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: Декартово произведение (кросс-соединение) выполняется между двумя первыми двумя таблицами в предложении FROM, и в результате создается виртуальная таблица VT1.

  • ВКЛ: фильтр ВКЛ применяется к VT1. Только строки, для которых значение <join_condition> TRUE, вставляются в VT2.

  • OUTER (join): Если указан OUTER JOIN (в отличие от CROSS JOIN или INNER JOIN), строки из сохраненной таблицы или таблиц, для которых совпадение не было найдено, добавляются к строкам из VT2 как внешние строки, генерирующие VT3. Если в предложении FROM появляется более двух таблиц, шаги 1 - 3 применяются повторно между результатом последнего соединения и следующей таблицей в предложении FROM до тех пор, пока не будут обработаны все таблицы.

  • ГДЕ: Фильтр WHERE применяется к VT3. Только строки, для которых <where_condition> TRUE, вставляются в VT4.

  • GROUP BY: Строки из VT4 расположены в группах на основе списка столбцов, указанного в предложении GROUP BY. VT5 генерируется.

  • CUBE | ROLLUP: Супергруппы (группы групп) добавляются к строкам из VT5, генерируя VT6.

  • HAVING: Фильтр HAVING применяется к VT6. В VT7 вставляются только те группы, для которых значение <having_condition> TRUE.

  • SELECT: обрабатывается список SELECT, генерирующий VT8.

  • DISTINCT: Дублирующие строки удаляются из VT8. VT9 генерируется.

  • ORDER BY: строки из VT9 сортируются в соответствии с списком столбцов, указанным в предложении ORDER BY. Создается курсор (VC10).

  • TOP: указанное число или процент строк выбирается с начала VC10. Таблица VT11 генерируется и возвращается вызывающему абоненту.



   Таким образом, (INNER JOIN) ON будет фильтровать данные (счетчик данных VT будет уменьшен здесь сам) перед применением предложения WHERE. Последующие условия соединения будут выполняться с отфильтрованными данными, что улучшает производительность. После этого условие WHERE будет применяться только к условиям фильтра.

(Применение условных операторов в ON/WHERE не будет иметь большого значения в нескольких случаях. Это зависит от того, сколько таблиц вы присоединились и количество строк доступно в каждой таблице соединений)

  • 10
    «Следовательно, (INNER JOIN) ON будет фильтровать данные (количество данных VT будет уменьшено здесь) до применения предложения WHERE». Не обязательно. Статья о логическом порядке обработки. Когда вы говорите, что конкретная реализация будет делать одну вещь перед другой, вы говорите о реализованном порядке обработки. Реализациям разрешено выполнять любые оптимизации, которые им нравятся, при условии, что результат такой же, как если бы реализация следовала логическому порядку. Джо Селко много писал об этом в Usenet.
  • 0
    @rafidheen "(INNER JOIN) ON будет фильтровать данные ... перед применением предложения WHERE ... что повышает производительность." Хорошая точка зрения. «После этого только условие WHERE будет применять условия фильтрации». Как насчет предложения HAVING?
Показать ещё 1 комментарий
53

Синтаксис неявного соединения ANSI является более старым, менее очевидным и не рекомендуется.

Кроме того, реляционная алгебра допускает взаимозаменяемость предикатов в предложении WHERE и INNER JOIN, поэтому даже запросы INNER JOIN с предложениями WHERE могут иметь предикаты, переупорядоченные оптимизатором.

Я рекомендую вам писать запросы наиболее читаемым способом.

Иногда это включает в себя сделать "t21" относительно "неполным" и поставить некоторые из критериев в 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

Но это зависит, конечно.

  • 15
    Ваш первый фрагмент определенно ранит мой мозг больше. Кто-нибудь на самом деле это делает? Если я встречаю кого-то, кто это делает, можно ли мне бить его по голове?
  • 3
    Я нахожу критерии там, где это имеет смысл. Если я присоединяюсь к согласованной во времени поисковой таблице моментальных снимков (и у меня нет представления или UDF, обеспечивающего выбор допустимой даты), я включу дату вступления в силу в соединение, а не в WHERE, потому что оно меньше может быть случайно удален.
Показать ещё 2 комментария
25

Неявные объединения (это то, что называется вашим первым запросом) становятся намного более запутанными, трудными для чтения и трудно поддерживать, как только вам нужно начать добавлять в свой запрос больше таблиц. Представьте, что вы делаете тот же запрос и тип соединения на четырех или пяти разных таблицах... это кошмар.

Использование явного соединения (ваш второй пример) гораздо читабельнее и легко поддерживается.

  • 43
    Я не мог не согласиться больше. Синтаксис JOIN чрезвычайно многословен и сложен в организации. У меня есть множество запросов, соединяющих 5, 10, даже 15 таблиц, использующих объединения WHERE, и они отлично читаются. Переписывание такого запроса с использованием синтаксиса JOIN приводит к искаженному беспорядку. Это говорит о том, что на этот вопрос нет правильного ответа, и что это зависит больше от того, с чем вам удобно.
  • 31
    Ноа, я думаю, ты здесь в меньшинстве.
Показать ещё 6 комментариев
23

Я также укажу, что использование более старого синтаксиса более подвержено ошибкам. Если вы используете внутренние соединения без предложения ON, вы получите синтаксическую ошибку. Если вы используете старый синтаксис и забыли одно из условий соединения в предложении where, вы получите кросс-соединение. Разработчики часто исправляют это, добавляя отдельное ключевое слово (вместо того, чтобы фиксировать соединение, потому что они все еще не понимают, что само соединение не работает), что может показаться, что оно устраняет проблему, но значительно замедлит запрос.

Кроме того, для обслуживания, если у вас есть крест-соединение в старом синтаксисе, как разработчик узнает, если вы хотите его иметь (есть ситуации, когда необходимы перекрестные соединения) или если это была ошибка, которая должна быть исправлена?

Позвольте мне указать на этот вопрос, чтобы понять, почему неявный синтаксис является плохим, если вы используете левые соединения. Sybase * = для Ansi Standard с 2 различными внешними таблицами для одной и той же внутренней таблицы

Плюс (личный rant здесь), стандарт с использованием явных объединений старше 20 лет, что означает, что синтаксис неявного соединения устарел для этих 20 лет. Вы могли бы написать код приложения, используя синтаксис, устаревший в течение 20 лет? Почему вы хотите написать код базы данных?

  • 3
    @HLGEM: Хотя я полностью согласен с тем, что явные соединения лучше, в некоторых случаях вам просто нужно использовать старый синтаксис. Пример из реальной жизни: ANSI JOIN вошел в Oracle только в версии 9i, которая была выпущена в 2001 году, и только год назад (16 лет с момента публикации стандарта) мне приходилось поддерживать несколько установок 8i, для которых мы имели выпускать критические обновления. Я не хотел поддерживать два набора обновлений, поэтому мы разработали и протестировали обновления для всех баз данных, включая 8i, что означало, что мы не могли использовать ANSI JOIN.
  • 0
    +1 интересный момент, когда вы указываете, что синтаксис без INNER JOIN более подвержен ошибкам. Я запутался в вашем последнем предложении, когда вы говорите: «... стандарт, использующий явные объединения, 17 лет». поэтому вы предлагаете использовать ключевое слово INNER JOIN или нет?
Показать ещё 8 комментариев
12

У них есть другое понятное для человека значение.

Однако, в зависимости от оптимизатора запросов, они могут иметь то же значение для машины.

Вы всегда должны кодироваться для чтения.

То есть, если это встроенное отношение, используйте явное соединение. если вы сопоставляете слабосвязанные данные, используйте предложение where.

10

Стандарт SQL: 2003 изменил некоторые правила приоритета, поэтому оператор JOIN имеет преимущество перед соединением "запятая". Это может фактически изменить результаты вашего запроса в зависимости от того, как он настроен. Это вызывает некоторые проблемы для некоторых людей, когда MySQL 5.0.12 переключился на соблюдение стандарта.

Итак, в вашем примере ваши запросы будут работать одинаково. Но если вы добавили третью таблицу: SELECT... FROM table1, table2 JOIN table3 ON... WHERE...

До MySQL 5.0.12 сначала будут объединены table1 и table2, а затем table3. Теперь (5.0.12 и далее), table2 и table3 соединяются сначала, а затем table1. Он не всегда меняет результаты, но может, и вы даже не можете этого понять.

Я больше не использую синтаксис "запятая", предпочитая второй пример. В любом случае, это намного более читаемо, условия JOIN заключаются в JOIN, а не в отдельный раздел запроса.

  • 0
    Стандартный SQL не изменился. MySQL был просто неправ, а теперь прав. Смотрите руководство по MySQL.
4

Я знаю, что вы говорите о MySQL, но так или иначе: В Oracle 9 явные объединения и неявные объединения будут генерировать различные планы выполнения. AFAIK, который был решен в Oracle 10+: больше нет такой разницы.

1

Синтаксис ANSI join определенно более портативный.

Я собираюсь обновить Microsoft SQL Server, и я бы также упомянул, что синтаксис = * и * = для внешних соединений в SQL Server не поддерживается (без режима совместимости) для SQL Server 2005 года и позже.

  • 2
    Даже в SQL Server 2000 = и = могут давать неправильные результаты и никогда не должны использоваться.
  • 2
    *= и =* никогда не были ANSI и никогда не были хорошим обозначением. Вот почему ON был необходим - для ВНЕШНИХ СОЕДИНЕНИЙ в отсутствие подвыборов (которые были добавлены в то же время, поэтому они на самом деле не нужны в CROSS & INNER JOIN.)

Ещё вопросы

Сообщество Overcoder
Наверх
Меню