Апросы, использующие соединения
Способность соединять две или более таблицы в одну, представляет собой одну из наиболее мощных средств реляционных систем.
Пример 5.13. Простое эквисоединение. Выдать все комбинации информации о таких поставщиках и деталях, которые размещены в одном и том же городе: SELECT S.*,P.*
FROM S,P
WHERE S.ГОРОД == Р.ГОРОД
Будут выбраны все строки с одинаковыми городами. Это декартово произведе- ние таблиц.
Варианты:
- WHERE S.ГОРОД Б<> Р.ГОРОД
- WHERE S.ГОРОД = Р.ГОРОД
AND S.СОСТОЯНИЕ = 20
При соединении необязательно чтобы было равенство. В случае равенства соединение называется эквисоединением.
В SELECT могут быть указаны выборочные поля, а не все. Выражение:
SELECT S.*, Р.* может быть записано: SELECT *
FROM S,P FROM S,Р
Но может быть записано и с указанием всех полей.
Так как при соединении возникают два одинаковых столбца (S.Город и Р.Город), то один столбец исключается и такое соединение называется естественным. Могут быть соединения и 3, 4 или любого числа таблиц.
|
|
|
Пример 5.14. Соединение с дополнительным условием. Выдать все комбинации информации о поставщиках и деталях, такие, что рассматриваемые поставщи - ки и детали “соразмещены”. Опустить при этом поставщиков с состоянием 20:
SELECT *
FROM S, Р
WHERE S.ГОРОД = Р.ГОРОД
AND S.СОСТОЯНИЕ <>20
Результат: НОМЕР_ПОСТАВЩИКА ИМЯ СОСТОЯНИЕS.ГОРОД
S2 Дима 10 Пермь
S2 Дима 10 Пермь
S3 Боря 30 Пермь
S3 Боря 30 Пермь
НОМЕР_ДЕТАЛИНАЗВАНИЕЦВЕТ ВЕСР.ГОРОД
Р2 Болт Зеленый 17 Пермь
Р5 Кулачок Голубой 12 Пермь
Р2 Болт Зеленый 17 Пермь
Р5 Кулачок Голубой 12 Пермь
|
|
|
Подзапросы
Подзапросы представляют собой вложенные предложения SELECT. Именно такая возможность позволила назвать язык SQL структурированным.
Пример5.15. Выдать фамилии поставщиков, поставляющих детали Р2:
SELECT ИМЯ
FROM S
WHERE НОМЕР_ПОСТАВЩИКА IN
(SELECT НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE НОМЕР_ДЕТАЛИ = ‘P2’);
Результат: ИМЯ
Саша
Дима
Боря
Коля
Этот подзапрос возвращает множество поставщиков, поставляющих деталь Р2:
S1, S2, S3, S4. Поэтому первоначальный запрос эквивалентен простому запросу :
SELECT ФАМИЛИЯ
FROM S
WHERE НОМЕР_ПОСТАВЩИКА IN (‘S1’,’S2’,’S3’,’S4’)
Номер поставщика здесь задан неявно. Для явного задания необходимо указать имя таблицы .
SELECT S.ИМЯ
FROM S
WHERE S.НОМЕР_ПОСТАВЩИКА IN
(SELECT SP.НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE SP.HOMEP_ДЕТАЛИ='P2')
Этот же подзапрос может быть выражен и соединением:
SELECT S.ИМЯ
FROM S,SP
WHERE S.HOМЕР_ПОСТАВЩИКА = P.HOMEP_ПОСТАВЩИКА
AND SP.HOMEP_ДЕТАЛИ = 'P2'
Подзапросы с несколькими уровнями вложения
|
|
|
Пример 5.16. Выдать имена поставщиков, поставляющих красные детали:
SELECТ ИМЯ
FROM S
WHERE НО МЕР_ПОСТАВЩИКА IN
(SELECT НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE НОМЕР_ДЕТАЛИ IN
(SELECT НОМЕР_ДЕТАЛИ
FROM P
WHERE ЦВЕТ = 'Красный'))
Результат: ИМЯ
Саша
Дима
Коля
Коррелированный подзапрос.
Пример 5.17. Выдать имена поставщиков, которые поставляют деталь P2:
SELECT ФАМИЛИЯ
FROM S
WHERE ‘P2’ IN
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = S.НОМ ЕР_ПОСТАВЩИКА
В данном случае в предикате подзапроса
WHERE НОМЕР_ПОСТАВЩИКА = S. НОМЕР_ПОСТАВЩИКА задано не какое-то конкретное значение номера поставщика, а название поля S. НОМЕР_ПОСТАВЩИКА и алгоритм работы следующий.
Система проверяет первую строку таблицы S. Предположим, что это строка поставщика "S1". Тогда переменная S.HOMEP_ПОСТАВЩИКА в данный момент имеет значение "S1", и система обрабатывает внутренний запрос:
|
|
|
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = ' S 1')
Далее система будет повторять обработку такого рода для следующего поставщика и т. д., пока не будут рассмотрены все строки таблицы S.
Такой подзапрос, как в этом примере, называется коррелированным. Для того чтобы сделать более ясной связь коррелированных подзапросов с внешними запросами, иногда вводят псевдонимы.
Например:
SELECT SX.ИМЯ
FROM S SX
WHERE 'P2' IN
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = SX. НОМЕР_ПОСТАВЩИКА)
Здесь псевдонимом является имя SX, введенное во фразе FROM как альтернативное имя таблицыS, т. е.
- SX - это переменная, областью определения которой является множество записей таблицы S.
Поочередно для каждого возможного значения SX выполняется следующее:
1) вычисляется подзапрос и получается множество номеров деталей Р;
2) добавляется к результирующему множеству значение SX. ИМЯ, если
только P2 принадлежит множеству Р.
Некоторые считают более ясным использование двух различных символов для того, чтобы различать эти две различные функции.
Пример 5.18. Использование одной и той же таблицы в подзапросе и внешнем запросе. Выдать номера поставщиков, которые поставляют по крайней мере одну деталь, поставляемую поставщиком S2:
SELECT DISTINCT НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE НОМЕР_ДЕТАЛИ IN
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = 'S2')
Результат: НОМЕР ПОСТАВЩИКА
S1
S2
S3
S4
Решение этой задачи с использованием псевдонимов:
SELECT DISTINCT SPX.HOMEP_ПОСТАВЩИКА
FROM SP SPX
WHERE SPX.HOMEP_ДЕТАЛИ IN
(SELECT SPY.HOMEP_ДЕТАЛИ
FROM SP SPY
WHERE SPY.HOMEP_ПОСТАВЩИКА = ' S2')
Эквивалентный запрос с использованием соединения имеет вид:
SELECT DISTINCT SPX.HOMEP_ПОСТАВЩИКА
FROM SP SPX, SP SPY
WHERE SPX.HOMEP_ДЕТАЛИ=SPY.HOMEP_ДЕТАЛИ
AND SPY.HOMEP_ПОСТАВЩИКА = ' S2'
Пример 5.19. Случай, когда в коррелированном и внешнем запросе используется одна и та же таблица. Выдать номера всех деталей, поставляемых более чем одним поставщиком:
SELECT DISTINCT SPX.HOMEP_ДЕТАЛИ
FROM SP SPX
WHERE SPX.HOMEP_ДЕТАЛИ IN
(SELECT SPY.HOMEP_ДЕТАЛИ
FROM SP SPY
WHERE SPY.НОМЕР_ПОСТАВЩИКА<>SPX.НОМЕР_ПОСТАВЩИКА)
Результат: НОМЕР ДЕТАЛИ
Р1
P2
P4
P5
Пример 5.20. Подзапрос с оператором сравнения, отличным от IN. Выдать номера поставщиков, находящихся в том же городе, что и поставщик S 1:
SELECT НОМЕР_ПОСТАВЩИКА
FROM S
WHERE ГОРОД=
(SELECT ГОРОД
FROM S
WHERE НОМЕР_ПОСТАВЩИКА = ' S 1')
Результат: НОМЕР ПОСТАВЩИКА
Sl
S4
Дата добавления: 2018-04-04; просмотров: 334; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!