Выборка данных. Фразы ORDER BY, GROUP BY и CONNECT BY предложения SELECT. Множественные операции

Комбинирование результатов SELECT множественными операциями

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

Сложение строк — результатов SELECT оператором UNION

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

"Выдать номера сотрудников, имеющих должность MANAGER или имеющих подчиненных":

SELECT mgr FROM emp WHERE mgr IS NOT NULL
UNION
SELECT empno FROM emp WHERE job = 'MANAGER'
;

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

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

SELECT mgr FROM emp WHERE mgr IS NOT NULL
UNION ALL
SELECT empno FROM emp WHERE job = 'MANAGER'
;

Если программист, зная свои данные, уверен, что строки в объединении результатов запросов не повторяются, ему следует использовать именно UNION ALL и существенно сэкономить на этом ресурсы СУБД, включая процессорное время обработки.

Пересечение результатов SELECT оператором INTERSECT

Пересечение результатов двух или более запросов достигается оператором INTERSECT.

Пример. "Выдать номера сотрудников, имеющих должность MANAGER и имеющих подчиненных":

SELECT mgr FROM emp WHERE mgr IS NOT NULL
INTERSECT
SELECT empno FROM emp WHERE job = 'MANAGER'
;

Вычитание результатов SELECT оператором MINUS

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

Пример. "Выдать номера сотрудников, имеющих подчиненных, но не в должности MANAGER":

SELECT mgr FROM emp WHERE mgr IS NOT NULL
MINUS
SELECT empno FROM emp WHERE job = 'MANAGER'
;

Упражнение. Поменяйте в последнем запросе предложения SELECT местами: выдайте список "сотрудников в должности MANAGER, но не имеющих подчиненных". Пустой ответ означает, что таковых нет.

Упражнение. Выдайте с помощью множественной операции MINUS названия отделов, где нет сотрудников.

В Oracle имя операции MINUS взято из реляционной модели, а в стандарте SQL:1999 соответствующий оператор называется EXCEPT (последнего названия придерживаются, например, DB2 и SQL Server).

Общие правила

• Комбинируемые блоки SELECT должны иметь одинаковую структуру (в Oracle — с точностью до совместимости типов столбцов: общего формата хранения, но не обязательно точности).
• При комбинировании запросов автоматически убираются дубликаты (за исключением UNION ALL).
• При комбинировании запросов отсутствующие значения (NULL) считаются равными друг другу (исключение из общего правила сравнения с отсутствующим значением), подобно тому как это происходит при использовании DISTINCT или GROUP BY.
• Фраза ORDER BY может следовать только за последним SELECT и применяться к общему результату.
• Именование столбцов окончательного результата по правилам первого предложения SELECT.

Нехарактерное для SQL самопроизвольное устранение повторяющихся строк при выполнении UNION, INTERSECT и MINUS требует внимания программиста. Например, в случае наличия повторений в столбцах-операндах UNION нельзя заменить на однопроходный SELECT с условным выражением через OR, а INTERSECT — с условным выражением через AND. Следующие два предложения дадут разные ответы:

SELECT mgr FROM emp WHERE deptno = 10 AND mgr IS NOT NULL
UNION
SELECT mgr FROM emp WHERE deptno = 20 AND mgr IS NOT NULL
;
SELECT mgr FROM emp 
WHERE ( deptno = 10 OR deptno = 20 ) AND mgr IS NOT NULL
;

Пример явного именования столбцов и упорядочения строк окончательного ответа:

SELECT mgr "Начальники не менеджеры" FROM emp WHERE mgr IS NOT NULL
MINUS
SELECT empno FROM emp WHERE job = 'MANAGER'
ORDER BY "Начальники не менеджеры"
;

Пример трактовки отсутствующих значений при множественных операциях. "Выдать должности и комиссионные сотрудников отделов 10 и 30":

SELECT job, comm FROM emp WHERE deptno = 30 
UNION
SELECT job, comm FROM emp WHERE deptno = 10
;

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

Пример явного именования столбцов и упорядочения строк окончательного ответа:

SELECT mgr "Начальники не менеджеры" FROM emp WHERE mgr IS NOT NULL
MINUS
SELECT empno FROM emp WHERE job = 'MANAGER'
ORDER BY "Начальники не менеджеры"
;