SQL-Урок 11. Расширенное объединение таблиц (OUTER JOIN)

В предыдущем разделе мы рассмотрели самые простые способы объединения таблиц - с помощью предложений WHERE и INNER JOIN. Эти объединения называются внутренними объединениями или объединениями по эквивалентности. Однако SQL имеет в своем арсенале гораздо больше возможностей объединить таблицы, а именно существуют также и другие виды объединений: внешние объединения, природные объединения и самообъединения. Но для начала рассмотрим, каким образом мы можем присваивать таблицам псевдонимы, поскольку в дальнейшем, мы будем вынуждены использовать полные названия полей (Таблиця.Поле), которыми без сокращений будет очень трудно оперировать из-за их большой длины.

1. Использование псевдонимов таблиц

В предыдущем разделе мы узнали, как можно использовать псевдонимы для ссылки на определенные поля таблицы или на расчетные поля. SQL так же дает нам возможность использовать псевдонимы вместо имен таблиц. Это дает нам такие преимущества, как более короткий синтаксис SQL и позволяет много раз использовать одну и ту же таблицу в операторе SELECT.

SELECT Seller_name, SUM(Amount) AS Sum1
FROM Sellers AS S, Sumproduct AS SP
WHERE S.City = SP.City
GROUP BY Seller_name

Мы отобразили общую сумму реализованного товара по каждому продавцу. В нашем SQL запросе мы использовали такие псевдонимы: для расчетного поля SUM (Amount) псевдоним Sum1 для таблицы Sellers псевдоним S и для Sumproduct псевдоним SP. Заметим, что псевдонимы таблиц могут быть применены и в других предложениях, как ORDER BY, GROUP BY и других.

2. Самообъединения

Рассмотрим пример. Предположим, нам нужно узнать адрес продавцов, торгующих в той же стране, что и John Smith. Для этого создадим такой запрос:

SELECT City, Country, Seller_name
FROM Sellers
WHERE Country = (SELECT Country FROM Sellers WHERE Seller_name = 'John Smith')

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

SELECT S1.Address, S1.City, S1.Country, S1.Seller_name
FROM Sellers AS S1, Sellers AS S2
WHERE S1.Country = S2.Country AND S2.Seller_name = 'John Smith'

Для решения этой задачи использовались псевдонимы. Первый раз для таблицы Sellers присвоили псевдоним S1, второй раз - псевдоним S2. После этого эти псевдонимы можно применять в качестве имен таблиц. В операторе WHERE мы с названием каждого поля добавляем префикс S1, для того, чтобы СУБД понимала поля которой таблицы нужно выводить (поскольку мы из одной таблицы сделали две виртуальные). Предложение WHERE сначала объединяет таблицы, а затем фильтрует данные второй таблицы по полю Seller_name, чтобы вернуть только необходимые значения.

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

3. Естественное объединение

Естественное объединение - это объединение, в котором вы выбираете только те столбцы, которые не повторяются. Обычно это делается с помощью записи ( SELECT *) для одной таблицы и указанием перечня полей - для остальных таблиц. Например:

SELECT SP.*, S.Country
FROM Sumproduct AS SP, Sellers AS S
WHERE SP.City = S.City

В этом примере метасимвол (*) используется только для первой таблицы. Все остальные столбцы указаны явно, поэтому дубликаты столбцов не выбираются.

4. Внешнее объединение (OUTER JOIN)

Обычно при объединении связывают строки одной таблицы с соответствующими строками другой, однако в некоторых случаях может потребоваться включать в результат строки, не имеющие связанных строк в другой таблице (т.е. выбираются совершенно все строки из одной таблицы и добавляются только связанные строки из другой). Объединение такого типа называется внешним. Для этого используются ключевые слова OUTER JOIN ... ON ... с приставкой LEFT или RIGHT. Рассмотрим пример, предварительно добавив в таблицу Sellers нового продавца - Semuel Piter, который не имеет продаж:

SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers LEFT OUTER JOIN Sumproduct ON Sellers.City=Sumproduct.City
GROUP BY Seller_name

Данным запросом мы вытащили перечень всех продавцов в базе и подсчитали для них общее количество проданного товара за все месяцы. Видим что по новому продавцу Semuel Piter отсутствуют продажи. Если бы мы использовали внутреннее объединение, то нового продавца мы бы не увидели, поскольку он не имеет записей в таблице Sumproduct. Мы можем изменять направление объединения не только прописывая LEFT или RIGHT, но и просто меняя порядок таблиц (т. е. две записи будут давать одинаковый результат: Sellers LEFT OUTER JOIN Sumproduct та Sumproduct RIGHT OUTER JOIN Sellers).

Также некоторые СУБД позволяют осуществлять внешнее объединение по упрощенной записи, используя знаки * = и = * , что соответствует LEFT OUTER JOIN и RIGHT OUTER JOIN соответственно. Таким образом предыдущий запрос можно было бы переписать так:

SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers, Sumproduct
WHERE Sellers.City *= Sumproduct.City

К сожалению Access не поддерживает сокращенную запись для внешнего объединения.

5. Полное внешнее объединение (FULL OUTER JOIN)

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

SELECT Seller_name, Product
FROM Sellers FULL OUTER JOIN Sumproduct ON Sellers.City=Sumproduct.City

Опять же, полное внешнее объединение не поддерживают такие СУБД: Access, MySQL, SQL Server и Sybase. Как обойти эту несправедливость, мы рассмотрим в следующем разделе.

Статьи по теме:

  • SQL-Урок 10. Объединение таблиц (INNER JOIN)
  • SQL-Урок 12. Комбинированные запросы (UNION)