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

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

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

table1

a1	a2	c1	c2
			NULL
	NULL	NULL

table2

b1	b2	c1	c2
	NULL	NULL
	NULL	NULL

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

JR

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2

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

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

JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
	NULL	NULL			NULL	NULL
	NULL	NULL			NULL	NULL

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

JR

a1	a2	c1	c2	b1	b2

Такой же результат будет получен при выполнении операции 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

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
	1
			NULL	NULL	NULL	NULL	NULL
	NULL	NULL		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
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

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

Результатом операции 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
			NULL	NULL	NULL	NULL	NULL
	NULL	NULL		NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

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

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

JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
	NULL	NULL			NULL	NULL
	NULL	NULL			NULL	NULL
			NULL	NULL	NULL	NULL

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

JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
	NULL	NULL			NULL	NULL
	NULL	NULL			NULL	NULL
NULL	NULL	NULL

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

JR

a1	a2	table1.c1	c2	b1	b2	table2.c1
	NULL	NULL			NULL	NULL
	NULL	NULL			NULL	NULL
			NULL	NULL	NULL	NULL
NULL	NULL	NULL

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

JR

a1	a2	c1	c2	b1	b2
			NULL	NULL	NULL
	NULL	NULL		NULL	NULL

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

JR

a1	a2	c1	c2	b1	b2
NULL	NULL
NULL	NULL	NULL			NULL
NULL	NULL	NULL			NULL

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

JR

a1	a2	c1	c2	b1	b2
			NULL	NULL	NULL
	NULL	NULL		NULL	NULL
NULL	NULL
NULL	NULL	NULL			NULL
NULL	NULL	NULL			NULL

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

JR

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
			NULL	NULL	NULL	NULL	NULL
	NULL	NULL		NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

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

Мы приведем всего пару примеров, чтобы проиллюстрировать формулировки запросов, в разделе 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 должен быть в состоянии распознать все эквивалентные формулировки одного и того же запроса и выработать для всех них один и тот же наиболее эффективный план выполнения. Но чем больше разнообразие возможных формулировок, тем сложнее эта задача. Отсюда практический совет: не злоупотребляйте сложностью формулировки запроса. Полагайтесь на интуицию (и имеющиеся представления об особенностях используемой вами системы) и формулируйте запрос как можно проще.

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

Дата добавления: 2014-01-03