Язык SQL

8.5.3 Объединение результатов нескольких запросов и соединения таблиц

Объединение результатов нескольких запросов

Результаты нескольких запросов можно объединить операциями UNION и UNION ALL. Объединение возможно, если результирующие таблицы соединяемых запросов имеют одинаковое число столбцов попарно одинаковых типов. Имена соответствующих столбцов могут различаться. В результате обычно используются имена первого из объединяемых запросов.

Структура объединения:

Запрос 1 без ORDER BY
UNION [ALL]
Запрос 2 без ORDER BY
[ORDER BY ...]

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

Пример: Выбрать сотрудников отдела 20, присоединив к ним клерков из любых отделов. Первый запрос даёт повторы строк (листинг 8.7).

SELECT ename,job, deptno FROM emp WHERE deptno=20
UNION ALL
SELECT ename,job, deptno FROM emp WHERE job='CLERK'
ename job deptno
SMITH CLERK 20
JONES MANAGER 20
SCOTT ANALYST 20
ADAMS CLERK 20
FORD ANALYST 20
SMITH CLERK 20
ADAMS CLERK 20
JAMES CLERK 30
MILLER CLERK 10

Убрав слово ALL, получаем ответ без повторов для Смита и Адамса.

Этот же результат даст единственный запрос со сложным условием:

SELECT DISTINCT ename,job, deptno FROM emp WHERE deptno=20 OR job='CLERK'

Из описаний процессов выполнения запросов понятно, что в единственном запросе фраза WHERE работает только один раз, а в запросе с UNION, по крайней мере, дважды. Повторную выборку строк в одном запросе организовать нельзя.

Выполнение запросов с UNION

1. Выполнить составляющие запросы.
2. Объединить результаты, разрешая или удаляя повторы.
3. Если имеется фраза ORDER BY, упорядочить результат. Как всегда, фраза ORDER BY должна быть последней в запросе.

Соединения таблиц

Соединения двух и более таблиц могут выполняться в одном запросе с указанием условий соединения. Пример: Запрос в листинге 8.8 выбирает фамилии сотрудников, номера и названия отделов, в которых они работают.

SELECT ename, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno
ename deptno dname
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING

Соединяем те строки таблиц emp и dept, которые имеют одинаковые значения столбца deptno. Поскольку deptno имеется в обеих таблицах, в условии соединения следует уточнить название столбца названием его таблицы, например, emp.deptno. В списке фразы SELECT только для одного столбца необходимо указание таблицы emp.deptno или dept.deptno. Если этого не сделать, появится сообщение об ошибке, потому что транслятор не может "понять" из какой таблицы выбрать deptno. Остальные столбцы ename и dname имеются только в одной таблице. При желании префиксы можно поставить и перед их именами.

Замечание. Различайте связи, объединения и соединения таблиц. Связи реализуются внешними ключами и работают во время манипулирования данными, обеспечивая выполнение ограничений ссылочной целостности. Объединения —это запросы с UNION и UNION ALL. Соединения создаются в запросах пользователя. Их смысл целиком на совести программиста, создающего запрос. СУБД в общем случае не хранит всех смыслов данных и не следит за осмысленностью соединений.

Внутренние и внешние соединения

В последнем рассмотренном примере и в операциях соединения реляционной алгебры (по равенству и не по равенству) соединялись существующие строки двух и более таблиц/отношений. (А как иначе?) Такие соединения называются внутренними. Существуют ещё внешние соединения. В них строка одной таблицы может соединяться с пустой строкой из другой таблицы. Несмотря на кажущуюся странность этой операции, она отражает смысл, имеющийся в моделях бизнеса.

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

Пример: Просмотреть список сотрудников во всех отделах, указав названия отделов.

SELECT ename, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno

Это внутреннее соединение. В ответе отсутствует только что введённый в таблицу dept отдел 50. Поэтому пользователь может считать, что такого отдела нет. Но мы же знаем, что отдел существует, только список его сотрудников пустой.

Избежать подобных казусов позволяют внешние соединения.

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

Для задания внешнего соединения до появления стандарта SQL92 во фразе WHERE использовались специальные обозначения, свои для каждого производителя. Например, в Cache используется обозначение =* для левого внешнего соединения и *= для правого внешнего соединения.

Пример: Правильное решение предыдущего примера с использованием правого внешнего соединения.

SELECT ename, dname
FROM emp, dept
WHERE emp.deptno *= dept.deptno

Теперь в ответе присутствует отдел 50, но сотрудников в нём нет. В стандарте существуют:

1. Левое внешнее соединение (LEFT OUTER JOIN).
2. Правое внешнее соединение (RIGHT OUTER JOIN).
3. Полное внешнее соединение (FULL OUTER JOIN).

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

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

Выполнение внешних соединений

Порядок действий при выполнении полного внешнего соединения двух таблиц:

1. Построить внутреннее соединение таблиц.
2. Каждую строку первой таблицы, для которой не найдена соответствующая строка второй таблицы, добавить в результат запроса, приписав строку второй таблицы со значениями NULL.
3. Каждую строку второй таблицы, для которой не найдена соответствующая строка первой таблицы, добавить в результат запроса, приписав строку первой таблицы со значениями NULL

Левое внешнее соединение получится, если не выполнять п. 3. Правое внешнее соединение получится, если не выполнять п. 2.

Соединения в стандарте SQL92

В стандарте SQL92 внешние соединения определяются во фразе FROM, которая получает сложный синтаксис. Мы рассмотрим основные частные случаи.

