SQL left join vs multiple tables on FROM line?

Question

SQL left join vs multiple tables on FROM line?

большинство диалектов SQL принимают оба следующих запроса:

SELECT a.foo, b.foo

FROM a, b

WHERE a.x = b.x



SELECT a.foo, b.foo

FROM a

LEFT JOIN b ON a.x = b.x

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

582 11

syntax sql join

11 ответов:

Comments

Ничего не найдено.

Lasse Vågsæther Karlsen · Accepted Answer · 2015-08-15 13:24:15

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

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

позвольте мне привести вам пример.

предположим, у вас есть 3 таблицы в вашей системе:
Company
Department
Employee
каждая таблица содержит многочисленные строки, связанные вместе. У вас есть несколько компаний, и каждая компания может иметь несколько отделов, и каждый отдел может иметь несколько сотрудников.

Итак, теперь вы хотите сделать следующее:

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

Итак, вы делаете это:
SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID
обратите внимание, что последний есть внутреннее соединение, чтобы выполнить критерии, которые вы хотите только отделы с людьми.

хорошо, так что же происходит сейчас. Ну, проблема в том, что это зависит от ядра СУБД, оптимизатора запросов, индексов и статистики таблиц. Позвольте мне объяснить.

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

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

и в этом случае, из-за левого соединения, Department.ID столбец будет NULL, и поэтому, когда дело доходит до внутреннего соединения с сотрудником, нет никакого способа выполнить это ограничение для сотрудника подряд, и поэтому он не появится.

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

так что старый синтаксис неоднозначен. Там нет никакого способа, чтобы указать, что вы хотите, не имея дело с подсказками запроса, и некоторые базы данных не имеют никакого способа вообще.

введите новый синтаксис, с этим вы можете выбрать.

например, если вы хотите, чтобы все компании, как описание проблемы заявлено, это то, что вы бы написали:
SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID
здесь вы указываете, что вы хотите, чтобы соединение отдела-сотрудника было сделано как одно соединение, а затем осталось присоединиться к результатам этого с компаниями.

кроме того, предположим, что вам нужны только отделы, которые содержат букву X в своем имени. Опять же, при соединении старого стиля вы также рискуете потерять компанию, если у нее нет отделов с X в ее имени, но с новым синтаксисом вы можете сделать это:
SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'
это дополнительное предложение используется для объединения, но не является фильтром для всей строки. Таким образом, строка может отображаться с информацией о компании, но может иметь нули во всех столбцах отдела и сотрудников для этой строки, потому что нет отдела с X в его имени для этой компании. Это трудно со старым синтаксисом.

вот почему, среди других поставщиков, Microsoft устарела старый внешний синтаксис соединения, но не старый внутренний синтаксис соединения, так как SQL Server 2005 и выше. Единственный способ поговорить с базой данных, работающей на Microsoft SQL Server 2005 или 2008, используя синтаксис внешнего соединения старого стиля, - это установить эту базу данных в режиме совместимости 8.0 (он же SQL Server 2000).

кроме того, старый способ, бросая кучу таблиц в оптимизатор запросов, с кучей предложений WHERE, был сродни тому, чтобы сказать: "Вот вы, делайте все возможное". С новым синтаксисом оптимизатор запросов имеет меньше работы, чтобы выяснить, какие части пойти вместе.

так что у вас есть.

левое и внутреннее соединение-это волна будущего.

Andomar · Accepted Answer · 2009-05-21 21:56:38

синтаксис соединения сохраняет условия рядом с таблицей, к которой они применяются. Это особенно полезно при объединении большого количества таблиц.

кстати, вы можете сделать внешнее соединение с первым тоже синтаксис:
WHERE a.x = b.x(+)
или
WHERE a.x *= b.x
или
WHERE a.x = b.x or a.x not in (select x from b)

Dwight T · Accepted Answer · 2009-05-21 23:06:19

