Внутреннее и внешнее соединение при помощи оператора JOIN


Рубрика: Учебные материалы
Метки:
Внутреннее и внешнее соединение при помощи оператора JOIN

Соединения с использованием фразы FROM

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

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

таблица [INNER | {FULL | LEFT | RIGHT} [OUTER]] JOIN таблица {ON условие}

 

Внутреннее соединение

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

Запрос: Список всех клиентов с указанием названий городов, в которых они проживают

 

SELECT FName, LName, CityName

FROM Customer k JOIN

City c ON k.IdCity = c.IdCity

 

При соединении с использованием фразы FROM дополнительное условие можно для увеличения наглядности запроса помещать во фразу WHERE. В этом случае второй пример с предыдущего раздела примет такой вид.
Запрос: Список всех клиентов из Казани с фамилией Иванов

 

SELECT K.IdCust, k.FName

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity

WHERE k.LName = 'Иванов' AND c.CityName = 'Казань'

 

Внешнее соединение

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

С помощью специальных ключевых слов LEFT OUTER, RIGHT OUTER и FULL OUTER, написанных перед JOIN, можно выполнить соответственно левое, правое и полное соединение. В SQL-выражении запроса таблица, указанная слева от оператора JOIN, называется левой, а указанная справа от него — правой.

 

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

 

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

 

Соответственно левое и правое внешние соединения различаются только порядком следования таблиц.

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

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

 

SELECT c.CityName, a.CountCity

FROM City c LEFT OUTER JOIN

(SELECT IdCity, COUNT(*) AS CountCity

FROM Customer

GROUP BY IdCity) a ON c.IdCity = a.IdCity

ORDER BY c.CityName

 

Если в данном запросе заменить левое внешнее соединение на внутреннее, то из результата будут потеряны города, из которых нет ни одного клиента (проверьте это заменив LEFT OUTER JOIN на INNER JOIN и объясните причину разницы). Обратите внимание, что таблица City соединяется не с таблицей, а с подзапросом, которому задан псевдоним a.

Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных (с использование оператора JOIN для соединения таблиц):

 

  • список всех товаров, которые когда-либо заказывал заданный клиент;
  • список всех клиентов, не имеющих ни одного заказа.

 

Множественные операции

Множественные операции выполняются над наборами записей (таблицами), полученными в результате запросов, и, в свою очередь, возвращают таблицу.

В стандарте SQL множественные операции имеют следующий синтаксис:

запрос {UNION | INTERSECT | EXCEPT} [DISTINCT | ALL] запрос

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

 

  • UNION — все строки таблиц, возвращенных обоими запросами;
  • INTERSECT — только те строки, которые имеются в таблицах обоих запросов;
  • EXCEPT — только те строки таблицы первого запроса, которых нет среди строк таблицы второго запроса.

 

Запросы, содержащие множественные операторы, называются составными.

Стандарт SQL определяет следующие правила относительно повторяющихся строк в таблицах.

 

  • базовые таблицы не могут содержать повторяющихся строк (это принципиальное требование реляционной модели данных);
  • результирующие таблицы запросов могут содержать повторяющиеся строки, если это не запрещено ключевым словом DISTINCT во фразе SELECT;
  • результирующие таблицы множественных операций не могут содержать повторяющихся строк, если это не разрешено ключевым словом ALL.

 

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

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

 

  • обе таблицы должны иметь одинаковое количество столбцов;
  • соответствующие пары столбцов должны быть одинаковых или совместимых типов.

Комментариев: 1 RSS

1 Людмила 02-02-2014 23:30

Просто и доходчиво написано. Раскрывается логика работы SQL-запросов.

Оставьте комментарий!

grin LOL cheese smile wink smirk rolleyes confused surprised big surprise tongue laugh tongue rolleye tongue wink raspberry blank stare long face ohh grrr gulp oh oh downer red face sick shut eye hmmm mad angry zipper kiss shock cool smile cool smirk cool grin cool hmm cool mad cool cheese vampire snake excaim question

Комментарий будет опубликован после проверки

Вы можете войти под своим логином или зарегистрироваться на сайте.

(обязательно)