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

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

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

table1

table2

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

JR

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

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

JR

Результатом операции table1 INNER JOIN table2 USING (c1,c2) будет следующая таблица:

JR

Такой же результат будет получен при выполнении операции 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 (левое внешнее соединение по условию) будет следующая таблица:

JR

Как видно, в результате левого внешнего соединения сохраняются все данные первого (левого) операнда.

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

JR

Как видно, в результате правого внешнего соединения сохраняются все данные второго (правого) операнда.

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

JR

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

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

JR

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

JR

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

JR

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

JR

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

JR

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

JR

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

JR

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

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

Пример 15.17. Для каждого отдела найти его номер, имя руководителя, число сотрудников, минимальный, максимальный и средний размеры зарплаты сотрудников (еще одна формулировка запроса из примера 15.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;

Пример 15.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, если пожертвовать “естественностью” первого соединения (пример 15.17a):

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;

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

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

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

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

table_reference ::= LATERAL (query_expression)

[ [ AS ] correlation_name
[ ( derived_column_list ) ] ]

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

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

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

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

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-таблиц не требуется изобретать какие-то новые технические приемы).

Заключение

Теперь вы должны быть в состоянии в полной мере оценить мощность, разнообразие и избыточность средств языка SQL, предназначенных для формулировки запросов на выборку данных. Конечно, язык SQL (по крайней мере, ту часть SQL, которая обсуждается в этом курсе) нельзя считать языком программирования, но написание сложных запросов сродни программированию. И нельзя сказать, что SQL каким-либо образом дисциплинирует это “программирование”. По всей видимости, в общем случае никто не может сказать, какая из формулировок одного и того же запроса является более правильной, отвечает “хорошему вкусу”.

Зачастую десять студентов, одновременно формулирующих на SQL один и тот же запрос к одной и той же базе данных, выдают десять разных правильных решений. Один человек предпочитает формулировки запросов в классическом стиле, другой предпочитает использовать выражения запросов в разделе FROM, третий пытается сосредоточить все условия выборки в разделе HAVING. Люди с алгебраическими наклонностями предпочитают использовать выражения соединений. Приходилось встречать и формулировки со сложными вложенными подзапросами в списке выборки раздела SELECT.

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

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