первый способ более старый стандарт. Второй метод был введен в SQL-92,http://en.wikipedia.org/wiki/SQL. полный стандарт можно посмотреть на http://www.contrib.andrew.cmu.edu / ~shadow/sql/sql1992.txt .

прошло много лет, прежде чем компании баз данных приняли стандарт SQL-92.

таким образом, причина, по которой второй метод является предпочтительным, это стандарт SQL в соответствии с ANSI и ISO стандартов комитета.

HLGEM · Accepted Answer · 2009-05-21 22:02:38

второй предпочтительнее, потому что это гораздо менее вероятно, чтобы привести к случайному перекрестному соединению, забыв поставить в предложении where. Соединение без предложения on не выполнит проверку синтаксиса, соединение старого стиля без предложения where не будет выполнено, оно будет выполнять перекрестное соединение.

кроме того, когда вы позже должны слева присоединиться, это полезно для обслуживания, что все они находятся в той же структуре. И старый синтаксис устарел с 1992 года, давно пора перестать использовать оно.

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

Peter Perháč · Accepted Answer · 2017-04-13 13:40:05

в основном, когда ваше предложение FROM перечисляет таблицы следующим образом:
SELECT * FROM
  tableA, tableB, tableC
результат является перекрестным произведением всех строк в таблицах A, B, C. Затем вы применяете ограничение WHERE tableA.id = tableB.a_id, который будет выбрасывать огромное количество строк, затем дальше ... AND tableB.id = tableC.b_id и вы должны тогда получить только те строки, которые вас действительно интересуют.

СУБД знают, как оптимизировать этот SQL, чтобы разница в производительности для записи этого с помощью соединений была незначительной (если таковая имеется). Использование соединения нотация делает оператор SQL больше читаемый (IMHO, не используя соединения превращает заявление в беспорядок). Используя перекрестный продукт, вам нужно предоставить критерии соединения в предложении WHERE, и это проблема с обозначением. Вы переполняете свой пункт WHERE такими вещами, как
    tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id 
, который используется только для ограничения продукта кросс. Где предложение должно содержать только ограничения на результирующий набор. Если вы смешиваете критерии объединения таблиц с ограничениями набора результатов, вы (и другие) найдут ваш запрос труднее читать. Вы должны определенно использовать соединения и сохранить от пункта а от пункта, и предложение where предложение where.

Alan G · Accepted Answer · 2012-06-25 13:17:55

Я думаю, что есть некоторые веские причины на этой странице, чтобы принять второй метод-с помощью явных соединений. Однако решающим моментом является то, что когда критерии соединения удаляются из предложения WHERE, становится намного легче увидеть остальные критерии выбора в предложении WHERE.

в действительно сложных операторах SELECT читателю становится намного легче понять, что происходит.

Euro Micelli · Accepted Answer · 2009-05-21 22:15:53

на SELECT * FROM table1, table2, ... синтаксис в порядке для нескольких таблиц, но он становится экспоненциально (не обязательно математически точное утверждение) все труднее и труднее читать по мере увеличения количества таблиц.

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

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

Pablo Santa Cruz · Accepted Answer · 2009-05-21 22:33:43

когда вам нужно внешнее соединение второй синтаксис не всегда требуется:

Oracle:
SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x = b.x(+)
MSSQLServer (хотя это было не рекомендуется в версии 2000) / Sybase:
SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x *= b.x
Но вернемся к вашему вопросу. Я не знаю ответа, но это, вероятно, связано с тем, что вступить более естественно (по крайней мере, синтаксически), чем добавление выражения к здесь предложение, когда вы находитесь делая именно это:вступление.

kemiller2002 · Accepted Answer · 2009-05-21 21:55:50

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

Jeff Ferland · Accepted Answer · 2009-05-21 21:56:19

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

Gavin H · Accepted Answer · 2009-05-21 21:56:50

Ну первый и второй запросы могут давать разные результаты, потому что левое соединение включает все записи из первой таблицы, даже если в правой таблице нет соответствующих записей.