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

Предикат сравнения с квантором

Этот предикат позволяет специфицировать квантифицированное сравнение строчного значения и определяется следующим синтаксическим правилом:

quantified_comparison_predicate ::= row_value_constructor
    comp_op { ALL | SOME | ANY } query_expression

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

Обозначим через x строку-первый операнд, а через S - результат вычисления выражения запроса. Пусть s обозначает произвольную строку таблицы S. Тогда:

• условие x comp_op ALL S имеет значение true в том и только в том случае, когда S пусто, или значение условия x comp_op s равно true для каждой строки s, входящей в S. Условие x comp_op ALL S имеет значение false в том и только в том случае, когда значение предиката x comp_op s равно false хотя бы для одной строки s, входящей в S. В остальных случаях значение условия x comp_op ALL S равно unknown ;
• условие x comp_op SOME S имеет значение false в том и только в том случае, когда S пусто, или значение условия x comp_op s равно false для каждой строки s, входящей в S. Условие x comp_op SOME S имеет значение true в том и только в том случае, когда значение предиката x comp_op s равно true хотя бы для одной строки s, входящей в S. В остальных случаях значение условия x comp_op SOME S равно unknown ;
• условие x comp_op ANY S эквивалентно условию x comp_op SOME S.

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

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65 
    AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS ( пример 14.21.1):

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65 
    AND EXISTS(SELECT *
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO 
        AND EMP.EMP_SAL > EMP1.EMP_SAL);

Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN ( пример 14.21.2):

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65 AND
    EMP_SAL > (SELECT MIN(EMP1.EMP_SAL)
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65 AND
    EMP_NAME = SOME (SELECT EMP1.EMP_NAME
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO
        AND EMP.EMP_NO <> EMP1.EMP_NO);

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

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65 AND
    EMP_NAME IN (SELECT EMP1.EMP_NAME
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO
        AND EMP.EMP_NO <> EMP1.EMP_NO);

Возможна формулировка с использованием агрегатной функции COUNT ( пример 14.22.2):

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65 AND
    (SELECT COUNT(*)
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO
        AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;

Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения ( пример 14.22.3):

SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME
FROM EMP, EMP EMP1
WHERE EMP.DEPT_NO = 65
    AND EMP.EMP_NAME = EMP1.EMP_NAME
    AND EMP.DEPT_NO = EMP1.DEPT_NO
    AND EMP.EMP_NO <> EMP1.EMP_NO;

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

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL
    FROM EMP EMP1
    WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS ( пример 14.23.1):

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65 
 AND NOT EXISTS (SELECT *
    FROM EMP EMP1
    WHERE EMP.DEPT_NO = EMP1.DEPT_NO
    AND EMP.EMP_SAL < EMP1.EMP_SAL);

Можно сформулировать этот же запрос с использованием агрегатной функции MAX ( пример 14.23.2):

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL)
    FROM EMP EMP1
    WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME
    FROM EMP EMP1
    WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 14.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса

SELECT DISTINCT EMP_NO, EMP_NAME
FROM EMP, EMP EMP1
WHERE EMP.EMP_NAME <> EMP1.EMP_NAME
    AND EMP1.EMP_NO <> EMP.EMP_NO);

эквивалентна формулировке

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME
    FROM EMP EMP1
    WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Очевидно, что этот запрос является бессмысленным ("Найти служащих, для которых имеется хотя бы один не однофамилец").