Внешние объединения
В некоторых случаях использование внутреннего соединения может привести к нежелательной потере информации.
Внешние объединения делятся на три типа:
левые;
правые;
полные.
Общий синтаксис кода для установления внешнего объединения таблиц:
SELECT <Список выборки>
FROM <Таблица, которую вы хотите сделать левой>
<LEFT | RIGHT [OUTER] JOIN >
<Таблица, которую вы хотите сделать правой>
[ON <Условия соединения> ]
Обратите внимание на то, что слово OUTER является необязательным.
Выполнение оператора приводит к тому, что выводятся не только те данные, которые соответствуют критериям отбора, но и данные, которые зависят от стороны соединения.
Левое внешнее объединение
Левое внешнее объединение возвращает все записи левой таблицы объединения даже в том случае, если соответствующих записей в правой таблице не существует.
Если условие объединения не выполняется, столбцы правой таблицы заполняются неопределенными значениями null.
Пример левого внешнего объединения:
Пример 8.
SELECT T.Название, Pr.Дата, Pr.Количество_ед_товара
FROM Товары Т LEFT OUTER JOIN Продажи Pr
ON Т.ID_товара = Pr.Товар
В запросе будет выведен список всех товаров вне зависимости от того, продавался товар или нет.
Пример 9.
Требуется вывести список поставщиков и общее количество поставленных ими товаров в заданный период - с 1.01.11 по 20.04.11
SELECT P.Название AS Фирма, COUNT(Поставки.Поставщик)*SUM(Поставки.Количество_ед_товара)
AS ‘Общее количество поставленных товаров’
FROM Поставщики P LEFT JOIN Поставки Post
ON P.ID_поставщика = Post.Поставщик
WHERE Post.Дата BETWEEN ‘1.01.11’ AND ‘20.04.11’
GROUP BY P.Название_фирмы
Правое внешнее объединение
Правое внешнее объединение возвращает все записи правой таблицы объединения даже в том случае, если записи в левой таблице не существуют.
Если условие объединения не выполняется, столбцы левой таблицы заполняются неопределенными значениями null.
Пример правого внешнего объединения в стандарте ANSI:
Пример 10.
Требуется вывести список всех поставщиков и, если имеются, их телефоны.
SELECT P.Название_фирмы, Телефон
FROM Телефоны Tel RIGHT JOIN Поставщики P
ON P.ID_поставщика = Tel.Фирма_поставщик
Если в приведенном запросе в условиях объединения поменять местами таблицы, получим список всех имеющихся в таблице телефонов с указанием тех поставщиков, у которых он имеется. Оставшиеся записи будут заполнены неопределенными значениями null.
Команда UNION
Команда union связывает воедино данные, полученные от нескольких команд select, то есть она позволяет связать данные из двух и более итоговых наборов в один.
Команда union имеет следующий синтаксис:
Команда SELECT 1
UNION [ALL]
Команда SELECT 2
Однако применение этой команды возможно при некоторых условиях:
все списки выборки в union должны содержать одинаковое число столбцов;
соответствующие столбцы должны принадлежать к одному типу данных (или преобразовываться к нему, явно или неявно), иначе SQL Server выдаст сообщение об ошибке. Неявные преобразования типов подчиняются правилам приоритетов.
Первая команда select в конструкции union определяет заголовки столбцов результата.
Если задан параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае в результирующем наборе остаются только уникальные строки.
Пример 11. Пусть имеется небольшая фирма. В отделе кадров хранится краткая информация о работниках фирмы (Фамилия, год рождения, телефон) в трех таблицах:
«Программисты»,
«Бухгалтера»,
«Финансовые аналитики».
Программисты
Фамилия |
Год рождения |
Телефон |
Лепёшкин |
1970 |
3456789 |
Бубликов |
1975 |
3452334 |
Бухгалтера
Фамилия |
Год рождения |
Телефон |
Кукушкина |
1965 |
45644567 |
Орлова |
1971 |
35665456 |
Голицын |
1973 |
45776557 |
Финансовые аналитики
Фамилия |
Год рождения |
Телефон |
Никитин |
1975 |
46765457 |
Самородов |
1978 |
45677655 |
Жженых |
1976 |
56677876 |
Пусть необходимо извлечь фамилии и год рождения всех сотрудников организации. Для этого нужно будет выполнить следующий запрос:
SELECT Фамилия, [Год рождения] FROM Программисты
UNION ALL
SELECT Фамилия, [Год рождения] FROM Бухгалтера
UNION ALL
SELECT Фамилия, [Год рождения] FROM [Финансовые аналитики]
Данный запрос вернёт результат
Фамилия |
Год рождения |
Лепёшкин |
1970 |
Бубликов |
1975 |
Кукушкина |
1965 |
Орлова |
1971 |
Голицын |
1973 |
Никитин |
1975 |
Самородов |
1978 |
Жженых |
1976 |
Задания для самостоятельной работы (лаб. № 3)
Вывести список плохо продаваемых товаров (общее количество продаж <=5).
Вывести страну-производителя, товары которой покупались менее / более других.
Вывести самый востребованный / невостребованный товар.
Вывести количество поставок для заданной категории товара.
Вывести список всех поставщиков, кроме московских, и все поставки каждого поставщика.
Вывести список поставщиков и общее количество поставок заданной категории товаров.
Вывести список поставщиков, у которых в базе данных отсутствует номер телефона.
Сформировать запрос, выводящий следующую информацию:
Поставщик |
Товар |
Общее количество поставленного товара |
Единицы измерения |
Сформировать запрос, выводящий следующую информацию:
Товар |
Клиент |
Общее количество приобретенных товаров |
Единицы измерения |