Агрегатные функции, группировка и условия раздела HAVING

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

Агрегатные функции, группировка и условия раздела HAVING

Более сложные конструкции оператора выборки

В этом разделе мы обсудим возможности языка SQL, касающиеся явного задания выражений с соединениями и порождаемых таблиц с горизонтальной связью (lateral_derived_table). Начнем с соединений.

Более сложные конструкции оператора выборки

Еще один способ формулировки запросов

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

SELECT MNG.DEPT_NO, MNG.MNG_NAME FROM (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO) AS MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) WHERE DEPT_NO_1 = DEPT_NO_2 AND MNG_SAL = (SELECT MAX (EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT_NO_1);

Пример 15.14. Найти номера отделов и имена руководителей отделов, которые числятся в тех же отделах, которыми руководят, и получают зарплату, размер которой является максимальным для сотрудников данного отдела. (html, txt)

В этом запросе порождаемая таблица MNG содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы - DEPT_NO_1 - содержит номер отдела, которым руководит данный служащий. В столбце DEPT_NO_1 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах EMP_NAME и EMP_SAL содержатся имя служащего-руководителя отдела и размер его заработной платы соответственно.

Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделе FROM, например, следующим образом (пример 15.14.1):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND DEPT.DEPT_NO = EMP.DEPT_NO AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 15.14.1.

(html, txt)

А вот как можно сформулировать тот же запрос с использованием раздела WITH (пример 15.14.2):

WITH MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) AS (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL FROM DEPT, EMP WHERE DEPT.MNG_NO = EMP.EMP_NO), MAX_DEPT_SAL (MAX_SAL, DEPT_NO) AS (SELECT MAX (EMP_SAL), DEPT_NO FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO) SELECT DEPT_NO_1, MNG_NAME FROM MNG WHERE DEPT_NO_1 = DEPT_NO_2 AND MNG_SAL = (SELECT MAX_SAL FROM MAX_DEPT_SAL WHERE MAX_DEPT_SAL.DEPT_NO = DEPT_NO_1);

Пример 15.14.2.

(html, txt)

Еще один способ формулировки запросов

SELECT DEPT.DEPT_NO, EMP.EMP_NAME FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND DEPT.DEPT_NO = EMP.DEPT_NO AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 15.14.1.

А вот как можно сформулировать тот же запрос с использованием раздела WITH (пример 15.14.2):

Пример 15.14.2.

Формальные определения

Пусть требуется выполнить некоторую операцию соединения над таблицами table1 и table2. Тогда:

Обозначим через CP результат выполнения запроса1)

SELECT * FROM table1, table2

Если задается операция JOIN (или NATURAL JOIN) без явного указания типа соединения (join_type), то по умолчанию имеется в виду INNER JOIN (или NATURAL INNER JOIN).Если в спецификации соединения (join_specification) указано ключевое слово ON, то все ссылки на столбцы, встречающиеся в условном выражении (conditional_expression), должны указывать на столбцы таблиц table1 и table2 или на столбцы таблиц внешнего запроса. Если в этом условном выражении присутствует вызов агрегатной функции, то соединенная таблица может фигурировать только в подзапросах, используемых в разделах HAVING или SELECT внешнего запроса, и ссылка на столбец в вызове функции должна указывать на столбец таблицы внешнего запроса.Для прямых соединений (CROSS JOIN) и всех других видов соединения, включающих раздел ON, заголовок результата операции совпадает с заголовком таблицы CP.Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то заголовок результата операции определяется следующим образом: если в спецификации вида соединения присутствует ключевое слово NATURAL, то будем называть соответствующими столбцами соединения (corresponding join column) все столбцы таблиц table1 и table2, которые имеют в заголовках этих таблиц одинаковые имена. Если в спецификации вида соединения присутствует ключевое слово USING, то будем называть соответствующими столбцами соединения (corresponding join column) все столбцы таблиц table1 и table2, имена которых входят в список имен столбцов раздела USING (эти столбцы должны быть одноименными в заголовках обеих таблиц). В обоих случаях типы данных каждой пары соответствующих столбцов должны быть совместимыми; будем называть списком выборки соответствующих столбцов соединения (select_list of corresponding join columns - SLCC) список элементов вида COALESCE (table1.c, table2.c) AS c*, где c является именем соответствующего столбца соединения. Элементы располагаются в том порядке, в котором они появляются в заголовке таблицы table1. Обозначим через SLT1 (SLT2) список имен столбцов таблицы table1 (table2), которые не являются соответствующими столбцами соединения. Имена располагаются в том же порядке, в котором они появляются в заголовке соответствующей таблицы;заголовок результата совпадает с заголовком результата запроса

Формальные определения

Пусть требуется выполнить некоторую операцию соединения над таблицами table1 и table2. Тогда:

Обозначим через CP результат выполнения запроса4)

SELECT * FROM table1, table2

SELECT SLCC, SLT1, SLT2 FROM

SELECT SLCC, SLT1, SLT2 FROM table1, table2;

Набор строк результата (множество или мультимножество) определяется по следующим правилам. Обозначим через T следующие наборы строк: если видом соединения является UNION JOIN, то T - пусто;если видом соединения является CROSS JOIN, то T включает все строки, входящие в CP;если в спецификацию вида соединения входит раздел ON, то T включает все строки CP, для которых результатом вычисления условного выражения является true;если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC не является пустым, то T включает все строки CP, для которых значения соответствующих столбцов соединения совпадают;2)если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC является пустым, то T включает все строки CP.

Обозначим через P1 (P2) набор (множество или мультимножество) всех строк таблицы table1 (table2), каждая из которых участвует в образовании некой строки T.Обозначим через U1 (U2) набор (множество или мультимножество) всех строк таблицы table1 (table2), ни одна из которых не участвует в образовании какой-либо строки T.Обозначим через X1 набор (множество или мультимножество) всех строк, образуемых из строк набора U1 путем добавления справа подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table2. Обозначим через X2 набор (множество или мультимножество) всех строк, образуемых из строк набора U2 путем добавления слева подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table1.Для соединений вида CROSS JOIN и INNER JOIN пусть S обозначает тот же набор строк, что и T.Для соединений вида LEFT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM T UNION ALL SELECT * FROM X1; Для соединений вида RIGHT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM T UNION ALL SELECT * FROM X2; Для соединений вида FULL OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM T UNION ALL SELECT * FROM X1 UNION ALL SELECT * FROM X2; Для соединений вида UNION JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM X1 UNION ALL SELECT * FROM X2; Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то результат операции совпадает с результатом выражения запросов

SELECT SLCC, SLT1, SLT2 FROM S; Во всех остальных случаях результат операции совпадает с S.

SELECT SLCC, SLT1, SLT2 FROM

SELECT SLCC, SLT1, SLT2 FROM table1, table2;

Набор строк результата (множество или мультимножество) определяется по следующим правилам. Обозначим через T следующие наборы строк: если видом соединения является UNION JOIN, то T - пусто;если видом соединения является CROSS JOIN, то T включает все строки, входящие в CP;если в спецификацию вида соединения входит раздел ON, то T включает все строки CP, для которых результатом вычисления условного выражения является true;если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC не является пустым, то T включает все строки CP, для которых значения соответствующих столбцов соединения совпадают;5)если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC является пустым, то T включает все строки CP.

Обозначим через P1 (P2) набор (множество или мультимножество) всех строк таблицы table1 (table2), каждая из которых участвует в образовании некой строки T.Обозначим через U1 (U2) набор (множество или мультимножество) всех строк таблицы table1 (table2), ни одна из которых не участвует в образовании какой-либо строки T.Обозначим через X1 набор (множество или мультимножество) всех строк, образуемых из строк набора U1 путем добавления справа подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table2. Обозначим через X2 набор (множество или мультимножество) всех строк, образуемых из строк набора U2 путем добавления слева подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table1.Для соединений вида CROSS JOIN и INNER JOIN пусть S обозначает тот же набор строк, что и T.Для соединений вида LEFT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM T UNION ALL SELECT * FROM X1; Для соединений вида RIGHT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM T UNION ALL SELECT * FROM X2; Для соединений вида FULL OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM T UNION ALL SELECT * FROM X1 UNION ALL SELECT * FROM X2; Для соединений вида UNION JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов

SELECT * FROM X1 UNION ALL SELECT * FROM X2; Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то результат операции совпадает с результатом выражения запросов

SELECT SLCC, SLT1, SLT2 FROM S; Во всех остальных случаях результат операции совпадает с S.

Логические выражения раздела HAVING

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

