Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Предикаты существования

Предикаты существования

Последняя группа предикатов включает предикаты, которые используют подзапросы для передачи значений для различного вида утверждений в условиях поиска. Ключевые слова для этих предикатов ALL, ANY, EXISTS, IN, SINGULAR и SOME. Они называются предикатами существования, потому что все они используются в предикатах поиска, которые разными способами проверяют существование значения в левой части предиката в выходных результатах включенных запросов к другим таблицам.

Все эти предикаты- описанные в табл. 21.6- используют в том или ином виде подзапросы. Тема подзапросов подробно рассматривается в главе 22.

Таблица 21.6. Предикаты существования

Самый полезный из всех предикатов существования - EXISTS предоставляет самый быстрый из всех возможных методов способ проверки существования значения в другой таблице.

Часто в хранимых процедурах или запросах вам может понадобиться узнать, существуют ли в таблице строки, соответствующие некоторому набору критериев. Вас не интересует, сколько существует таких строк - нужно только определить, есть ли хотя бы одна. Стратегия выполнения COUNT(*) с последующей проверкой, что полученный результат больше нуля, дорого стоит в Firebird.

! ! !

ВНИМАНИЕ! Счетчик строк, полученный в контексте одной транзакции для проверки условия последующей работы в другой транзакции, - например, для вычисления значения "следующего" ключа - является совершенно ненадежным.

. ! .

Стандартный предикат SQL EXISTS (значение-подзапроса) и его противоположный аналог NOT EXISTS дают способ выполнения проверки существования набора, который является очень дешевым с точки зрения используемых ресурсов. Он не генерирует выходной набор, а просто проходит по таблице, пока не найдет строку, соответствующую условиям в подзапросе. В этот момент возвращается истина. Если же не находится ни одной строки, то возвращается ложь.

В первом примере EXISTS проверяет условия для выполнения изменения в операторе динамического SQL:

UPDATE TABLEA

SET COL6 ='SOLD'

WHERE COL1 =99

AND EXISTS(SELECT COLB FROM TABLEB WHERE COLB = 99);

Оператор, подобный нашему примеру, обычно использует заменяемые параметры в правой части предикатов в предложении WHERE.

В действительности многие подзапросы в предикатах EXISTS являются коррелированными, то есть условия поиска подзапроса связаны отношением с одним или более столбцами главного запроса. Возьмем предыдущий пример и заменим жестко закодированную константу в условии поиска ссылкой на столбец. Более реальный запрос может быть таким:

UPDATE TABLEA

SET TABLEA.COL6 ='SOLD'

WHERE EXISTS(SELECT TABLEB.COLB FROM TABLEB

WHERE TABLEB.COLB = TABLEA.COL1) ;

Действием такого выражения существования будет: "Если в таблице TABLEB существует хотя бы одна соответствующая строка, то выполнить изменение".

Предикат IN, используемый в подзапросе, похож на EXISTS постольку, поскольку он может проверять результат подзапроса. Например:

UPDATE TABLEA

SET COL6 ='SOLD'

WHERE COL1 IN (SELECT COLB FROM TABLEB

WHERE COL1 > 0);

В этом случае подзапрос возвращает набор всех значений столбца COLB второй таблицы, которые соответствуют его собственному предложению WHERE. Предикат IN приводит к сравнению COL1 С каждым возвращенным значением набора, пока не будет найдено соответствие. Он будет выполнять изменение каждой строки таблицы TABLEA, для которой значение COL1 соответствует значению в наборе.

! ! !

ПРИМЕЧАНИЕ. В Firebird предикат IN, который использует набор подзапроса, фактически реализуется предикатом EXISTS для операции сравнения.

. ! .

С точки зрения производительности предикат IN не будет полезен, когда подзапрос возвращает достаточно большое количество значений. Чаще он используется при формировании набора значений для сравнения из небольшой таблицы соответствия (lookup table) или из фиксированного набора констант, например:

UPDATE TABLEA SET C0L6 ='SOLD'

WHERE COL1 IN ('А', 'В', 'C', 'D');

Количество констант в наборе имеет ограничение максимум 1500 значений- возможно меньше, если значения велики по размеру, и размер строки запроса превышает его лимит в 64 Кбайт.

Его использование лучше проиллюстрировать, начав с примера:

SELECT * FROM MEMBERSHIP

WHERE

(EXTRACT (YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM DATE_OF_BIRTH))

< ALL (SELECT MINIMUM_AGE FROM AGE_GROUP) ;

Выражение в левой части вычисляет возраст в годах для каждого человека из таблицы MEMBERSHIP и выводит только тех людей, кто моложе минимального возраста в таблице MINIMUM AGE. Предикат ALL имеет ограниченное использование, поскольку он подходит только для поиска в случае сортировки от большего к меньшему при проверке исключения.

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

SELECT OH.ORDER_ID FROM ORDER_HEADER OH

WHERE OH.ORDER_ID = SINGULAR(SELECT OD.ORDER_ID

FROM ORDER_DETAIL OD) ;

Эти два предиката идентичны по поведению. Очевидно, оба представлены в стандарте SQL для взаимозаменяемого использования с целью улучшения читаемости операторов. При сравнении на равенство они логически эквивалентны предикату EXISTS. При этом, поскольку они предназначены и для других сравнений, таких как >, <, >=, <=, STARTING WITH, LIKE и CONTAINING, они в особенности полезны для проверок существования, где EXISTS не может быть использован.

Следующий оператор будет отыскивать список всех служащих, кто имеет, по крайней мере, одно изменение оклада в течение года после приема на работу:

SELECT E.EMP_NO, E.FULL_NAME, E.HIRE_DATE

FROM EMPLOYEE E

WHERE E.HIRE_DATE + 365 > SOME (

SELECT SH.CHANGE_DATE FROM SALARY_HISTORY SH

WHERE SH.EMP_NO = E.EMP_NO);

Обратите внимание на использование алиасов таблиц в последних двух примерах, что уменьшает двусмысленность в соответствующих именах столбцов двух таблиц. Firebird весьма требователен к использованию алиасов таблиц в запросах к нескольким таблицам. Алиасы таблиц обсуждаются в главе 22.

Оглавление книги