Операторы EXISTS и NOT EXISTS

Предположим, что мы хотим идентифицировать рабочих, которые не на­значены работать на некоторое здание. При поверхностном взгляде кажется, что такой запрос легко выполнить при помощи простого отрицания утверди­тельной версии запроса. Предположим, например, что нас интересует здание с BLDG_ID 435. Рассмотрим запрос:

SELECT WORKER_ID

FROM ASSIGNMENT

WHERE BLDG_ID NOT 435

К сожалению, это неверная формулировка решения. Запрос просто вы­даст нам ИД работников, работающих на других зданиях. Очевидно, что не­которые из них могут также быть назначены и на здание 435.

В правильно сформулированном решении используется оператор NOT EXISTS (не существует):

SELECT WORKER_ID

FROM WORKER

WHERE NOT EXISTS

(SELECT *

FROM ASSIGNMENT

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND

BLDG_ID = 435)

Результат:

WORKER_ID

Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапро­сом. EXISTS принимает значение «истина», если множество, порожденное подзапросом, не пусто. Если порожденное подзапросом множество пусто, то EXISTS принимает значение «ложь». Оператор NOT EXISTS, естественно, работает в точности наоборот. Он истинен, если результат подзапроса пуст, и ложен в противном случае.

 

Оператор EXISTS. Принимает значение «истина», если результирующее множество не пусто.

Оператор NOT EXISTS. Принимает значение «истина», если результирующее множество пусто.

В этом примере мы воспользовались оператором NOT EXISTS. Подзапрос выбирает все такие строки таблицы ASSIGNMENT, в которых WORKER_ID имеет то же значение, что и в строке, рассматриваемой главным запросом, а BLDG_ID равен 435. Если это множество пусто, тогда строка работника, рас­сматриваемая главным запросом, выбирается, поскольку это означает, что данный работник не работает на здании 435.

 

В приведенном нами решении использовав коррелированный подзапрос. Если мы воспользуемся вместо NOT EXISTS оператором IN, то можем обой­тись некоррелированным подзапросом:

SELECT WORKER_ID

FROM WORKER

WHERE WORKER_ID NOT IN

(SELECT WORKER_ID

FROM ASSIGNMENT

WHERE BLDG_ID = 435)

Это решение проще, чем решение с оператором NOT EXISTS. Возникает естественный вопрос, зачем нам вообще нужны EXISTS и NOT EXISTS. От­вет состоит в том, что NOT EXISTS является единственным средством реше­ния запросов, содержащих в условии слово «каждый». Такие запросы решаются в реляционной алгебре с помощью операции деления, а в реляционном исчислении - с помощью квантора всеобщности. Приведем пример запроса, в условии которого есть слово «каждый»:

Запрос: Перечислить работников, назначенных на каждое здание.

Этот вопрос может быть реализован в SQL при помощи двойного отрица­ния. Мы переформулируем запрос, включив в него двойное отрицание:

Запрос: Перечислить таких работников, для которых не существует здания, на которое они не назначены.

Мы выделили двойное отрицание. Ясно, что этот запрос логически эквивалентен предыдущему.

Теперь мы хотим сформулировать решение на SQL. Для того чтобы уп­ростить понимание окончательного решения, мы сначала дадим решение предварительной задачи: задачи идентификации всех зданий, на которые гипотетический работник, «1234» не назначен.

(I) SELECT BLDG_ID

FROM BUILDING

WHERE NOT EXISTS

(SELECT *

FROM ASSIGNMENT

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID = 1234)

Мы пометили этот запрос (I), поскольку мы будем ссылаться на него позже. Если не существует здания, удовлетворяющего этому запросу, то то­гда работник 1234 назначен на каждое здание и, следовательно, удовлетво­ряет условиям исходного запроса. Для того чтобы получить решение исход­ного запроса, мы должны обобщить запрос (I) с конкретного рабочего 1234 на переменную WORKER_ID и превратить этот модифицированный запрос в подзапрос большего запроса. Приведем решение:

 

(II) SELECT WORKER_ID

FROM WORKER

WHERE NOT EXISTS

(SELECT BLDG_ID

FROM BUILDING

WHERE NOT EXISTS

(SELECT *

FROM ASSIGNMENT

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Результат:

WORKER ID

 

Обратите внимание, что подзапрос, начинающийся с четвертой строки запроса (II), идентичен запросу (I), в котором «1234» заменено WORKER.WORKER_ID. Запрос (II) можно прочитать следующим образом:

Выбрать WORKER_ID из WORKER, если не существует здания, на которое WORKER_ID не назначен.

Это соответствует условиям исходного запроса.

Мы видим, что оператор NOT EXISTS может использоваться для форму­лировки тех запросов, при решении которых в реляционной алгебре требо­валась операция деления, а в реляционном исчислении - квантор всеобщ­ности. С точки зрения простоты использования оператор NOT EXISTS не дает никаких особых преимуществ, то есть в запросах SQL, в ко­торых дважды используется NOT EXISTS, ничуть не проще разобраться, чем в решениях реляционной алгебры с операцией деления или решениях реля­ционного исчисления с квантором всеобщности. Для создания языковых конструкций, позволяющих более естественным образом решать подобные запросы, потребуются дополнительные исследования.