Логические выражения раздела HAVING

Порождаемые таблицы с горизонтальной связью (lateral_derived_table)

Во всех вариантах построения запросов, обсуждавшихся ранее в этой и предыдущей лекциях, оставалась действующей общая семантика выполнения запроса: на первом шаге вычисляется расширенное декартово произведение таблиц, специфицированных в списке раздела FROM. Это остается верным и для случаев порождаемых и соединенных таблиц - вычисление выражения запросов или выражения соединений соответственно производится как подшаг вычисления раздела FROM. Однако в SQL имеется один специальный случай спецификации ссылки на таблицу (table_reference), который, вообще говоря, изменяет семантику раздела FROM. В этом подразделе мы кратко рассмотрим специальный случай.

Как показывают синтаксические правила, приведенные в лекции 13, один из возможных способов спецификации ссылки на таблицу состоит в следующем:

table_reference ::= LATERAL (query_expression) [ [ AS ] correlation_name [ ( derived_column_list ) ] ]

Таблица, ссылка на которую специфицируется таким образом, называется порождаемой таблицей с горизонтальной связью1) (lateral_derived_table; для краткости будем называть такие таблицы LD-таблицами). Отличие LD-таблицы от обычной порождаемой таблицы состоит в том, что в выражении запросов LD-таблицы разрешается использовать ссылки на столбцы таблиц, специфицированных ранее в разделе FROM (т. е. таких таблиц, ссылки на которые содержатся в списке раздела FROM слева от ссылки на данную LD-таблицу).2) Покажем на примере, каким образом наличие в списке раздела FROM ссылки на LD-таблицу меняет семантику этого раздела.

Предположим, что раздел FROM имеет вид FROM T1, T2, причем таблица T2 является LD-таблицей. Обозначим соответствующее выражение запросов через Q2. Тогда таблица T, являющаяся результатом раздела FROM, будет вычисляться следующим образом. Последовательно, строка за строкой просматривается таблица T1. Пусть s1 является очередной строкой T1. Тогда в Q2 все ссылки на столбцы вида T1.ck, где ck - имя некоторого столбца T1, заменяются значением s1.ck, и вычисляется полученное таким образом выражение запросов. Обозначим результирующую таблицу этого выражения через T2s1. Обозначим через T12s1 таблицу, являющуюся результатом расширенного декартова произведения s1 CROSS JOIN T2s1. Таблица T получается путем объединения с сохранением дубликатов таблиц T12s1, полученных для всех строк s1 таблицы T1.

Видимо, наиболее важным (хотя и не единственным) частным случаем применения LD-таблицы является тот случай, когда в результате выполнения раздела FROM формируется соединение таблиц. Многие из формулировок запросов, приводившихся в этой лекции в качестве примеров, можно переформулировать с использованием данного механизма. Приведем лишь один простой пример.

SELECT EMP.EMP_NO FROM DEPT, LATERAL (SELECT EMP1_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG), LATERAL (SELECT EMP_NO FROM EMP WHERE EMP_SAL = EMP1_SAL AND EMP.EMP_NO <> DEPT.DEPT_MNG);

Пример 15.19. Найти номера сотрудников, не являющихся руководителями отделов и получающих заплату, размер которой равен размеру зарплаты какого-либо руководителя отдела (еще одна формулировка запроса из примера 14.10 из лекции 14). (html, txt)

Я не мог бы привести ни одного примера запроса, который было бы невозможно сформулировать без использования порождаемых таблиц с горизонтальной связью. Возникает впечатление (возможно, ошибочное), что эта конструкция была введена в язык по двум причинам - (a) из соображений общности и (b) по причине простоты реализации (в том смысле, что для реализации LD-таблиц не требуется изобретать какие-то новые технические приемы).

Порождаемые таблицы с горизонтальной связью (lateral_derived_table)

table_reference ::= LATERAL (query_expression) [ [ AS ] correlation_name [ ( derived_column_list ) ] ]

Таблица, ссылка на которую специфицируется таким образом, называется порождаемой таблицей с горизонтальной связью7) (lateral_derived_table; для краткости будем называть такие таблицы LD-таблицами). Отличие LD-таблицы от обычной порождаемой таблицы состоит в том, что в выражении запросов LD-таблицы разрешается использовать ссылки на столбцы таблиц, специфицированных ранее в разделе FROM (т. е. таких таблиц, ссылки на которые содержатся в списке раздела FROM слева от ссылки на данную LD-таблицу).8) Покажем на примере, каким образом наличие в списке раздела FROM ссылки на LD-таблицу меняет семантику этого раздела.

SELECT EMP.EMP_NO FROM DEPT, LATERAL (SELECT EMP1_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG), LATERAL (SELECT EMP_NO FROM EMP WHERE EMP_SAL = EMP1_SAL AND EMP.EMP_NO <> DEPT.DEPT_MNG);

Предикат between

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;

Пример 15.5. Найти номера отделов и минимальный и максимальный размер зарплаты сотрудников для отделов, в которых средний размер зарплаты сотрудников не меньше среднего размера зарплаты сотрудников во всей компании и не больше 30000 руб. (html, txt)

Еще раз приведем возможную формулировку этого запроса без использования разделов GROUP BY и HAVING (пример 15.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 EMP WHERE (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;

Пример 15.5.1.

(html, txt)

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

Предикат between

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

Пример 15.5.1.

Предикат distinct

SELECT DEPT.DEPT_NO FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM (SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL) FROM DEPT DEPT1, EMP EMP1, EMP EMP2 WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND DEPT1.DEPT_MNG = EMP2.EMP_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);

Пример 15.13. Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты. (html, txt)

Предикат distinct

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

Предикат exists

SELECT DEPT.DEPT_NO FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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));

Пример 15.9. Найти номера отделов, в которых средний размер зарплаты сотрудников равен максимальному размеру зарплаты сотрудников какого-либо другого отдела (другая формулировка для примера 15.7). (html, txt)

В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 15.9.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE 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));

Пример 15.9.1.

(html, txt)

Предикат exists

Пример 15.9.1.

Предикат in

SELECT DEPT.DEPT_NO FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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);

Пример 15.7. Найти номера отделов, в которых средний размер зарплаты сотрудников равен максимальному размеру зарплаты сотрудников какого-либо другого отдела. (html, txt)

Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 15.7.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE (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);

Пример 15.7.1.

(html, txt)

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

Предикат in

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

Пример 15.7.1.

Предикат like

SELECT EMP_NAME, COUNT(*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP_NAME HAVING COUNT(*) > 1 AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';

Пример 15.8. Во всех отделах найти имена и число сотрудников, у которых в данном отделе имеются однофамильцы и фамилии которых начинаются со строки символов, изображающей фамилию руководителя отдела. (html, txt)

Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING (пример 15.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) + 1 FROM EMP WHERE (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) || '%';

Пример 15.8.1.

(html, txt)

Предикат like

Пример 15.8.1.

Предикат null

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;

Пример 15.6. Найти номера и число сотрудников отделов, данные о руководителях которых не содержат номер отдела (конечно, в этом случае нас интересуют только те отделы, у которых имеется руководитель). (html, txt)

Как и в примере 15.4, условие раздела HAVING можно переместить в раздел WHERE и получить вторую формулировку (пример 15.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;

Пример 15.6.1.

(html, txt)

Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела GROUP BY оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора SELECT), что показывает пример 15.6.2:

SELECT DEPT.DEPT_NO, (SELECT COUNT(*) FROM EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO) FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND EMP.DEPT_NO IS NULL;

Пример 15.6.2.

(html, txt)

Предикат null

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;

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;

Пример 15.6.1.

SELECT DEPT.DEPT_NO, (SELECT COUNT(*) FROM EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO) FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND EMP.DEPT_NO IS NULL;

Пример 15.6.2.

Предикат unique

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL) FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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));

Пример 15.10. Найти номера отделов и средний размер зарплаты сотрудников для таких отделов, где средний размер зарплаты сотрудников отличается от среднего размера зарплаты всех других отделов. (html, txt)

Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS (пример 15.10.1):

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL) FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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));

Пример 15.10.1.

(html, txt)

Предикат unique

Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS (пример 15.10.1):

Пример 15.10.1.

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

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

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

