Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 7969 / 391 | Оценка: 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);

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

Александра Каева
Александра Каева
Антон Честиков
Антон Честиков

Не вижу список тем из плана занятий. Проблема известна?

Александр Хребтов
Александр Хребтов
Россия, г.Курган
Александр Стулов
Александр Стулов
Россия, Москва, МИФИ