- •5. Сложные запросы
- •5.1. Теоретико-множественные операции
- •5.1.1. Декартово произведение наборов записей
- •5.1.2. Объединение наборов записей
- •5.1.3. Пересечение наборов записей
- •5.1.4. Вычитание наборов записей
- •5.2. Внешние соединения
- •5.2.1. Левое внешнее соединение
- •5.2.2. Правое внешнее соединение
- •5.2.3. Полное внешнее соединение
- •5.3. Подзапросы
- •5.3.1. Простые подзапросы
- •5.3.2. Связанные подзапросы
- •5.4. Представления
- •Лабораторная работа 5
5. Сложные запросы
5.1. Теоретико-множественные операции
Над наборами записей, содержащихся в таблицах базы данных и/или возвращаемых запросами, можно совершать теоретико-множественные операции, такие как декартово произведение, объединение, пересечение и вычитание.
5.1.1. Декартово произведение наборов записей
Запрос вида SELECT списокСтолбцов
FROM T1, T2, ..., Tn;
возвращает набор записей, полученный в результате декартового произведения наборов записей из таблиц T1, T2, ..., Tn. Таблицы, указанные в операторе FROM, могут быть как таблицами базы данных, так и виртуальными таблицами, возвращаемыми какими-нибудь запросами.
Иногда требуется получить декартово произведение таблицы самой на себя. В этом случае необходимо применить различные псевдонимы для этой таблицы, например:
SELECT списокСтолбцов
FROM MyTab T1, MyTab T2;
Попытка выполнить запрос:
SELECT списокСтолбцов
FROM MyTab, MyTab;
приведет к ошибке.
В списке столбцов следует использовать полные имена с помощью точечной записи.
Для декартова произведения в SQL также допустим синтаксис с ключевыми словами CROSS JOIN:
SELECT списокСтолбцов
FROM MyTab T1 CROSS JOIN MyTab T2;
Запросы на декартово произведение сами по себе очень редко используются. Они приобретают некоторый смысл, если применяются с секцией WHERE.
Допустим, что имеется таблица Рейсы (Начальный_пункт, Конечный_пункт), содержащая сведения о том, из каких пунктов и в какие можно попасть с помощью того или иного авиарейса.
Рейсы | |
Начальный_пункт |
Конечный_пункт |
A |
B |
A |
C |
A |
F |
B |
C |
B |
G |
B |
F |
C |
E |
C |
D |
D |
H |
Можно заметить, что из некоторых пунктов в другие можно попасть только с пересадкой на другой рейс, т.е. через транзитный пункт. Следующий запрос возвращает таблицу, содержащую сведения о достижимости пунктов в точности через один транзитный пункт:
SELECT T1.Начальный_пункт, T2.Конечный_пункт
FROM Рейсы T1, Рейсы T2
WHERE T1.Конечный_пункт = T2.Начальный_пункт;
Сначала запрос выполняет декартово произведение таблицы Рейсы на эту же таблицу. В результате получается таблица с четырьмя столбцами: T1.Начальный_пункт, T1.Конечный_пункт, T2.Начальный_пункт, T2.Конечный_пункт. Затем из полученной таблицы выбираются такие записи, в которых T1.Конечный_пункт = T2.Начальный_пункт. Это и есть пары пунктов, между которыми находится один промежуточный пункт. Наконец, из четырех столбцов выделяются только два: T1.Начальный_пункт и T2.Конечный_пункт. Результат запроса приведен ниже.
Рейсы | |
Начальный_пункт |
Конечный_пункт |
A |
C |
A |
G |
A |
F |
A |
E |
A |
D |
B |
E |
B |
D |
C |
H |
5.1.2. Объединение наборов записей
Нередко требуется объединить наборы записей двух или более таблиц с похожими структурами в одну таблицу. Иначе говоря, к набору записей, возвращаемых одним запросом, требуется добавить записи, возвращаемые другим запросом. Для этого служит оператор UNION:
Запрос1
UNION
Запрос2;
При этом в результатной таблице остаются только отличающиеся записи. Чтобы сохранить в ней все записи, следует после оператора UNION написать ключевое слово ALL.
Например, таблицы Коробки и Крышки имеют однотипные столбцы Размер, Количество и Цвет. Тогда, чтобы получить общий список данных и о коробках, и о крышках, достаточно выполнить следующий запрос:
SELECT Размер, Количество, Цвет
FROM Коробки
UNION
SELECT Размер, Количество, Цвет
FROM Крышки;
Оператор UNION можно применять только к таблицам, удовлетворяющим следующим условиям совместимости:
количества столбцов объединяемых таблиц должны быть равны;
данные в соответствующих столбцах объединяемых таблиц должны иметь совместимые типы данных. Например, символьные (строковые) типы CHAR и VARCHAR совместимы, а числовой и строковый типы не совместимы.
Имена соответствующих столбцов и их размеры могут быть различными. Важно, чтобы количества столбцов были равны, а их типы были совместимы.
Пусть требуется получить сведения о том, в какие пункты можно попасть, сделав не более одной пересадки (т.е. без пересадок или с одной пересадкой). Для этого достаточно объединить записи исходной таблицы Рейсы с результатом запроса о достижимости через один промежуточный пункт:
SELECT Начальный_пункт, Конечный_пункт
FROM Рейсы
UNION
SELECT T1.Начальный_пункт, T2.Конечный_пункт
FROM Рейсы T1, Рейсы T2
WHERE T1.Конечный_пункт = T2.Начальный_пункт;