Конечно, этот запрос можно сформулировать и без использования разделов GROUP BY и HAVING. Например, возможна следующая формулировка (пример 15.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.

(html, txt)

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

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

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

Очевидно, что и в этом случае возможна формулировка запроса без использования разделов GROUP BY и HAVING (пример 15.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;

Пример 15.2.1.

(html, txt)

Немного задержимся на этих примерах и обсудим, что означает различие в формулировках запросов. В соответствии с семантикой оператора 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 соответственно, и избежать выполнения лишних действий.

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

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

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

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

Пример 15.1.1.

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

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

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

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

Пример 15.2.1.

SELECT DEPT_NO FROM EMP WHERE

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

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

И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING (пример 15.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);

Пример 15.3.1.

(html, txt)

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;

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

Этот запрос иллюстрирует несколько интересных особенностей языка SQL. Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое - в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 15.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;

Пример 15.4.1.

(html, txt)

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

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

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

SELECT DEPT_NO FROM EMP WHERE

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

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

И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING (пример 15.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);

Пример 15.3.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 GROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAME HAVING DEPT.DEPT_MNG = EMP.EMP_NO;

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

Этот запрос иллюстрирует несколько интересных особенностей языка SQL. Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое - в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 15.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;

Пример 15.4.1.

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

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

Наконец, и этот запрос можно сформулировать без использования раздела GROUP BY за счет использования подзапросов в списке раздела SELECT (пример 15.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;

Пример 15.4.2.

(html, txt)

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

И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 15.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;

Пример 15.4.3.

(html, txt)

Здесь мы снова имеем замаскированную

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;

Пример 15.4.2.

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

И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 15.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;

Пример 15.4.3.

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

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG (CURRENT_DATE - EMP_BDATE)< SOME (SELECT AVG (CURRENT_DATE - EMP1.EMP_BDATE) FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO);

Пример 15.11. Найти номера отделов и средний возраст сотрудников для таких отделов, что найдется хотя бы один другой отдел, средний возраст сотрудников которого больше, чем в данном. (html, txt)

Напомним, что "ниладическая" функция CURRENT_DATE выдает текущую дату, и, следовательно, значением выражения CURRENT_DATE - EMP_BDATE является интервал, представляющий текущий возраст сотрудника. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикат предикатом EXISTS (пример 15.11.1):

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING EXISTS (SELECT EMP1.DEPT_NO FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) > AVG (CURRENT_DATE - EMP.EMP_BDATE));

Пример 15.11.1.

(html, txt)

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG (CURRENT_DATE - EMP_BDATE) <= ALL (SELECT AVG (CURRENT_DATE - EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO);

Пример 15.12. Найти номера отделов и средний возраст сотрудников для отделов с минимальным средним возрастом сотрудников. (html, txt)

Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS (пример 15.12.1):

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING NOT EXISTS (SELECT EMP1.DEPT_NO FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) < AVG (CURRENT_DATE - EMP.EMP_BDATE));

Пример 15.12.1.

(html, txt)

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

Пример 15.11.1.

Пример 15.12. Найти номера отделов и средний возраст сотрудников для отделов с минимальным средним возрастом сотрудников.

Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS (пример 15.12.1):

Пример 15.12.1.

SELECT DEPT_NO FROM EMP WHERE

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

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

Закрыть окно

SELECT DISTINCT DEPT_NO FROM EMP

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

Пример 15.1.1.

Закрыть окно

SELECT DISTINCT

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

WHERE DEPT_NO IS NOT

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

SELECT DEPT_NO FROM EMP WHERE

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

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

Закрыть окно

SELECT DISTINCT DEPT_NO FROM EMP

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

Пример 15.2.1.

Закрыть окно

SELECT DISTINCT

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

WHERE DEPT_NO IS NOT

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

SELECT DEPT_NO FROM EMP WHERE

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

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

Закрыть окно

SELECT DISTINCT DEPT_NO FROM EMP

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.3.1.

Закрыть окно

FROM EMP EMP1,

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

WHERE DEPT_NO IS NOT

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

FROM DEPT, EMP, EMP EMP1

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;

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

Закрыть окно

FROM DEPT, EMP, EMP EMP1

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;

Пример 15.4.1.

Закрыть окно

FROM DEPT, EMP, EMP

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;

FROM DEPT, EMP WHERE

Пример 15.4.2.

Закрыть окно

FROM DEPT,

DEPT_MNG IS NULL THEN NULL

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;

Пример 15.4.3.

Закрыть окно

DEPT_MNG IS NULL THEN NULL

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;

FROM DEPT, EMP, EMP

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;

FROM EMP WHERE DEPT_NO IS

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;

Закрыть окно

FROM EMP EMP1 WHERE

Пример 15.5.1.

Закрыть окно

SELECT DISTINCT DEPT_NO,

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 EMP
WHERE (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;

WHERE DEPT_NO IS NOT

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;

FROM DEPT, EMP EMP1, EMP

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;

Закрыть окно

FROM DEPT, EMP EMP1, EMP

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;

Пример 15.6.1.

Закрыть окно

FROM DEPT, EMP EMP1, EMP

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;

FROM DEPT, EMP WHERE

SELECT DEPT.DEPT_NO, (SELECT COUNT(*) FROM EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO) FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND EMP.DEPT_NO IS NULL;

Пример 15.6.2.

Закрыть окно

FROM DEPT,

SELECT DEPT.DEPT_NO, (SELECT COUNT(*)
FROM EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND
EMP.DEPT_NO IS NULL;

FROM DEPT, EMP EMP1, EMP

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;

DEPT_NO FROM DEPT, EMP WHERE

SELECT DEPT. DEPT_NO FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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);

Закрыть окно

FROM EMP, DEPT DEPT1 WHERE

Пример 15.7.1.

Закрыть окно

FROM EMP, DEPT

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (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);

FROM EMP, DEPT

SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING 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);

EMP_NAME FROM EMP EMP1 WHERE

SELECT EMP_NAME, COUNT(*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP_NAME HAVING COUNT(*) > 1 AND EMP.EMP_NAME LIKE (SELECT EMP1. EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';

Закрыть окно

EMP_NAME FROM EMP EMP1, DEPT

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) + 1 FROM EMP WHERE (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) || '%';

Пример 15.8.1.

Закрыть окно

AND EMP_NAME LIKE

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) + 1
FROM EMP
WHERE (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) || '%';

SELECT EMP_NAME,

SELECT EMP_NAME, COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
GROUP BY DEPT.DEPT_NO, EMP_NAME
HAVING COUNT(*) > 1
AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME
FROM EMP EMP1
WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';

DEPT_NO FROM DEPT, EMP WHERE

SELECT DEPT. DEPT_NO FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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));

Закрыть окно

DEPT_NO FROM DEPT WHERE EXISTS

SELECT DEPT. DEPT_NO FROM DEPT WHERE 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));

Пример 15.9.1.

Закрыть окно

WHERE EXISTS

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE 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));

FROM DEPT,

SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING 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));

DEPT_NO IS NOT NULL GROUP

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL) FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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));

Закрыть окно

DEPT_NO FROM EMP EMP1 WHERE

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL) FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING 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));

Пример 15.10.1.

Закрыть окно

HAVING NOT EXISTS

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING 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));

DEPT_NO IS NOT

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING 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));

FROM EMP WHERE DEPT_NO IS

Закрыть окно

FROM EMP WHERE DEPT_NO IS

Пример 15.11.1.

Закрыть окно

WHERE DEPT_NO IS NOT

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING EXISTS (SELECT EMP1.DEPT_NO
FROM EMP EMP1
WHERE EMP1.DEPT_NO IS NOT NULL
GROUP BY EMP1.DEPT_NO
HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) >
AVG (CURRENT_DATE - EMP.EMP_BDATE));

WHERE DEPT_NO IS NOT

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE - EMP_BDATE)< SOME
(SELECT AVG (CURRENT_DATE - EMP1.EMP_BDATE)
FROM EMP EMP1
WHERE EMP1.DEPT_NO IS NOT NULL
GROUP BY EMP1.DEPT_NO);

FROM EMP WHERE DEPT_NO IS

Закрыть окно

FROM EMP WHERE DEPT_NO IS

Пример 15.12.1.

Закрыть окно

WHERE DEPT_NO IS NOT

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
FROM EMP EMP1
WHERE EMP1.DEPT_NO IS NOT NULL
GROUP BY EMP1.DEPT_NO
HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) <
AVG (CURRENT_DATE - EMP.EMP_BDATE));

WHERE DEPT_NO IS NOT

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE - EMP_BDATE) <= ALL
(SELECT AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO);

DEPT_NO FROM DEPT, EMP EMP1,

SELECT DEPT. DEPT_NO FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM (SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL) FROM DEPT DEPT1, EMP EMP1, EMP EMP2 WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND DEPT1.DEPT_MNG = EMP2.EMP_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);

