Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 7969 / 391 | Оценка: 3.85 / 3.50 | Длительность: 22:03:00
Лекция 5:

Группировка и условия раздела HAVING, порождаемые и соединенные таблицы

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

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

Итак, пусть имеются таблицы 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 будет следующим:

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

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

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

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

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

Результатом операции 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
Примеры запросов с использованием соединенных таблиц

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

Можно обойтись вообще без раздела 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.

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

Александра Каева
Александра Каева
Антон Честиков
Антон Честиков

Не вижу список тем из плана занятий. Проблема известна?

Евгений Снегиренко
Евгений Снегиренко
Россия, г. Кемерово
Андрей Шадурский
Андрей Шадурский
Россия, Санкт-Петербург