Материал: Базы данных и их безопасность. Плотников Д.Г

Эквивалентный запрос с использованием соединения имеет вид:

SELECT DISTINCT SPX.НОМЕР_ПОСТАВЩИКА FROM SP SPX, SP SPY

WHERE SPX.НОМЕР_ДЕТАЛИ = SPY.НОМЕР_ДЕТАЛИ AND SPY.НОМЕР_ПОСТАВЩИКА = ’S2’;

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

Выдать номера всех деталей, поставляемых более чем одним поставщиком:

SELECT DISTINCT SPX.НОМЕР_ДЕТАЛИ FROM SP SPX

WHERE SPX.НОМЕР_ДЕТАЛИ IN

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

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

Результат:

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

P1

P2

P3

P5

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

271

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

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

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

FROM S

WHERE ГОРОД = (SELECT ГОРОД FROM S

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

Результат:

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

S1

S4

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

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

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

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

272

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)

273

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

EXISTS x (x<0)

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

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

EXISTS (SELECT * FROM …).

Такое выражение считается истинным тогда и только тогда, когда результат вычисления подзапроса, представленного

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

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

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

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

наиболее важных возможностей полного языка SQL.

274

Фактически любой запрос, который может быть выражен с использованием 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

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

275