Закрыть окно

FROM DEPT, EMP EMP1, EMP

SELECT DEPT.DEPT_NO
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND
DEPT.DEPT_MNG = EMP2.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE
HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM
(SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)
FROM DEPT DEPT1, EMP EMP1, EMP EMP2
WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND
DEPT1.DEPT_MNG = EMP2.EMP_NO AND
DEPT1.DEPT_NO <> DEPT.DEPT_NO
GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);

EMP_SAL FROM DEPT, EMP WHERE

Закрыть окно

EMP_NAME FROM DEPT, EMP WHERE

SELECT DEPT.DEPT_NO, EMP. EMP_NAME FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND DEPT.DEPT_NO = EMP.DEPT_NO AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 15.14.1.

Закрыть окно

FROM DEPT, EMP

SELECT DEPT.DEPT_NO, EMP.EMP_NAME
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO
AND DEPT.DEPT_NO = EMP.DEPT_NO
AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

EMP_SAL FROM DEPT, EMP WHERE

Пример 15.14.2.

Закрыть окно

WHERE DEPT_NO IS NOT

WITH MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) AS
(SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL
FROM DEPT, EMP
WHERE DEPT.MNG_NO = EMP.EMP_NO),
MAX_DEPT_SAL (MAX_SAL, DEPT_NO) AS
(SELECT MAX (EMP_SAL), DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO)
SELECT DEPT_NO_1, MNG_NAME
FROM MNG
WHERE DEPT_NO_1 = DEPT_NO_2
AND MNG_SAL = (SELECT MAX_SAL
FROM MAX_DEPT_SAL
WHERE MAX_DEPT_SAL.DEPT_NO = DEPT_NO_1);

FROM DEPT,

SELECT MNG.DEPT_NO, MNG.MNG_NAME
FROM (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO)
AS MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL)
WHERE DEPT_NO_1 = DEPT_NO_2
AND MNG_SAL = (SELECT MAX (EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT_NO_1);

FROM EMP WHERE DEPT_NO IS

SELECT SUM (TOTAL_EMP), MAX_SAL FROM (SELECT MAX (EMP_SAL), COUNT (*) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO ) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) GROUP BY MAX_SAL;

Пример 15.15. Найти общее число сотрудников и максимальный размер зарплаты в отделах с одинаковым максимальным размером зарплаты.

Закрыть окно

FROM EMP WHERE DEPT_NO IS

WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS (SELECT MAX (EMP_SAL), COUNT (*) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO) SELECT SUM (TOTAL_EMP), MAX_SAL FROM DEPT_MAX_SAL GROUP BY MAX_SAL;

Пример 15.15.1.

Закрыть окно

WHERE DEPT_NO IS NOT

WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS
(SELECT MAX (EMP_SAL), COUNT (*)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO)
SELECT SUM (TOTAL_EMP), MAX_SAL
FROM DEPT_MAX_SAL
GROUP BY MAX_SAL;

WHERE DEPT_NO IS NOT

SELECT SUM (TOTAL_EMP), MAX_SAL
FROM (SELECT MAX (EMP_SAL), COUNT (*)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO ) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP)
GROUP BY MAX_SAL;

PRO_NO FROM PRO) AS PRO1

SELECT COUNT (*), PRO_EDATE, AVG_SAL FROM (SELECT PRO_EDATE, AVG (EMP_SAL) FROM (SELECT PRO_SDATE + PRO_DURAT, PRO_NO FROM PRO) AS PRO1 (PRO_EDATE, PRO_NO), EMP WHERE PRO1.PRO_NO = EMP.PRO_NO GROUP BY PRO1.PRO_NO ) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL) GROUP BY PRO_EDATE, AVG_SAL;

Пример 15.16. Найти число проектов, дату их завершения и средний размер зарплаты сотрудников, участвующих в проекте, для проектов с одной и той же датой завершения и одним и тем же средним размером зарплаты сотрудников, участвующих в проекте.

Закрыть окно

PRO_NO FROM PRO) SELECT COUNT

WITH PRO1 (PRO_EDATE, PRO_NO) AS (SELECT PRO_SDATE + PRO_DURAT, PRO_NO FROM PRO) SELECT COUNT (*), PRO_EDATE, AVG_SAL FROM (SELECT PRO_EDATE, AVG (EMP_SAL) FROM PRO1, EMP WHERE PRO1.PRO_NO = EMP.PRO_NO GROUP BY PRO1.PRO_NO) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;

Пример 15.16.1.

Закрыть окно

SELECT PRO_EDATE, AVG

WITH PRO1 (PRO_EDATE, PRO_NO) AS
(SELECT PRO_SDATE + PRO_DURAT, PRO_NO
FROM PRO)
SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM ( SELECT PRO_EDATE, AVG (EMP_SAL)
FROM PRO1, EMP
WHERE PRO1.PRO_NO = EMP.PRO_NO
GROUP BY PRO1.PRO_NO) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;

FROM PRO) AS PRO1

SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
FROM (SELECT PRO_SDATE + PRO_DURAT, PRO_NO
FROM PRO) AS PRO1 (PRO_EDATE, PRO_NO), EMP
WHERE PRO1.PRO_NO = EMP.PRO_NO
GROUP BY PRO1.PRO_NO ) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;

DEPT NATURAL INNER JOIN EMP

SELECT DEPT.DEPT_NO, EMP1.EMP_NAME, COUNT(*), MIN(EMP2.EMP_SAL), MAX(EMP2.EMP_SAL), AVG(EMP2.EMP_SAL) FROM ( DEPT NATURAL INNER JOIN EMP AS EMP2) INNER JOIN EMP AS EMP1 ON DEPT.DEPT_MNG = EMP1.EMP_NO GROUP BY DEPT.DEPT_NO, EMP1.EMP_NAME;

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

Закрыть окно

EMP AS EMP1 INNER JOIN

SELECT EMP1.EMP_NO, EMP2.EMP_NAME FROM ( EMP AS EMP1 INNER JOIN DEPT ON EMP1.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_SAL > 30000.00) INNER JOIN EMP AS EMP2 ON DEPT.MNG = EMP2.EMP_NO;

Пример 15.17.1.

Закрыть окно

EMP AS EMP1 INNER JOIN

SELECT EMP1.EMP_NO, EMP2.EMP_NAME
FROM ( EMP AS EMP1 INNER JOIN DEPT
ON EMP1.DEPT_NO = DEPT.DEPT_NO AND
EMP1.EMP_SAL > 30000.00)
INNER JOIN EMP AS EMP2 ON DEPT.MNG = EMP2.EMP_NO;

DEPT NATURAL INNER JOIN EMP

SELECT DEPT.DEPT_NO, EMP1.EMP_NAME, COUNT(*), MIN(EMP2.EMP_SAL),
MAX(EMP2.EMP_SAL), AVG(EMP2.EMP_SAL)
FROM ( DEPT NATURAL INNER JOIN EMP AS EMP2)
INNER JOIN EMP AS EMP1 ON DEPT.DEPT_MNG = EMP1.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP1.EMP_NAME;

EMP AS EMP1 NATURAL INNER

SELECT EMP1.EMP_NO, EMP2.EMP_NAME FROM ( EMP AS EMP1 NATURAL INNER JOIN DEPT) INNER JOIN EMP AS EMP2 ON DEPT.DEPT_MNG = EMP2.EMP_NO WHERE EMP1.EMP_SAL > 30000.00;

Пример 15.18. Найти номера служащих и имена их начальников отделов для служащих, размер зарплаты которых больше 30000 руб.

Закрыть окно

EMP AS EMP1 NATURAL INNER

SELECT EMP1.EMP_NO, EMP2.EMP_NAME
FROM ( EMP AS EMP1 NATURAL INNER JOIN DEPT)
INNER JOIN EMP AS EMP2 ON DEPT.DEPT_MNG = EMP2.EMP_NO
WHERE EMP1.EMP_SAL > 30000.00;

SELECT EMP1_SAL FROM EMP EMP1

SELECT EMP.EMP_NO FROM DEPT, LATERAL ( SELECT EMP1_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG), LATERAL (SELECT EMP_NO FROM EMP WHERE EMP_SAL = EMP1_SAL AND EMP.EMP_NO <> DEPT.DEPT_MNG);

Закрыть окно

FROM DEPT,

SELECT EMP.EMP_NO
FROM DEPT, LATERAL
(SELECT EMP1_SAL
FROM EMP EMP1
WHERE EMP1.EMP_NO = DEPT.DEPT_MNG),
LATERAL
(SELECT EMP_NO
FROM EMP
WHERE EMP_SAL = EMP1_SAL AND
EMP.EMP_NO <> DEPT.DEPT_MNG);

