SELECT DISTINCT X.ПС
FROM Поставки X
WHERE X.ПР IN
( SELECT Y.ПР
FROM Поставки Y
WHERE Y.ПС = 6 );
Здесь X и Y – произвольные псевдонимы таблицы Поставки, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.
Вложенный подзапрос с оператором сравнения, отличным от IN
Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.
| Результат: | |
| SELECT ПС FROM Поставщики WHERE Город = ( SELECT Город FROM Поставщики WHERE ПС = 6 ); | ПС |
| 1 | |
| 4 | |
| 6 |
В подобных запросах можно использовать и другие операторы сравнения (<, <=, <, = или ), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.
Коррелированные вложенные подзапросы
Выдать название и статус поставщиков продукта с номером 11.
SELECT Название, Статус
FROM Поставщики
WHERE 11 IN
( SELECT ПР
FROM Поставки
WHERE ПС = Поставщики.ПС );
Такой подзапрос отличается от обычного тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.ПС а оно изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:
1. Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос
( SELECT ПР
FROM Поставки
WHERE ПС = 1 );
получая в результате множество (9, 11, 12, 15). Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС=1 (СЫТНЫЙ и рынок) будет проведена тогда и только тогда, когда ПР=11 будет принадлежать этому множеству, что, очевидно, справедливо.
2. Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики.
Подобные подзапросы называются коррелированными, так как их результат зависит от значений, определенных во внешнем подзапросе. Обработка коррелированного подзапроса, следовательно, должна повторяться для каждого значения извлекаемого из внешнего подзапроса, а не выполняться раз и навсегда.
Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе.
Выдать номера всех продуктов, поставляемых только одним по-ставщиком.
| Результат: | |
| SELECT DISTINCT X.ПР FROM Поставки X WHERE X.ПР NOT IN ( SELECT Y.ПР FROM Поставки Y WHERE Y.ПС <> X.ПС ); | X.ПР |
| 17 |
Действие этого запроса можно пояснить следующим образом: «Поочередно для каждой строки таблицы Поставки, скажем X, выделить значение номера продукта (ПР), если и только если это значение не входит в некоторую строку, скажем, Y, той же таблицы, а значение столбца номер поставщика (ПС) в строке Y не равно его значению в строке X».
Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним – либо X, либо Y.
Запросы, использующие EXISTS
Квантор EXISTS (существует) – понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM …).
Такое выражение считается истинным только тогда, когда результат вычисления «SELECT * FROM …» является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.)
Рассмотрим примеры. Выдать названия поставщиков, поставляющих продукт с номером 11.
| Результат: | |
| SELECT Название FROM Поставщики WHERE EXISTS ( SELECT * FROM Поставки WHERE ПС = Поставщики.ПС AND ПР = 11 ); | Название |
| СЫТНЫЙ | |
| УРОЖАЙ | |
| КОРЮШКА | |
| ЛЕТО |
Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбцов Название и ПС, а затем проверяет, является ли истинным условие существования, т.е. су-ществует ли в таблице Поставки хотя бы одна строка со значением ПР=11 и значением ПС, равным значению ПС, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат.
Предположим, что первые значения полей Название и ПС равны, соответственно, 'СЫТНЫЙ' и 1. Так как в таблице Поставки есть строка с ПР=11 и ПС=1, то значение 'СЫТНЫЙ' должно быть включено в результат.
Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.
Выдать название и статус поставщиков, не поставляющих продукт с номером 11.
| Результат: | ||
| SELECT Название, Статус FROM Поставщики WHERE NOT EXISTS ( SELECT * FROM Поставки WHERE ПС = Поставщики.ПС AND ПР = 11 ); | Название | Статус |
| ПОРТОС | кооператив | |
| ШУШАРЫ | совхоз | |
| ТУЛЬСКИЙ | универсам | |
| ОГУРЕЧИК | ферма | |
Функции в подзапросе
Теперь, после знакомства с различными формулировками вложенных подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса на получение тех поставщиков продуктов для Сырников, которые поставляют эти продукты за минимальную цену:
SELECT Продукт, Цена, Название, Статус
FROM Продукты, Состав, Блюда, Поставки, Поставщики
WHERE Продукты.ПР = Состав.ПР
AND Состав.БЛ = Блюда.БЛ
AND Поставки.ПР = Состав.ПР
AND Поставки.ПС = Поставщики.ПС
AND Блюдо = 'Сырники'
AND Цена = ( SELECT MIN(Цена)
FROM Поставки X
WHERE X.ПР = Поставки.ПР );
Естественно, что это коррелированный подзапрос: здесь сначала определяется минимальная цена продукта, входящего в состав Сырников, и только затем выясняется его поставщик.
На этом примере мы закончим знакомство с вложенными подзапросами, предложив попробовать свои силы в составлении ряда запросов, с помощью механизма таких подзапросов:
1. Выдать названия всех мясных блюд.
2. Выдать количество всех блюд, в состав которых входят помидоры.
3. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.
Объединение (UNION)
Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:
a. они имеют одинаковое число столбцов, например, m;
b. для всех i (i = 1, 2, …, m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных.
Например, выдать названия продуктов, в которых нет жиров, либо входящих в состав блюда с кодом БЛ = 1:
| Результат: | Продукт | ||
| SELECT Продукт FROM Продукты WHERE Жиры = 0 UNION SELECT Продукт FROM Соста WHERE БЛ = 1 | Майонез | ||
| Лук | |||
| Помидоры | |||
| Зелень | |||
| Яблоки | |||
| Сахар | |||
Из этого простого примера видно, что избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере Помидоры, Зелень и Яблоки выбираются обеими из двух составляющих предложения SELECT, в окончательном результате они появляются только один раз.
Предложением с UNION можно объединить любое число таблиц (проекций таблиц). Так, к предыдущему запросу можно добавить (перед точкой с запятой) конструкцию
UNION
SELECT Продукт
FROM Продукты
WHERE Ca < 250
позволяющую добавить к списку продуктов Масло, Рис, Мука и Кофе. Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса
WHERE Жиры = 0 OR Ca < 250
Реализация операций реляционной алгебры предложением SELECT
С помощью предложения SELECT можно реализовать любую операцию реляционной алгебры.
Селекция (горизонтальное подмножество) таблицы создается из тех ее строк, которые удовлетворяют заданным условиям. Пример:
SELECT *
FROM Блюда
WHER Основа = 'Молоко'
AND Выход 200;
Проекция (вертикальное подмножество) таблицы создается из указанных ее столбцов (в заданном порядке) с последующим исключением избыточных дубликатов строк. Пример:
SELECT DISTINCT Блюдо, Выход, Основа
FROM Блюда;
Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах. Пример:
SELECT Блюдо, Основа, Выход
FROM Блюда
WHER Основа = 'Овощи'
UNION
SELECT Блюдо, Основа, Выход
FROM Блюда
WHER В = 'Г';
Пересечение двух таблиц содержит только те строки, которые есть и в первой, и во второй. Пример:
SELECT БЛ
FROM Состав
WHERE БЛ IN
( SELECT БЛ
FROM Меню);
Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Пример: