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

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

SELECT DEPT_NO

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO

HAVING COUNT(*) = 30;

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

SELECT DISTINCT DEPT_NO

FROM EMP

WHERE (SELECT COUNT (*)

FROM EMP EMP1

WHERE EMP1.DEPT_NO = EMP.DEPT_NO) = 30;

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

Пример 19.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(пример 19.2.1):

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.1.1и15.2.1для каждой строки таблицыEMPв цикле просмотра внешнего запроса будет выполняться подзапрос, который в случае наших примеров выберет из таблицыEMP(EMP1) все строки со значением столбцаDEPT_NO, равным значению этого столбца в текущей строке внешнего цикла. Другими словами, для каждой строки внешнего цикла образуется группа, для нее проверяется условие выборки, и в списке выборки используется имя столбца этой неявной группировки. Из-за того, что группа образуется и оценивается для каждой строки таблицыEMP, мы вынуждены указать в разделеSELECTспецификациюDISTINCT.

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

Пример 19.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(пример 19.3.1). Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания.

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);

Пример 19.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и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 19.4.1:

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(пример 19.4.2):

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.4.2к виду15.4.1.

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

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;