Примеры соединений разного вида

Основное назначение приводимых ниже примеров состоит не в том, чтобы продемонстрировать практическую значимость разнообразных соединений, а лишь в том, чтобы помочь в них разобраться.1) Поэтому мы будем использовать упрощенные и формальные таблицы и показывать заголовки и тела результирующих таблиц.

Итак, пусть имеются таблицы table1 (a1, a2, c1, c2) и table2 (b1, b2, c1, c2) со следующими телами:

table1

a1	a2	c1	c2
1	1	1	1
1	1	2	3
1	1	2	3
2	3	4	NULL
3	NULL	NULL	5

table2

b1	b2	c1	c2
1	1	1	1
1	2	2	3
3	3	2	3
4	4	4	4
3	NULL	NULL	5
3	NULL	NULL	5

Обозначим через JR таблицу, являющуюся результатом соединения. Тогда для операции table1 INNER JOIN table2 ON a1=b1 AND a2<b2 (внутреннее соединение по условию) тело JR будет следующим:

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3

Строки-дубликаты появились в JR, поскольку в первом операнде присутствовали строки-дубликаты, удовлетворяющие условию соединения.

Результатом операции table1 INNER JOIN table2 USING (c2) (внутреннее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица.

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL

Результат операции table1 INNER JOIN table2 USING (c1,c2):

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3

Такой же результат будет получен при выполнении операции table1 NATURAL INNER JOIN table2 (естественное внутреннее соединение). Более того, для произвольных таблиц table1 и table2 результаты операций table1 INNER JOIN table2 USING (с1, c2, ...cn) и table1 INNER NATURAL JOIN table2 совпадают в том и только в том случае, когда список имен столбцов с1, c2, ...cn включает все имена столбцов, общие для таблиц table1 и table2.

Результатом операции table1 LEFT OUTER JOIN table2 ON a1=b1 AND a2<b2 (левое внешнее соединение по условию>) будет следующая таблица:

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3
2	3	4	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL	NULL	NULL

Примеры соединений разного вида

Основное назначение приводимых ниже примеров состоит не в том, чтобы продемонстрировать практическую значимость разнообразных соединений, а лишь в том, чтобы помочь в них разобраться.6) Поэтому мы будем использовать упрощенные и формальные таблицы и показывать заголовки и тела результирующих таблиц.

Итак, пусть имеются таблицы table1 (a1, a2, c1, c2) и table2 (b1, b2, c1, c2) со следующими телами:

table1

a1	a2	c1	c2
1	1	1	1
1	1	2	3
1	1	2	3
2	3	4	NULL
3	NULL	NULL	5

table2

b1	b2	c1	c2
1	1	1	1
1	2	2	3
3	3	2	3
4	4	4	4
3	NULL	NULL	5
3	NULL	NULL	5

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL

Результат операции table1 INNER JOIN table2 USING (c1,c2):

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3
2	3	4	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL	NULL	NULL

в результате левого внешнего соединения

Как видно, в результате левого внешнего соединения сохраняются все данные первого (левого) операнда.
Результатом операции table1 RIGHT OUTER JOIN table2 ON a1=b1 AND a2<b2 (правое внешнее соединение по условию) будет следующая таблица:
JR

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3
NULL	NULL	NULL	NULL	1	1	1	1
NULL	NULL	NULL	NULL	3	3	2	3
NULL	NULL	NULL	NULL	4	4	4	4
NULL	NULL	NULL	NULL	3	NULL	NULL	5
NULL	NULL	NULL	NULL	3	NULL	NULL	5

Как видно, в результате правого внешнего соединения сохраняются все данные второго (правого) операнда.
Результатом операции table1 FULL OUTER JOIN table2 ON a1=b1 AND a2<b2 (полное внешнее соединение по условию) будет следующая таблица:
JR

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3
2	3	4	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	1	1	1	1
NULL	NULL	NULL	NULL	3	3	2	3
NULL	NULL	NULL	NULL	4	4	4	4
NULL	NULL	NULL	NULL	3	NULL	NULL	5
NULL	NULL	NULL	NULL	3	NULL	NULL	5

Как видно, в результате полного внешнего соединения сохраняются данные обоих операндов. Кстати, полное внешнее соединение иногда называют еще симметричным внешним соединением. Очевидно, что все операции внутреннего соединения и операция полного внешнего соединения коммутативны, а операции левого и правого соединения коммутативными не являются.
Результатом операции table1 LEFT OUTER JOIN table2 USING (c2) (левое внешнее соединение по совпадению значений указанных одноименных столбцов>) будет следующая таблица:
JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL
2	3	4	NULL	NULL	NULL	NULL

Результатом операции table1 RIGHT OUTER JOIN table2 USING (c2) (правое внешнее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица:
JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL
NULL	NULL	NULL	4	4	4	4

в результате левого внешнего соединения

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3
NULL	NULL	NULL	NULL	1	1	1	1
NULL	NULL	NULL	NULL	3	3	2	3
NULL	NULL	NULL	NULL	4	4	4	4
NULL	NULL	NULL	NULL	3	NULL	NULL	5
NULL	NULL	NULL	NULL	3	NULL	NULL	5

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	1	2	2	3
1	1	2	3	1	2	2	3
1	1	2	3	1	2	2	3
2	3	4	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	1	1	1	1
NULL	NULL	NULL	NULL	3	3	2	3
NULL	NULL	NULL	NULL	4	4	4	4
NULL	NULL	NULL	NULL	3	NULL	NULL	5
NULL	NULL	NULL	NULL	3	NULL	NULL	5

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL
2	3	4	NULL	NULL	NULL	NULL

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL
NULL	NULL	NULL	4	4	4	4

Результатом операции table1 FULL OUTER

Результатом операции table1 FULL OUTER JOIN table2 USING (c2) (полное внешнее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица:
JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL
2	3	4	NULL	NULL	NULL	NULL
NULL	NULL	NULL	4	4	4	4

Результатом операции table1 LEFT OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL LEFT OUTER JOIN table2 - естественное левое внешнее соединение) будет следующая таблица:
JR

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3
2	3	4	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL

Результатом операции table1 RIGHT OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL RIGHT OUTER JOIN table2 - естественное правое внешнее соединение) будет следующая таблица:
JR

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3
NULL	NULL	4	4	4	4
NULL	NULL	NULL	5	3	NULL
NULL	NULL	NULL	5	3	NULL

Результатом операции table1 FULL OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL FULL OUTER JOIN table2 - естественное полное внешнее соединение) будет следующая таблица:
JR

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3
2	3	4	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL
NULL	NULL	4	4	4	4
NULL	NULL	NULL	5	3	NULL
NULL	NULL	NULL	5	3	NULL

Наконец, результатом операции table1 UNION JOIN table2 (соединение объединением) будет следующая таблица:
JR

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	NULL	NULL	NULL	NULL
1	1	2	3	NULL	NULL	NULL	NULL
1	1	2	3	NULL	NULL	NULL	NULL
2	3	4	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	1	1	2	3
NULL	NULL	NULL	NULL	1	2	2	3
NULL	NULL	NULL	NULL	3	3	2	3
NULL	NULL	NULL	NULL	4	4	4	4
NULL	NULL	NULL	NULL	3	NULL	NULL	5
NULL	NULL	NULL	NULL	3	NULL	NULL	5

Результатом операции table1 FULL OUTER

a1	a2	table1.c1	c2	b1	b2	table2.c1
1	1	1	1	1	1	1
1	1	2	3	1	2	2
1	1	2	3	3	3	2
1	1	2	3	1	2	2
1	1	2	3	3	3	2
3	NULL	NULL	5	3	NULL	NULL
3	NULL	NULL	5	3	NULL	NULL
2	3	4	NULL	NULL	NULL	NULL
NULL	NULL	NULL	4	4	4	4

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3
2	3	4	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3
NULL	NULL	4	4	4	4
NULL	NULL	NULL	5	3	NULL
NULL	NULL	NULL	5	3	NULL

a1	a2	c1	c2	b1	b2
1	1	1	1	1	1
1	1	2	3	1	2
1	1	2	3	3	3
1	1	2	3	1	2
1	1	2	3	3	3
2	3	4	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL
NULL	NULL	4	4	4	4
NULL	NULL	NULL	5	3	NULL
NULL	NULL	NULL	5	3	NULL

