Примеры запросов с использованием соединенных таблиц
Мы приведем всего пару примеров, чтобы проиллюстрировать формулировки запросов, в разделе FROMкоторых используются ссылки на соединенные таблицы, т. е. выражения соединений.
Пример 19.17. Для каждого отдела найти его номер, имя руководителя, число служащих, минимальный, максимальный и средний размеры зарплаты служащих (еще одна формулировка запроса изпримера 19.4).
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;
Пример 19.18. Найти номера служащих и имена их начальников отделов для служащих, размер зарплаты которых больше 30000 руб.
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, если пожертвовать «естественностью» первого соединения (пример 19.18.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;
Возможности соединенных таблиц открывают широкий простор для воображения, но не будем увлекаться и ограничимся приведенными простыми примерами.
19.4.2. Порождаемые таблицы с горизонтальной связью (lateral_derived_table)
Во всех вариантах построения запросов, обсуждавшихся ранее в этой и предыдущей лекциях, оставалась действующей общая семантика выполнения запроса: на первом шаге вычисляется расширенное декартово произведение таблиц, специфицированных в списке раздела FROM. Это остается верным и для случаев порождаемых и соединенных таблиц – вычисление выражения запросов или выражения соединений соответственно производится как подшаг вычисления разделаFROM. Однако в SQL имеется один специальный случай спецификации ссылки на таблицу (table_reference), который, вообще говоря, изменяет семантику разделаFROM. В этом подразделе мы кратко рассмотрим этот специальный случай.
Как показывают синтаксические правила, приведенные в лекции 17, один из возможных способов спецификации ссылки на таблицу состоит в следующем:
table_reference ::= LATERAL (query_expression)
[ [ AS ] correlation_name
[ ( derived_column_list ) ] ]
Таблица, ссылка на которую специфицируется таким образом, называется порождаемой таблицей с горизонтальной связью148)(lateral_derived_table; для краткости будем называть такие таблицы LD-таблицами). Отличие LD-таблицы от обычной порождаемой таблицы состоит в том, что в выражении запросов LD-таблицы разрешается использовать ссылки на столбцы таблиц, специфицированных ранее в разделеFROM(т. е. таких таблиц, ссылки на которые содержатся в списке разделаFROMслева от ссылки на данную LD-таблицу).149)Покажем на примере, каким образом наличие в списке раздела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.
148 Конечно, предлагаемый русский вариант термина lateral слишком громоздок. По всей видимости, если этот механизм войдет в практику пользователей SQL, можно будет использовать качестве термина что-то вроде латеральной порождаемой таблицы. Но здесь для нас главным является не предложение хорошей новой технологии, а обеспечение понимания материала.
149 Тем самым ссылка на LD-таблицу не может быть первой в списке раздела FROM. Кстати может возникнуть естественный вопрос: почему разрешаются ссылки только на таблицы, находящиеся в списке раздела FROM только слева LD-таблицы? Стандарт отвечает на этот вопрос весьма просто и бесхитростно. Если разрешить использовать ссылки, находящиеся и слева, и справа от спецификации ссылки на LD-таблицу, то это может привести к зацикливанию при выполнении раздела FROM. Поэтому нужно было вбирать одно из направлений, и было выбрано направление слева направо.
Видимо, наиболее важным (хотя и не единственным) частным случаем применения LD-таблицы является тот случай, когда в результате выполнения раздела FROMформируется соединение таблиц. Многие из формулировок запросов, приводившихся в этой лекции в качестве примеров, можно переформулировать с использованием данного механизма. Приведем лишь один простой пример.
Пример 19.19. Найти номера служащих, не являющихся руководителями отделов и получающих заплату, размер которой равен размеру зарплаты какого-либо руководителя отдела (еще одна формулировка запроса из примерапримера 18.10из лекции 18).
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);
Я не могу привести ни одного примера запроса, который было бы невозможно сформулировать без использования порождаемых таблиц с горизонтальной связью. Возникает впечатление (возможно, ошибочное), что эта конструкция была введена в язык по двум причинам – (a) из соображений общности и (b) по причине простоты реализации (в том смысле, что для реализации LD-таблиц не требуется изобретать какие-то новые технические приемы).