Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 8464 / 634 | Оценка: 3.85 / 3.50 | Длительность: 22:03:00
Лекция 4:

Предикаты раздела 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);
14.21. Найти номера служащих отдела номер 65, зарплата которых в этом отделе не является минимальной.

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката 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);
14.21.1.

Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции 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);
14.21.2.
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. Найти номера и имена служащих отдела 65, однофамильцы которых работают в этом же отделе.

Заметим, что эта формулировка эквивалентна следующей формулировке ( пример 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);
14.22.1.

Возможна формулировка с использованием агрегатной функции 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.2.

Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения ( пример 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;
14.22.3.

В последней формулировке мы вынуждены везде использовать уточненные имена столбцов, потому что на одном уровне используются два вхождения таблицы 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);
14.23. Найти номера служащих отдела номер 65, зарплата которых в этом отделе является максимальной.

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката 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);
14.23.1.

Можно сформулировать этот же запрос с использованием агрегатной функции 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);
14.23.2.
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);
14.24. Найти номера и имена служащих, не имеющих однофамильцев

Этот запрос можно переформулировать на основе использования предиката 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);

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

Алексей Ковтун
Алексей Ковтун

При попытке исполнения запроса:

CREATE DOMAIN EMP_NO AS INTEGER

    CHECK (VALUE BETWEEN 1 AND 10000);

Выдается ошибка: Неизвестный тип объекта "DOMAIN" в интсрукции CREATE, DROP или ALTER. 

Используется SQL Server MS SQL 2008R2

Александра Каева
Александра Каева