Наконец, результатом операции table1 UNION JOIN table2 (соединение объединением) будет следующая таблица:
JR

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
1	1	1	1	NULL	NULL	NULL	NULL
1	1	2	3	NULL	NULL	NULL	NULL
1	1	2	3	NULL	NULL	NULL	NULL
2	3	4	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	5	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	1	1	2	3
NULL	NULL	NULL	NULL	1	2	2	3
NULL	NULL	NULL	NULL	3	3	2	3
NULL	NULL	NULL	NULL	4	4	4	4
NULL	NULL	NULL	NULL	3	NULL	NULL	5
NULL	NULL	NULL	NULL	3	NULL	NULL	5

Примеры запросов с использованием соединенных таблиц

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

SELECT DEPT.DEPT_NO, EMP1.EMP_NAME, COUNT(*), MIN(EMP2.EMP_SAL), MAX(EMP2.EMP_SAL), AVG(EMP2.EMP_SAL) FROM (DEPT NATURAL INNER JOIN EMP AS EMP2) INNER JOIN EMP AS EMP1 ON DEPT.DEPT_MNG = EMP1.EMP_NO GROUP BY DEPT.DEPT_NO, EMP1.EMP_NAME;

SELECT EMP1.EMP_NO, EMP2.EMP_NAME FROM (EMP AS EMP1 NATURAL INNER JOIN DEPT) INNER JOIN EMP AS EMP2 ON DEPT.DEPT_MNG = EMP2.EMP_NO WHERE EMP1.EMP_SAL > 30000.00;

Пример 15.18. Найти номера служащих и имена их начальников отделов для служащих, размер зарплаты которых больше 30000 руб. (html, txt)

Можно обойтись вообще без раздела WHERE, если пожертвовать "естественностью" первого соединения (пример 15.17.1):

SELECT EMP1.EMP_NO, EMP2.EMP_NAME FROM (EMP AS EMP1 INNER JOIN DEPT ON EMP1.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_SAL > 30000.00) INNER JOIN EMP AS EMP2 ON DEPT.MNG = EMP2.EMP_NO;

Пример 15.17.1.

(html, txt)

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

Примеры запросов с использованием соединенных таблиц

SELECT DEPT.DEPT_NO, EMP1.EMP_NAME, COUNT(*), MIN(EMP2.EMP_SAL), MAX(EMP2.EMP_SAL), AVG(EMP2.EMP_SAL) FROM (DEPT NATURAL INNER JOIN EMP AS EMP2) INNER JOIN EMP AS EMP1 ON DEPT.DEPT_MNG = EMP1.EMP_NO GROUP BY DEPT.DEPT_NO, EMP1.EMP_NAME;

SELECT EMP1.EMP_NO, EMP2.EMP_NAME FROM (EMP AS EMP1 NATURAL INNER JOIN DEPT) INNER JOIN EMP AS EMP2 ON DEPT.DEPT_MNG = EMP2.EMP_NO WHERE EMP1.EMP_SAL > 30000.00;

Можно обойтись вообще без раздела WHERE, если пожертвовать "естественностью" первого соединения (пример 15.17.1):

SELECT EMP1.EMP_NO, EMP2.EMP_NAME FROM (EMP AS EMP1 INNER JOIN DEPT ON EMP1.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_SAL > 30000.00) INNER JOIN EMP AS EMP2 ON DEPT.MNG = EMP2.EMP_NO;

Пример 15.17.1.

Результаты запросов и агрегатные функции

Об использовании агрегатных функций в разделах HAVING и SELECT оператора выборки упоминалось в разделе "Общие синтаксические правила построения скалярных выражений" лекции 13. В данном подразделе уместно повторить и уточнить этот материал.

Агрегатные функции можно разумным образом использовать в списке выборки (при построении выражений, являющихся элементами выборки) и в логическом выражении раздела HAVING (вернее, в выражениях, входящих в простые условия). Рассмотрим разные случаи применения агрегатных функций в списке выборки в зависимости от вида табличного выражения.

Если результат табличного выражения R не является сгруппированной таблицей (т. е. в табличном выражении отсутствуют разделы GROUP BY и HAVING), то появление в списке выборки хотя бы одного вызова агрегатной функции от (мульти) множества строк R приводит к тому, что R неявно рассматривается как сгруппированная таблица, состоящая из одной (или нуля, если R пусто) групп с отсутствующими столбцами группирования. Поэтому в данном случае в выражениях списка выборки не допускается прямое использование имен столбцов R: все они должны находиться внутри спецификаций вызова агрегатных функций. Результатом запроса является таблица, состоящая не более чем из одной строки, значения столбцов которой получены путем применения агрегатных функций к R.

Аналогично обстоит дело в том случае, когда R представляет собой сгруппированную таблицу, но табличное выражение не содержит раздела GROUP BY (и, следовательно, содержит раздел HAVING). В этом случае считается, что результат табличного выражения явно объявлен сгруппированной таблицей, состоящей из одной группы, и результат запроса можно формировать только путем применения агрегатных функций к данной группе строк. Опять результатом запроса является таблица, состоящая не более чем из одной строки, значения столбцов которой получены путем применения агрегатных функций к R.

Наконец, рассмотрим случай, когда R представляет собой "настоящую" сгруппированную таблицу, т. е. табличное выражение содержит раздел GROUP BY, и, следовательно, определен по крайней мере один столбец группирования (т. е. имеется хотя бы один такой столбец, что для любой группы его значения одинаковы во всех строках группы). В этом случае правила формирования списка выборки полностью соответствуют правилам формирования условия выборки раздела HAVING. Другими словами, в выражениях, являющихся элементами списка выборки, допускается прямое использование имен столбцов группирования, а спецификации остальных столбцов R могут появляться только внутри спецификаций агрегатных функций. Результатом запроса является таблица, число строк в которой равно числу групп в R. Значения столбцов каждой строки формируются на основе значений столбцов группирования и вызовов агрегатных функций для соответствующей группы.

Результаты запросов и агрегатные функции

Семантика агрегатных функций

Агрегатные функции (в стандарте SQL они называются функциями над множествами)1) определяются следующими синтаксическими правилами:

<set_function_specification> ::= COUNT(*) | set_function_type ([DISTINCT | ALL ] value_expression) | GROUPING (column_reference) <set_function_type> ::= { AVG | MAX | MIN | SUM | EVERY | ANY | SOME | COUNT }

Как видно из этих правил, в стандарте SQL:1999 определены пять стандартных агрегатных функций: COUNT - число строк или значений, MAX - максимальное значение, MIN - минимальное значение, SUM - суммарное значение и AVG - среднее значение, а также две "кванторные" функции EVERY и SOME (ANY). В последних двух случаях выражение должно иметь булевский тип. Обсуждение функции GROUPING мы отложим до следующей лекции.

Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного мультимножества строк. Таким мультимножеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или (в вырожденных случаях) вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т. е. требуемый семантикой) порядок вычислений состоит в следующем. На основании параметров агрегатной функции из заданного мультимножества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значением функции COUNT для него является 0, значением функции SOME - false, значением функции ALL - true, а значением всех остальных функций - NULL.

Пусть T обозначает тип значений из этого списка (вернее, "наименьший общий" тип, см. раздел "Скалярные выражения" лекции 13). Типы значений агрегатных функций определяются следующими правилами.

Результат вычисления функции COUNT - это точное число с точностью и шкалой, которые определяются в реализации.Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк. Если T представляет собой тип точных чисел, то и типом результата функции является тип точных чисел с определяемыми в реализации точностью и шкалой.Если T представляет собой тип приблизительных чисел, то и типом результата функции является тип приблизительных чисел с определяемыми в реализации точностью.

Семантика агрегатных функций

Агрегатные функции (в стандарте SQL они называются функциями над множествами)2) определяются следующими синтаксическими правилами:

Первая функция принимает значение true

Для функций EVERY и SOME T является булевским типом. Первая функция принимает значение true в том и только в том случае, когда вычисление выражения-аргумента дает значение true для каждой строки из заданного набора строк; false - в том и только в том случае, когда значение выражения-аргумента есть false хотя бы для одной строки из заданного набора строк и uknown - во всех остальных случаях.Функция SOME принимает значение false в том и только в том случае, когда значение выражения-аргумента есть false для каждой строки из заданного набора строк; true - в том и только в том случае, когда значение выражения-аргумента есть true хотя бы для одной строки из заданного набора строк; uknown - во всех остальных случаях.

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном мультимножестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.2)

