Предикаты сравнения

Пример 15.1. Найти номера отделов, в которых работает ровно 30 сотрудников.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING COUNT(*) = 30;

Конечно, этот запрос можно сформулировать и без использования разделов GROUP BY и HAVING. Например, возможна следующая формулировка (пример 15.1a):

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT COUNT (*)
FROM EMP EMP1
WHERE EMP1.DEPT_NO = EMP.DEPT_NO) = 30;

Обратите внимание, что в формулировке 15.1a отдельная проверка условия DEPT_NO IS NOT NULL не требуется.

Пример 15.2. Найти номера всех отделов, в которых средний размер зарплаты сотрудников превосходит 12000 руб.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) > 12000.00;

Очевидно, что и в этом случае возможна формулировка запроса без использования разделов GROUP BY и HAVING (пример 15.2a):

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT AVG(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP1.DEPT_NO = EMP.DEPT_NO) > 12000.00;

Немного задержимся на этих примерах и обсудим, что означает различие в формулировках запросов. В соответствии с семантикой оператора SELECT, при выполнении запросов 15.1a и 15.2a для каждой строки таблицы EMP в цикле просмотра внешнего запроса будет выполняться подзапрос, который в случае наших примеров выберет из таблицы EMP (EMP1) все строки со значением столбца DEPT_NO, равным значению этого столбца в текущей строке внешнего цикла. Другими словами, для каждой строки внешнего цикла образуется группа, для нее проверяется условие выборки, и в списке выборки используется имя столбца этой неявной группировки. Из-за того, что группа образуется и оценивается для каждой строки таблицы EMP, мы вынуждены указать в разделе SELECT спецификацию DISTINCT.

Формулировки 15.1 и 15.2 обеспечивают более четкие указания для выполнения запроса. Нужно сразу сгруппировать таблицу EMP в соответствии со значениями столбца DEPT_NO, отобрать нужные группы, и для каждой отобранной группы вычислить значения выражений списка выборки. В этом случае семантика выполнения запроса не предписывает выполнения лишних действий. Конечно, в развитой реализации SQL компилятор должен суметь понять, что формулировки 15.1a и 15.2a эквиваленты формулировкам 15.1 и 15.2 соответственно, и избежать выполнения лишних действий.

Пример 15.3. Найти номера всех отделов, в которых суммарный объем зарплаты сотрудников меньше суммарного объема зарплаты всех руководителей отделов.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING SUM(EMP_SAL) < (SELECT SUM(EMP1.EMP_SAL)
FROM EMP EMP1, DEPT
WHERE EMP1.EMP_NO = DEPT_MNG);

И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING (пример 15.3a). Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания.

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT SUM(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP1.DEPT_NO = EMP.DEPT_NO) <
(SELECT SUM(EMP1.EMP_SAL)
FROM EMP EMP1, DEPT
WHERE EMP1.EMP_NO = DEPT_MNG);

Пример 15.4. Для каждого отдела найти его номер, имя руководителя, число сотрудников, минимальный, максимальный и средний размеры зарплаты сотрудников.

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), MIN(EMP1.EMP_SAL),
MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAME
HAVING DEPT.DEPT_MNG = EMP.EMP_NO;

Этот запрос иллюстрирует несколько интересных черт языка SQL. Во первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое – в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 15.4a:

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), MIN(EMP1.EMP_SAL),
MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
AND DEPT.DEPT_MNG = EMP.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP.EMP_NAME;

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

Наконец, легко видеть, что по существу группировка производится по значениям столбца DEPT.DEPT_NO. Остальные столбцы, указанные в списке столбцов группировки, функционально определяются столбцом DEPT.DEPT_NO. Тем не менее, в первой формулировке мы включили в этот список столбцы DEPT.DEPT_MNG и EMP.EMP_NO, чтобы их имена можно было использовать в условии раздела HAVING, и столбец EMP.EMP_NAME, чтобы можно было использовать его имя в списке выборки раздела SELECT. Другими словами, мы вынуждены расширять запрос избыточными данными, чтобы выполнить формальные синтаксические требования языка. Как видно, во второй формулировке мы смогли удалить из списка группировки два столбца. Кстати, не следует думать, что многословие первой формулировки помешает СУБД выполнить запрос настолько же эффективно, как запрос во второй формулировке. Грамотно построенный оптимизатор SQL сам приведет первую формулировку ко второй.

Наконец, и этот запрос можно сформулировать без использования раздела GROUP BY за счет использования подзапросов в списке раздела SELECT (пример 15.4b):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME,
(SELECT COUNT(*)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
(SELECT MIN(EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
(SELECT MAX(EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
(SELECT AVG(EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO;

Должно быть понятно, что здесь мы снова имеем замаскированную группировку строк по значениям столбца DEPT.DEPT_NO и вычисление агрегатных функций для каждой группы. Формально группа строится каждый раз заново при вызове каждой агрегатной функции. Хороший компилятор SQL должен привести формулировку 15.4b к виду 15.4a.

И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 15.4c):

SELECT DEPT.DEPT_NO,
CASE WHEN DEPT.DEPT_MNG IS NULL THEN NULL
ELSE (SELECT EMP.EMP_NAME
FROM EMP
WHERE EMP.EMP_NO = DEPT.DEPT_MNG),
COUNT(*), MIN(EMP1.EMP_SAL),
MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO;

Дата добавления: 2014-01-03