Предикаты раздела WHERE оператора SELECT

Предикат between

Предикат позволяет специфицировать условие вхождения в диапазон значений. Операндами являются строки:

between_predicate ::=
    row_value_constructor [ NOT ] BETWEEN
    row_value_constructor AND row_value_constructor

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

Пусть X, Y и Z обозначают первый, второй и третий операнды. Тогда по определению выражение X NOT BETWEEN Y AND Z эквивалентно выражению NOT (X BETWEEN Y AND Z). Выражение X BETWEEN Y AND Z по определению эквивалентно булевскому выражению X >= Y AND X <= Z.

Примеры запросов с использованием предиката between

SELECT EMP_NO, EMP_NAME, EMP_SAL 
FROM EMP
WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;

SELECT EMP_NO, EMP_NAME, EMP_SAL 
FROM EMP
WHERE EMP_SAL BETWEEN 
    (SELECT AVG(EMP1.EMP_SAL)
      FROM EMP EMP1
        WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
          AND
    (SELECT EMP1.EMP_SAL 
      FROM EMP EMP1
        WHERE EMP1.EMP_NO =
          (SELECT DEPT.DEPT_MNG
            FROM DEPT
            WHERE DEPT.DEPT_NO = EMP.DEPT_NO));

В этом запросе можно выделить три интересных момента. Во-первых, диапазон значений предиката BETWEEN задан двумя подзапросами, результатом каждого из которых является единственное значение. Первый подзапрос выдает единственное значение, поскольку в списке выборки содержится агрегатная функция ( AVG ) и отсутствует раздел GROUP BY, а второй - потому что в его разделе WHERE присутствует условие, задающее единственное значение первичного ключа. Во-вторых, в обоих подзапросах таблица EMP получает псевдоним EMP1 (в формулировке этого запроса мы старались использовать как можно меньше вспомогательных идентификаторов). Поскольку подзапросы выполняются независимо один от другого, использование общего имени не вызывает проблем. Наконец, в условии второго подзапроса присутствует более глубоко вложенный подзапрос, и в условии его раздела WHERE используется ссылка на столбец таблицы из самого внешнего раздела FROM.

Предикат is null

Предикат is null позволяет проверить, являются ли неопределенными значения всех элементов строки-операнда:

null_predicate ::= row_value_constructor IS [ NOT ] NULL

Пусть X обозначает строку-операнд. Если значения всех элементов X являются неопределенными, то значением условия X IS NULL является true ; иначе - false. Если ни у одного элемента X значение не является неопределенным, то значением условия X IS NOT NULL является true ; иначе - false.

Замечание: условие X IS NOT NULL имеет то же значение, что условие NOT X IS NULL для любого X в том и только в том случае, когда степень X равна 1. Полная семантика предиката null приведена в таблице 14.1.

Примеры запросов с использованием предиката null

На самом деле, в нашей формулировке запроса из примера 14.6 есть одна неточность. Если у некоторого служащего номер отдела неизвестен (значение столбца EMP.DEPT_NO у соответствующей строки таблицы служащих является неопределенным), то бессмысленно вычислять средний размер зарплаты отдела этого служащего и находить размер зарплаты руководителя отдела. Формулировка из примера 14.6 приведет к правильному результату, но это неочевидно^{4Покажем это в развернутой форме. Пусть s - текущая строка таблицы EMP, просматриваемой в цикле внешнего запроса, и пусть s.DEPT_NO содержит неопределенное значение. Тогда для строки s условие первого подзапроса будет иметь вид NULL = EMP1.DEPT_NO, и значением этого условия будет unknown для любой строки таблицы EMP ( EMP1 ), просматриваемой в цикле этого подзапроса. Поскольку unknown не является разрешающим условием, результирующая таблица подзапроса будет пуста, и агрегатная функция AVG выдаст значение NULL. По этому поводу значением условия внешнего запроса будет unknown, и строка s не войдет в результирующую таблицу.} Чтобы сделать формулировку более понятной (и, возможно, помочь системе выполнить запрос более эффективно), нужно воспользоваться предикатом IS NOT NULL и переписать запрос следующим образом:

SELECT EMP_NO, EMP_NAME, EMP_SAL 
FROM EMP
WHERE DEPT_NO IS NOT NULL AND
    EMP_SAL BETWEEN 
    (SELECT AVG(EMP1.EMP_SAL)
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
        AND
    (SELECT EMP1.EMP_SAL 
      FROM EMP EMP1
      WHERE EMP1.EMP_NO =
        ( SELECT DEPT.DEPT_MNG
        FROM DEPT
        WHERE DEPT.DEPT_NO = EMP.DEPT_NO ) );

SELECT EMP_NO, EMP_NAME 
FROM EMP
WHERE DEPT_NO IS NULL;

Предикат in

Предикат позволяет специфицировать условие вхождения строчного значения в указанное множество значений. Синтаксические правила следующие:

in_predicate ::= row_value_constructor [ NOT ]
    IN in_predicate_value
in_predicate_value ::= table_subquery
    | (value_expression_comma_list)

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

Пусть X обозначает строку-первый операнд, а S - множество строк второго операнда. Обозначим через s строку-элемент этого множества. Тогда по определению условие X IN S эквивалентно булевскому выражению OR_sS (X = s). Другими словами, X IN S принимает значение true в том и только в том случае, когда во множестве S существует хотя бы один элемент s, такой, что значением предиката X = s является true. X IN S принимает значение false в том и только том случае, когда для всех элементов s множества S значением операции сравнения X = s является false. Иначе значением условия X IN S является unknown. Заметим, что для пустого множества S значением X IN S является false.

По определению условие X NOT IN S эквивалентно NOT (X IN S).

Примеры запросов с использованием предиката in

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMP
WHERE DEPT_NO IN (15, 17, 19);

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

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMP
WHERE DEPT_NO = 15
  OR DEPT_NO = 17
  OR DEPT_NO = 19;

SELECT EMP_NO 
FROM EMP
WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT)
    AND EMP_SAL IN (SELECT EMP_SAL FROM EMP, 
      DEPT WHERE EMP_NO = DEPT_MNG);

Запросы, содержащие предикат IN с подзапросом, легко переформулировать в запросы с соединениями. Например, запрос из примера 14.10 эквивалентен следующему запросу с соединениями ( пример 14.10.1):

SELECT DISTINCT EMP_NO 
FROM EMP, EMP EMP1, DEPT
WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT)
 AND EMP_SAL = EMP1_SAL
 AND EMP1.EMP_NO = DEPT.DEPT_MNG;

По поводу этой второй формулировки следует сделать два замечания. Во-первых, как видно, мы изменили только ту часть условия, в которой использовался предикат IN , и не затронули предикат NOT IN. Запросы с предикатами NOT IN запросами с соединениями так просто не заменяются. Во-вторых, в разделе SELECT было добавлено ключевое слово DISTINCT, потому что в результате запроса во второй формулировке для каждого служащего будет содержаться столько строк, сколько существует руководителей отделов, получающих такую же зарплату, что и данный служащий.