Если "арифметическая" (AVG, MAX, MIN, SUM, COUNT) агрегатная функция специфицирована с ключевым словом DISTINCT, то множество значений, на котором она вычисляется, строится из значений указанного выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и в нем устраняются значения-дубликаты (т. е. образуется множество). После этого вычисляется указанная функция.

Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то мультимножество значений формируется из значений выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и производится вычисление агрегатной функции.

Первая функция принимает значение true

Для функций EVERY и SOME T является булевским типом. Первая функция принимает значение true в том и только в том случае, когда вычисление выражения-аргумента дает значение true для каждой строки из заданного набора строк; false - в том и только в том случае, когда значение выражения-аргумента есть false хотя бы для одной строки из заданного набора строк и uknown - во всех остальных случаях.Функция SOME принимает значение false в том и только в том случае, когда значение выражения-аргумента есть false для каждой строки из заданного набора строк; true - в том и только в том случае, когда значение выражения-аргумента есть true хотя бы для одной строки из заданного набора строк; uknown - во всех остальных случаях.

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном мультимножестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.3)

Если "арифметическая" (AVG, MAX, MIN, SUM, COUNT) агрегатная функция специфицирована с ключевым словом DISTINCT, то множество значений, на котором она вычисляется, строится из значений указанного выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и в нем устраняются значения-дубликаты (т. е. образуется множество). После этого вычисляется указанная функция.

Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то мультимножество значений формируется из значений выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и производится вычисление агрегатной функции.

Случаи, в которых без порождаемых таблиц обойтись невозможно

На самом деле, пример 15.14 демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе FROM. Но в некоторых случаях без подобных конструкций просто невозможно обойтись. Вот простой пример.

SELECT SUM (TOTAL_EMP), MAX_SAL FROM (SELECT MAX (EMP_SAL), COUNT (*) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO ) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) GROUP BY MAX_SAL;

Пример 15.15. Найти общее число сотрудников и максимальный размер зарплаты в отделах с одинаковым максимальным размером зарплаты. (html, txt)

И в этом случае выражение запросов, содержащееся в разделе FROM, можно перенести в раздел WITH (пример 15.15.1):

WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS (SELECT MAX (EMP_SAL), COUNT (*) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO) SELECT SUM (TOTAL_EMP), MAX_SAL FROM DEPT_MAX_SAL GROUP BY MAX_SAL;

Пример 15.15.1.

(html, txt)

Здесь мы не можем обойтись "одноуровневой" конструкцией запроса, поскольку требуется двойная группировка, причем вторая группировка должна быть получена в соответствии с результатами первой. Еще один пример.

Заметим, что выражение запросов на третьей и четвертой строках примера необходимо только по той причине, что нам требуется группировка по дате окончания проектов, соответствующий столбец в таблице PRO отсутствует, а в списке группировки можно использовать только имена столбцов. Для упрощения вида формулировки это выражение разумно вынести в раздел WITH (пример 15.16.1):

GROUP BY PRO_EDATE, AVG_SAL;

Пример 15.16.1.

(html, txt)

Случаи, в которых без порождаемых таблиц обойтись невозможно

SELECT SUM (TOTAL_EMP), MAX_SAL FROM (SELECT MAX (EMP_SAL), COUNT (*) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO ) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) GROUP BY MAX_SAL;

И в этом случае выражение запросов, содержащееся в разделе FROM, можно перенести в раздел WITH (пример 15.15.1):

WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS (SELECT MAX (EMP_SAL), COUNT (*) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO) SELECT SUM (TOTAL_EMP), MAX_SAL FROM DEPT_MAX_SAL GROUP BY MAX_SAL;

Пример 15.15.1.

GROUP BY PRO_EDATE, AVG_SAL;

Пример 15.16.1.

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

В примерах предыдущей и данной лекций присутствовало много запросов с соединениями двух или более таблиц. Условия соединения задавались предикатами сравнения столбцов таблиц, специфицированных в разделе FROM, и входили в состав логических выражений раздела WHERE (или, реже, раздела HAVING). Поскольку на практике требуются разные виды соединений, в стандарте SQL/92 появилась альтернативная возможность спецификации соединений - соединенная таблица (joined table). Соответствующая конструкция может использоваться в разделе FROM выражения запросов и фактически позволяет строить выражения соединений таблиц. Синтаксические правила построения таких выражений выглядят следующим образом:

joined_table ::= cross_join | qualified_join | natural_join | union_join cross_join ::= table_reference CROSS JOIN table_primary qualified_join ::= table_reference [ join_type ] JOIN table_primary join_specification natural_join ::= table_reference NATURAL [ join_type ] JOIN table_primary union_join ::= table_reference UNION JOIN table_primary join_type ::= INNER | { LEFT | RIGHT | FULL } [ OUTER ] join_specification ::= ON conditional_expression | USING (column_comma_list)

Напомним, что синтаксические правила для table_reference и table_primary были показаны в лекции 13.

Как показывает сводка синтаксических правил, в SQL поддерживается много вариантов соединений. Чтобы объяснить особенности разных видов соединений на неформальном уровне, требуется очень большой объем текста с большим числом повторений. Поэтому сначала мы приведем достаточно формальное описание порядка определения заголовка и тела результирующей таблицы для всех разновидностей соединений. Фактически это описание напрямую позаимствовано из стандарта SQL:1999 с некоторыми незначительными упрощениями. Затем мы представим ряд иллюстрирующих примеров.

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

Напомним, что синтаксические правила для table_reference и table_primary были показаны в лекции 13.

Ссылки на порождаемые таблицы в разделе FROM

В этом разделе мы приведем несколько примеров запросов, в разделе FROM которых содержатся выражения запросов (ссылки на порождаемые таблицы, см. раздел "Общие синтаксические правила построения скалярных выражений" лекции 13).

Ссылки на порождаемые таблицы в разделе FROM

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

Но имеются два важных частных случая соединений, которые выражаются с помощью традиционных средств SQL излишне громоздко,- это естественные и внешние соединения. При наличии возможности определения внешних ключей таблицы кажется достаточно странной потребность всякий раз явно указывать в запросах условие естественного соединения. Например, во многих примерах запросов в лекции 14 присутствует условие соединения EMP.DEPT_NO = DEPT.DEPT_NO в тех случаях, когда в действительности нам требовался результат операции EMP NATURAL JOIN DEPT.

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

Пусть имеются отношения r1 и r2, совместимые относительно операции взятия расширенного декартова произведения. Пусть s является результатом операции r1 LEFT OUTER JOIN r2 WHERE comp (левое внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1

Br1 и tr2

Br2. Тогда tr1 union tr2

Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1

Br1, для которого нет ни одного кортежа tr2

r2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null

Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными1).

