metBD (1084482), страница 36

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 36)

(SELECT SPY.НОМЕР_ДЕТАЛИ

FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА !=

SPX.НОМЕР_ПОСТАВЩИКА);

Результат:

Действие этого запроса можно пояснить следующим образом. «Поочередно для каждой строки таблицы SP, скажем SPX, вы­делить значение НОМЕР_ДЕТАЛИ, если и только если это значе­ние входит в некоторую строку, скажем SPY, таблицы SP, значе­ние столбца НОМЕР_ПОСТАВЩИКА в которой не является его значением в строке SPX». Заметим, что в этой формулировке дол­жен быть использован, по крайней мере, один псевдоним — либо SPX, либо SPY, но не они оба, может быть заменен просто на SP.

Подзапрос с оператором сравнения, отличным от in

Выдать номера поставщиков, находящихся в том же городе, что и поставщик S1.

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE ГОРОД =

(SELECT ГОРОД

FROM S

WHERE НОМЕР_ПОСТАВЩИКА = ’S1’);

Результат:

Иногда пользователь может знать, что заданный подзапрос должен возвратить в точности одно значение, как в рассматри­ваемом примере. В таком случае можно использовать вместо обычного IN более простой оператор сравнения (например, =, > и т. д.). Однако если подзапрос возвращает более одного значе­ния и не используется оператор IN, будет возникать ошибка. Ошибка не возникнет, если подзапрос не возвратит вообще ни одного значения. При этом сравнение интерпретируется в точности так, как если бы подзапрос возвратил неопределенное зна­чение. Иными словами, если х — переменная, то сравнение

х простой—оператор—сравнения (подзапрос),

где «подзапрос» возвращает пустое множество, имеет значение истинности не истина или ложь, а неизвестно.

Нужно отметить, что сравнение в предыдущем примере должно быть записано именно так, как показано — подзапрос должен сле­довать за оператором сравнения. Иначе говоря, следующая запись запроса некорректна:

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE (SELECT ГОРОД

FROM S

WHERE НОМЕР_ПОСТАВЩИКА = ’S1’) = ГОРОД;

Более того, подзапрос не может включать фраз GROUP BY или HAVING, если он используется с простым оператором срав­нения, например с =, > и т.д.

Квантор существования

Запрос, использующий exists

Выдать фамилии поставщиков, которые поставляют деталь Р2.

SELECT ФАМИЛИЯ

FROM S

WHERE EXISTS

(SELECT *

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА =

S.НОМЕР_ПОСТАВЩИКА

AND НОМЕР_ДЕТАЛИ = ’Р2’);

Пояснение. EXISTS (существует) представляет здесь квантор существования — понятие, заимствованное из формальной логики. Пусть символ «х» обозначает некоторую произвольную перемен­ную. Тогда в логике предикат с навешенным квантором сущест­вования EXISTS х (предикат—зависящий—от—х) принимает значение истина тогда и только тогда, когда «предикат—за­висящий—от—х» имеет значение истина при каком-либо значе­нии переменной х. Предположим, например, что переменная х обозначает любое целое число в диапазоне от 1 до 10. Тогда пре­дикат

EXISTS х (х < 5)

принимает значение истина. Напротив, предикат

EXISTS x (x<0)

принимает значение ложь.

В языке SQL предикат с квантором существования представ­ляется выражением вида

EXISTS (SELECT * FROM …).

Такое выражение считается истинным тогда и только тогда, когда результат вычисления подзапроса, представленного с помощью «SELECT * FROM …», является непустым множеством, иными сло­вами, тогда и только тогда, когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удов­летворяет условию WHERE этого подзапроса. (На практике этот подзапрос всегда будет коррелированным множеством.)

Вернемся к приведенному выше примеру. Поочередно рассмат­риваем каждое значение столбца ФАМИЛИЯ и проверяем, является ли для него истинным условие существования. Предполо­жим, что первое значение поля ФАМИЛИЯ — ’Смит’. Тогда соот­ветствующее значение поля HOMEP_ПОСТАВЩИКА — S1. Яв­ляется ли пустым множество записей из SP, содержащих НО­МЕР_ПОСТАВЩИКА, равный S1, и НОМЕР_ДЕТАЛИ, равный Р2? Если ответ отрицателен, то существует запись в SP с НОМЕРОМ_ПОСТАВЩИКА, равным S1, и номером детали, равным Р2, и, следовательно, ’Смит’ должно быть одним из результирую­щих значений. Аналогично поступаем для каждого из других зна­чений столбца ФАМИЛИЯ.

Хотя этот первый пример только показывает иной способ фор­мулировки запроса для задачи, с которой мы уже умеем справ­ляться в языке SQL (используя либо соединение, либо оператор IN), EXISTS представляет собой одну из наиболее важных воз­можностей полного языка SQL. Фактически любой запрос, кото­рый может быть выражен с использованием IN, может быть аль­тернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.

Запрос, использующий not exists

Выдать фамилии поставщиков, которые не поставляют деталь Р2.

SELECT ФАМИЛИЯ

FROM S

WHERE NOT EXISTS

(SELECT *

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА =

S.НОМЕР_ПОСТАВЩИКА

AND НОМЕР_ДЕТАЛИ = ’P2’);

