Левое соединение SQL с несколькими таблицами в строке FROM?

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

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

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

Предположим, в вашей системе есть 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, и поэтому, когда дело доходит до INNER JOIN для Employee, нет способа выполнить это ограничение для строки Employee, и поэтому оно не будет появляться.

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

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

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

Например, если вам нужны все компании, как указано в описании проблемы, вы должны написать следующее:

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%'

Это дополнительное предложение используется для объединения, но не является фильтром для всей строки. Таким образом, строка может отображаться с информацией о компании, но может содержать NULL во всех столбцах отделов и сотрудников для этой строки, потому что для этой компании нет отдела с X в названии. Со старым синтаксисом это сложно.

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

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

Вот и все.

LEFT и INNER JOIN - это волна будущего.

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

Кстати, вы также можете выполнить внешнее соединение с первым синтаксисом:

WHERE a.x = b.x(+)

Or

WHERE a.x *= b.x

Or

WHERE a.x = b.x or a.x not in (select x from b)

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

Потребовалось много лет, чтобы компании, работающие с базами данных, приняли стандарт SQL-92.

Итак, причина, по которой предпочтение отдается второму методу, - это стандарт SQL согласно комитету стандартов ANSI и ISO.

В основном, когда ваше предложение FROM перечисляет такие таблицы:

SELECT * FROM
  tableA, tableB, tableC

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

СУБД знают, как оптимизировать этот SQL, так что разница в производительности при написании этого с использованием JOINs незначительна (если таковая имеется). Использование нотации JOIN делает инструкцию SQL более читаемой (IMHO, отсутствие соединений превращает инструкцию в беспорядок). Используя перекрестное произведение, вам необходимо указать критерии соединения в предложении WHERE, и в этом проблема с нотацией. Вы переполняете предложение WHERE такими вещами, как

    tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id 

который используется только для ограничения перекрестного произведения. Предложение WHERE должно содержать только ОГРАНИЧЕНИЯ для набора результатов. Если вы смешиваете критерии объединения таблиц с ограничениями набора результатов, вам (и другим) будет труднее читать ваш запрос. Вы обязательно должны использовать JOIN и оставить предложение FROM предложением FROM, а предложение WHERE - предложением WHERE.

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

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

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

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

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

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

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

Кроме того, если все объединения являются ВНУТРЕННИМИ, то обе версии эквивалентны. Однако в тот момент, когда у вас есть ВНЕШНЕЕ соединение в любом месте оператора, все становится намного сложнее, и это практически гарантирует, что то, что вы пишете, не будет запрашивать то, что, по вашему мнению, вы написали.

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

Оракул:

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

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

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

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

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