 |
|
Предикаты сравнения
Пример 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 EMP1WHERE DEPT.DEPT_NO = EMP1.DEPT_NOGROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAMEHAVING 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 EMP1WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND DEPT.DEPT_MNG = EMP.EMP_NOGROUP 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, EMPWHERE 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 EMP1WHERE DEPT.DEPT_NO = EMP1.DEPT_NOGROUP BY DEPT.DEPT_NO;Предикат between
Пример 19.5. Найти номера отделов и минимальный и максимальный размер зарплаты служащих для отделов, в которых средний размер зарплаты служащих не меньше среднего размера зарплаты служащих во всей компании и не больше 30000 руб.
SELECT DEPT_NO, MIN(EMP_SAL), MAX(EMP_SAL) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG(EMP_SAL) BETWEEN (SELECT AVG(EMP_SAL) FROM EMP) AND 30000.00;Еще раз приведем возможную формулировку этого запроса без использования разделов GROUP BY и HAVING (пример 19.5.1):
SELECT DISTINCT DEPT_NO, (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO), (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO)FROM EMPWHERE (SELECT AVG(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO) BETWEEN (SELECT AVG(EMP_SAL) FROM EMP) AND 30000.00;Как видно, отказ от использования раздела GROUP BY приводит к размножению однотипных подзапросов, строящих одну и ту же группу строк, над которой вычисляется агрегатная функция.
Предикат null
Пример 19.6. Найти номера и число служащих отделов, данные о руководителях которых не содержат номер отдела (конечно, в этом случае нас интересуют только те отделы, у которых имеется руководитель).
SELECT DEPT.DEPT_NO, COUNT(*) FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO GROUP BY DEPT.DEPT_NO, EMP1.DEPT_NO HAVING EMP1.DEPT_NO IS NULL;Как и в примере 19.4, условие раздела HAVING можно переместить в раздел WHERE и получить вторую формулировку (пример 19.6.1):
SELECT DEPT.DEPT_NO, COUNT(*) FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO AND EMP1.DEPT_NO IS NULL GROUP BY DEPT.DEPT_NO;Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела GROUP BY оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора SELECT), что показывает пример 19.6.2:
SELECT DEPT.DEPT_NO, (SELECT COUNT(*) FROM EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO)FROM DEPT, EMPWHERE DEPT.DEPT_MNG = EMP.EMP_NO AND EMP.DEPT_NO IS NULL;Предикат in
Пример 19.7. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела.
SELECT DEPT.DEPT_NOFROM DEPT, EMPWHERE DEPT.DEPT_NO = EMP.DEPT_NOGROUP BY DEPT.DEPT_NOHAVING AVG(EMP.EMP_SAL) IN (SELECT MAX(EMP1.EMP_SAL) FROM EMP, DEPT DEPT1 WHERE EMP.DEPT_NO = DEPT1.DEPT_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO);Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 19.7.1):
SELECT DEPT.DEPT_NOFROM DEPTWHERE (SELECT AVG(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) IN (SELECT MAX(EMP1.EMP_SAL) FROM EMP, DEPT DEPT1 WHERE EMP.DEPT_NO = DEPT1.DEPT_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO);Но в данном случае мы не можем отказаться от раздела GROUP BY во втором вложенном запросе, поскольку без этого невозможно получить множество значений результатов вызова агрегатной функции.
Предикат like
Пример 19.8. Во всех отделах найти имена и число служащих, у которых в данном отделе имеются однофамильцы и фамилии которых начинаются со строки символов, изображающей фамилию руководителя отдела.
SELECT EMP_NAME, COUNT(*)FROM EMP, DEPTWHERE EMP.DEPT_NO = DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP_NAMEHAVING COUNT(*) > 1 AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING (пример 19.8.1), но вряд ли это разумно, поскольку формулировка является менее понятной и существенно более сложной.
SELECT EMP_NAME, (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND EMP1.EMP_NAME = EMP.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO) + 1FROM EMPWHERE (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND EMP1.EMP_NAME = EMP.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO) > 1 AND EMP_NAME LIKE (SELECT EMP1.EMP_NAME FROM EMP EMP1, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';Предикат exists
Пример 19.9. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела (другая формулировка для примера 19.7).
SELECT DEPT.DEPT_NOFROM DEPT, EMPWHERE DEPT.DEPT_NO = EMP.DEPT_NOGROUP BY DEPT.DEPT_NOHAVING EXISTS (SELECT * FROM EMP EMP1 WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO GROUP BY EMP1.DEPT_NO HAVING MAX (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 19.9.1):
SELECT DEPT.DEPT_NOFROM DEPTWHERE EXISTS (SELECT EMP.DEPT_NO FROM EMP WHERE EMP.DEPT_NO <> DEPT.DEPT_NO GROUP BY EMP.DEPT_NO HAVING MAX (EMP.EMP_SAL)= (SELECT AVG (EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = DEPT.DEPT_NO));Предикат unique
Пример 19.10. Найти номера отделов и средний размер зарплаты служащих для таких отделов, где средний размер зарплаты служащих отличается от среднего размера зарплаты всех других отделов.
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)FROM DEPT, EMPWHERE DEPT.DEPT_NO = EMP.DEPT_NOGROUP BY DEPT.DEPT_NOHAVING UNIQIUE (SELECT AVG (EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO HAVING AVG (EMP1.EMP_SAL) = AVG (EMP.EMP_SAL));Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS (пример 19.10.1):
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)FROM DEPT, EMPWHERE DEPT.DEPT_NO = EMP.DEPT_NOGROUP BY DEPT.DEPT_NOHAVING NOT EXISTS (SELECT EMP1.DEPT_NO FROM EMP EMP1 WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO GROUP BY EMP1.DEPT_NO HAVING AVG (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));