Результат:

Этот запрос можно перефразировать: «Выбрать фамилии по­ставщиков таких, что не существует поставки, связывающей их с деталью Р2». Заметим, что легко преобразовать решение преды­дущей задачи в решение данной.

Между прочим, заключенный в скобки подзапрос, входящий в выражение EXISTS, вовсе не обязательно должен использовать предложение SELECT вида «SELECT *». Можно использовать, например, предложение следующего вида: «SELECT имя-поля FROM …». Однако на практике оно почти всегда будет иметь вид «SELECT *», как уже было продемонстрировано в наших при­мерах.

Запрос, использующий not exists

Выдать фамилии поставщиков, которые поставляют все де­тали.

Имеется два квантора, обычно встречающихся в логике, EXISTS (существует) и FORALL (для всех). FORALL—это квантор общности. В логике предикат с навешенным квантором общности

FORALL х (предикат-зависящий-от-х)

принимает значение истина тогда и только тогда, когда «пре­дикат-зависящий-от-х» принимает значение истина для всех зна­чений переменной х. Например, если х снова обозначает любое целое число в диапазоне от 1 до 10, то предикат

FORALL х (х < 100)

принимает значение истина в то время, как предикат

FORALL х (х < 5)

принимает значение ложь.

В принципе, FORALL — это то, что нужно для формулировки рассматриваемого запроса. Нам хотелось бы сказать примерно следующее: «Выдать фамилии поставщиков таких, что ДЛЯ ВСЕХ (FORALL) деталей СУЩЕСТВУЕТ (EXISTS) запись в таб­лице SP, указывающая, что данный поставщик поставляет эту деталь». К сожалению, в языке SQL квантор FORALL непосред­ственно не поддерживается. Однако включающий FORALL пре­дикат всегда может быть преобразован в эквивалентный преди­кат, содержащий вместо него квантор существования EXISTS, при помощи следующего тождества:

FORALL х (р) = NOT (EXISTS х (NOT (р))).

Здесь «р» — это любой предикат, который зависит от перемен­ной х. Например, предположим еще раз, что х обозначает любое целое число в диапазоне от 1 до 10. Тогда предикат

FORALL x (x < 100)

(значение которого, конечно, истина) эквивалентен следующему предикату:

NOT (EXISTS х (NOT (х < 100)))

(«не существует такого х, для которого бы не имело места, что х меньше 100», т. е. «не существует х такого, что х>= 100»). Ана­логичным образом, предикат

FORALL х (х < 5)

(который имеет значение ложь) эквивалентен предикату

NOT (EXISTS х (NOT (х < 5)))

(«не существует такого х, для которого было бы несправедливо, что х<5», т. е. «не существует такого х, что х> =5»).

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

FORALL х (EXISTS у (у> х)),

значение которого — истина, эквивалентен следующему:

NOT (EXISTS (NOT (EXISTS у (у > х))))

(«не существует такого действительного х, для которого не суще­ствует такого действительного у, что у больше х»).

Вернемся теперь к рассматриваемой задаче. Можно преобра­зовать выражение «поставщики такие, что ДЛЯ ВСЕХ деталей СУЩЕСТВУЕТ запись в таблице SP, указывающая, что данный поставщик поставляет эту деталь» в эквивалентное выражение «поставщики такие, что НЕ СУЩЕСТВУЕТ детали такой, что НЕ СУЩЕСТВУЕТ записи в таблице SP, указывающей, что дан­ный поставщик поставляет эту деталь». Следовательно, форму­лировка запроса в языке SQL такова:

SELECT ФАМИЛИЯ

FROM S

WHERE NOT EXISTS

(SELECT *

FROM P

WHERE NOT EXISTS

(SELECT *

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА =

S.НОМЕР_ПОСТАВЩИКА

AND НОМЕР_ДЕТАЛИ = Р.НОМЕР_ДЕТАЛИ));

Результат:

Данный запрос можно перефразировать: «Выдать фамилии по­ставщиков таких, что не существует детали, которую они бы не поставляли». Вообще говоря, наиболее легкий способ, позволяю­щий справляться со сложными запросами, подобными только что рассмотренному, состоит, вероятно, в том, чтобы сначала записывать их в «пceвдoSQL» форме с использованием квантора FORALL, а затем более или менее механически трансформировать такую запись в реальный SQL, используя взамен NOT EXISTS.

Запрос, использующий not exists

Выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик S2.

Один из способов справиться с этой сложной задачей состоит в том, чтобы разбить ее на множество более простых запросов и заниматься ими последовательно. Так, можно сначала определить множество номеров деталей, которые поставляются поставщи­ком S2:

SELECT НОМЕР_ДЕТАЛИ

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА = ’S2’;

Результат:

Используя предложения CREATE TABLE и INSERT, которые будут обсуждаться в части 8.4, можно сохранить этот результат в некоторой таблице в базе данных, например в таблице ВРЕ­МЕННАЯ. Далее можно перейти к определению множества номе­ров поставщиков, которые поставляют все детали, перечисленные в таблице ВРЕМЕННАЯ:

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА

FROM SP SPX

WHERE NOT EXISTS

(SELECT *

Характеристики

Список файлов книги