• Внутреннее соединение. Основной вариант. Синтаксис:

  SELECT список_SELECT
  FROM имя_таблицы INNER JOIN имя_таблицы ON условие_соединения
  

  Пример:

  Старый формат	Новый формат
  SELECT ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno =dept.deptno	SELECT ename,emp.deptno,dname FROM emp INNER JOIN dept ON emp.deptno =dept.deptno
• Естественное внутреннее соединение. Синтаксис:

  SELECT фраза_SELECT
  FROM имя_таблицы NATURAL JOIN имя_таблицы USING (список_столбцов)
  

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

  SELECT ename, emp.deptno, dname
  FROM emp INNER JOIN dept
  USING (deptno)
  ename deptno dname
  SMITH 20 RESEARCH
  ALLEN 30 SALES
  WARD 30 SALES
  JONES 20 RESEARCH
  MARTIN 30 SALES
  BLAKE 30 SALES
  CLARK 10 ACCOUNTING
  SCOTT 20 RESEARCH
  KING 10 ACCOUNTING
  TURNER 30 SALES
  ADAMS 20 RESEARCH
  JAMES 30 SALES
  FORD 20 RESEARCH
  MILLER 10 ACCOUNTING
  

  Пример 8.9. Пример внутреннего соединения таблиц с использованием USING

• Внешние соединения — полное, левое, правое. Синтаксис:

  SELECT список_SELECT FROM имя_таблицы
  FULL|LEFT|RIGHT OUTER JOIN имя_таблицы ON условие_соединения
  

  В естественном внешнем соединении фраза ON условие_соед-инения, как в п. 1,2 заменяется фразой USING список_столбцов

  Пример:

  Старый формат	Новый формат
  SELECT ename, dname FROM emp, dept WHERE emp.deptno =*dept.deptno	SELECT ename, dname FROM emp LEFT JOIN dept ON emp.deptno =dept.deptno SELECT ename, dname FROM emp LEFT JOINUSING (deptno)

• Для задания декартова произведения используют ключевое слово

  CROSS JOIN.

8.5.4 Запросы с группированием

Фраза GROUP BY, упоминавшаяся ранее, обеспечивает объединение строк с одинаковыми значениями в перечисленных столбцах. Такое преобразование необходимо для получения итоговых данных с помощью многострочных (они же статистические или агрегатные) функций MIN, MAX, SUM, COUNT, AVG и др.

Пример: В листинге 8.10 приведен запрос, который находит суммарную заработную плату по отделам.

SELECT deptno, SUM(sal) salary
FROM emp
GROUP BY deptno
deptno salary 30 8750
20 10875
30 9400

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

Если убрать фразу GROUP BY, то образуется одна группа из всех строк таблицы и ответ состоит из единственной строки, представляющей зарплату всех сотрудников из таблицы emp.

Аргументы функций SUM, AVG и COUNT могут уточняться указанием DISTINCT.

Примеры (не очень умные, но поясняющие суть дела) приведены в листинге 8.11. Первый запрос выдаёт количество сотрудников, получающих зарплату, второй — количество разных зарплат, а третий — количество сотрудников, получающих комиссионные (NULL не учитывается, 0 считается).

SELECT COUNT(sal) FROM emp
Aggregate_1
14
SELECT COUNT(DISTINCT sal) FROM emp
Aggregate_1
12
SELECT COUNT(comm) FROM emp
Aggregate_1
4

Выполнение запросов с группированием

Порядок действий при выполнении однотабличных запросов с фразой GROUP BY:

1. Если имеется фраза WHERE, применить к строкам условие отбора, выбрав только те строки, для которых условие выполняется.
2. Разделить оставшиеся строки на группы строк, имеющих одинаковые значения во всех столбах, по которым производится группирование, описанное фразой GROUP BY.
3. Если в аргументе групповой функции указан спецификатор DISTINCT, удалить все повторяющиеся строки
4. Для каждой группы строк вычислить значения групповых функций, создав одну строку результата запроса. Вычисления проводятся для значений столбца у всех строк, входящих в группу.
5. 5. Если имеется фраза ORDER BY, отсортировать результат запроса.

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

Отбор групп строк — фраза HAVING

Фраза HAVING предназначена для организации отбора групп.

Формат записываемого в ней условия такой же, как во фразе WHERE. Если условие отбора даёт значение TRUE, группа строк остаётся, и в результате для неё создаётся одна строка. Если же проверка даёт FALSE или NULL, группа строк не рассматривается, и результирующая строка для неё не формируется.

Пример:

SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING SUM(sal)  > 3100

Фраза HAVING почти всегда используется вместе с фразой GROUP BY, однако некоторые трансляторы допускают применение HAVING в отсутствие GROUP BY. В этом случае образуется одна группа из всех строк таблицы.

Правила работы с NULL'ами такие же как в условиях фразы WHERE. Групповые функции можно использовать только в фразах SELECT, HAVING и ORDER BY.

Выполнение запросов с фразой HAVING

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

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

Порядок действий при выполнении многотабличных запросов с фразой

HAVING:

1. Создать декартово произведение таблиц, перечисленных во фразе FROM.
2. Применить условие фразы WHERE, чтобы оставить только те строки, для которых это условие выполнено.
3. Применить предложение GROUP BY для разделения строк на группы.
4. Отобрать группы строк в соответствии с условием фразы HAVING, оставив только группы удовлетворяющие этому условию и сформировав для каждой отобранной группы одну строку результата.
5. Если указан спецификатор DISTINCT, удалить все повторяющиеся строки.
6. Если имеется фраза ORDER BY, отсортировать результат запроса.