Пусть s является результатом операции r1 RIGHT OUTER JOIN r2 WHERE comp (правое внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1

Br1 и tr2

Br2. Тогда tr1 union tr2

Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr2

Br2, для которого нет ни одного такого кортежа tr1

Br1, что comp (tr1 union tr2) = true, то tr1null union tr2

Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.

Наконец, пусть s является результатом операции r1 FULL OUTER JOIN r2 WHERE comp (полное внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1

Br1 и tr2

Br2. Тогда tr1 union tr2

Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1

Br1, для которого нет ни одного кортежа tr2

Br2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null

Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными. Если имеется кортеж tr2

Br2, для которого нет ни одного кортежа tr1

Br1, такого, что comp (tr1 union tr2) = true, то tr1null union tr2

Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.

Понятно, что традиционными средствами SQL можно выразить все виды внешних соединений (например, с использованием переключателей), но такие запросы будут очень громоздкими. Компании-производители SQL-ориентированных СУБД пытались обеспечивать выразительные средства внешних соединений путем расширения системы обозначений для операций сравнения. Этот подход был не слишком удачным и не обеспечивал общего решения.

В стандарте языка SQL специфицирован отдельный специализированный подъязык для формирования выражений соединения таблиц. Такие выражения называются соединенными таблицами, и их можно использовать в качестве ссылок на таблицы в списке раздела FROM. Разработчики стандарта SQL не любят мельчить - в языке допускается 14 видов соединений:

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

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

Наконец, последняя тема этой лекции относится к еще одному типу ссылок на таблицу, допускаемых в разделе FROM: порождаемым таблицам с горизонтальной связью. Фактически порождаемая таблица с горизонтальной связью представляет собой выражение запросов, в котором может присутствовать корреляция со строками таблиц, специфицированных в списке раздела FROM слева от данной порождаемой таблицы с горизонтальной связью. Наличие порождаемых таблиц с горизонтальной связью требует некоторого уточнения семантики выполнения раздела FROM оператора SELECT. По нашему мнению, это средство является полностью избыточным, хотя и не вредным, поскольку его реализация не должна вызывать затруднений и/или снижать эффективность системы.

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

Br1 и tr2

Br2. Тогда tr1 union tr2

Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1

Br1, для которого нет ни одного кортежа tr2

r2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null

Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными1).

Br1 и tr2

Br2. Тогда tr1 union tr2

Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr2

Br2, для которого нет ни одного такого кортежа tr1

Br1, что comp (tr1 union tr2) = true, то tr1null union tr2

Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.

Br1 и tr2

Br2. Тогда tr1 union tr2

Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1

Br1, для которого нет ни одного кортежа tr2

Br2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null

Br2, для которого нет ни одного кортежа tr1

Br1, такого, что comp (tr1 union tr2) = true, то tr1null union tr2

Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.

В предыдущих двух лекциях мы

В предыдущих двух лекциях мы обсудили допускаемые в стандарте SQL виды ссылок на таблицы в разделе FROM оператора SELECT и подробно, с многочисленными примерами, рассмотрели возможные способы построения условных выражений раздела WHERE. Данную лекцию мы начинаем с анализа возможностей и целесообразности использования в запросах разделов GROUP BY и HAVING. Соответствующий раздел "Агрегатные функции, группировка и условия раздела HAVING" формально похож на раздел "Логические выражения раздела WHERE" лекции 14: обсуждаются виды предикатов, которые можно использовать в условных выражениях раздела HAVING, и приводятся иллюстрирующие примеры. Но в действительности мы преследуем большую цель: показать, что во многих случаях разделы GROUP BY и HAVING являются избыточными; запрос можно сформулировать более понятным образом без их использования. Применение разделов GROUP BY и HAVING оказывается действительно полезным, а иногда и необходимым, в тех случаях, когда в запросе присутствует несколько вызовов агрегатных функций на группах строк.
После обсуждения разделов GROUP BY и HAVING можно будет считать, что мы полностью рассмотрели базовые конструкции оператора выборки (раздел ORDER BY не заслуживает дополнительного обсуждения). Поэтому в разделах "Ссылки на порождаемые таблицы в разделе FROM" и "Более сложные конструкции оператора выборки" мы возвращаемся к отложенным в лекции 13 темам порождаемых таблиц, соединенных таблиц и порождаемых таблиц с горизонтальной связью.
В обычных порождаемых таблицах SQL нет ничего особенного. По всей видимости, возможность указывать в разделе FROM выражения запросов, а не только ссылки на базовые или представляемые таблицы, была введена в SQL на основе следующих естественных соображений. Результатом вычисления выражения запросов в SQL является таблица. Следовательно, в любой конструкции языка, где может присутствовать ссылка на таблицу SQL, следует допустить присутствие выражения запросов. Одновременное наличие возможностей определения представляемых таблиц, указания именованного выражения запросов в разделе WITH и указания выражения запросов порождаемой таблицы непосредственно в списке раздела FROM, очевидно, является избыточным.
Соединенные таблицы появились еще в стандарте SQL/92, и внедрение в стандарт SQL этой возможности было действительно обоснованным. В соответствии с традиционной общей семантикой оператора SELECT в нем вообще не предусматривалось явных средств для выражения потребности в соединении двух или более таблиц. Наличие возможности указывать несколько ссылок на таблицы в разделе FROM и спецификации произвольного логического выражения в разделе WHERE для ограничения расширенного декартова произведения этих таблиц позволяет выражать с помощью традиционных средств SQL соединение общего вида в смысле Кодда, и до поры до времени это считалось достаточным.

В предыдущих двух лекциях мы

в полной мере оценить мощность,

Думаю, что теперь читатели в состоянии в полной мере оценить мощность, разнообразие и избыточность средств языка SQL, предназначенных для формулировки запросов на выборку данных. Конечно, язык SQL (по крайней мере, ту часть SQL, которая обсуждается в этом курсе) нельзя считать языком программирования, но написание сложных запросов сродни программированию. И нельзя сказать, что SQL каким-либо образом дисциплинирует это "программирование". По всей видимости, в общем случае никто не может сказать, какая из формулировок одного и того же запроса является более правильной, это дело вкуса.
Зачастую десять студентов, одновременно формулирующих на SQL один и тот же запрос к одной и той же базе данных, выдают десять разных правильных решений. Один человек предпочитает формулировки запросов в классическом стиле, другой использует выражения запросов в разделе FROM, третий пытается сосредоточить все условия выборки в разделе HAVING. Люди с алгебраическими наклонностями предпочитают использовать выражения соединений. Приходилось встречать и формулировки со сложными вложенными подзапросами в списке выборки раздела SELECT.
Конечно, теоретически компилятор SQL должен быть в состоянии распознать все эквивалентные формулировки одного и того же запроса и выработать для всех них один и тот же наиболее эффективный план выполнения. Но чем больше разнообразие возможных формулировок, тем сложнее эта задача. Отсюда практический совет: не злоупотребляйте сложностью формулировки запроса. Полагайтесь на интуицию (и имеющиеся представления об особенностях используемой системы) и формулируйте запрос как можно проще.
И еще один практический совет. При формулировке запроса никогда не пользуйтесь имеющимися у вас данными о текущем состоянии базы данных, полагайтесь только на метаданные схемы базы данных. В противном случае вы сможете сформулировать запрос, выдающий в данный момент правильный результат, но этот запрос не будет эквивалентен никакому запросу, выдающему правильный ответ при любом состоянии базы данных.

в полной мере оценить мощность,

к компромиссу между реляционной терминологией

1)
Здесь мы прибегаем к компромиссу между реляционной терминологией и моделью данных SQL: конечно, в реляционной модели кортеж из неопределенных значений не может соответствовать заголовку отношения, поскольку NULL не является значением ни одного типа данных.

2)
Оба термина являются приемлемыми. Речь идет об агрегатных функциях, поскольку аргументом функции является агрегатное (составное) значение. Речь идет о функциях над множествами, поскольку аргументом функции является множество (в общем случае, мультимножество) значений. Но более правильно было бы говорить о групповых функциях, поскольку в большинстве случаев такие функции работают на значениях столбцов групп строк.

3)
Обратите внимание на то, что это еще один вид различения строк в SQL и еще одна скрытая интерпретация неопределенного значения. COUNT(*) работает так, как если бы выполнялось соотношение (NULL=NULL)

false. Тем самым, в SQL применяются все три возможных интерпретации NULL. При вычислении логических выражений полагается (NULL=NULL)

uknown; при определении строк-дубликатов неявно считается, что (NULL=NULL)

true; наконец, при вычислении агрегатной функции COUNT(*) неявно полагается, что (NULL=NULL)

false. Конечно, в такой "тройственности" нет ничего хорошего, но в контексте языка SQL приходится мириться с этими и другими негативными последствиями наличия неопределенных значений.

4)
Интересно, что для этого запроса возможна альтернативная формулировка с использованием операции CROSS JOIN: SELECT * FROM table1 CROSS JOIN table2. Может возникнуть естественный вопрос: зачем вводить специальную конструкцию для декартова произведения? По мнению автора, эта конструкция была введена, главным образом, для повышения уровня общности языка SQL. Кроме того, использование явного ключевого слова CROSS JOIN является подтверждением того, что пользователь действительно может получить декартово произведение, а не упустил по ошибке раздел WHERE.

5)
Для удобства читателей напомним, что по определению выражение COALESCE (V1, V2) эквивалентно следующему выражению с переключателем: CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END.

За очевидностью мы опустим примерCROSS

6)
За очевидностью мы опустим примерCROSS JOIN.

7)
Конечно, предлагаемый русский вариант термина lateral слишком громоздок. По всей видимости, если этот механизм войдет в практику пользователей SQL, можно будет использовать качестве термина что-то вроде литеральной порождаемой таблицы. Но здесь для нас главным является не обеспечение хорошей новой технологии, а обеспечение понимания материала.

8)
Тем самым ссылка на LD-таблицу не может быть первой в списке раздела FROM. Кстати может возникнуть естественный вопрос: почему разрешаются ссылки только на таблицы, находящиеся в списке раздела FROM только слева LD-таблицы? Стандарт отвечает на этот вопрос весьма просто и бесхитростно. Если разрешить использовать ссылки, находящиеся и слева, и справа от спецификации ссылки на LD-таблицу, то это может привести к зацикливанию при выполнении раздела FROM. Поэтому нужно было вбирать одно из направлений, и было выбрано направление слева направо.