2008 г.

Базы данных. Вводный курс

Сергей Кузнецов

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

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

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.

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

Пример 18.21. Найти номера служащих отдела номер 65, зарплата которых в этом отделе не является минимальной.

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 (пример 18.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 (пример 18.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);

Пример 18.22. Найти номера и имена служащих отдела 65, однофамильцы которых работают в этом же отделе.

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);

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

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

Пример 18.23. Найти номера служащих отдела номер 65, зарплата которых в этом отделе является максимальной.

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 (пример 18.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 (пример 18.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);

Пример 18.24. Найти номера и имена служащих, не имеющих однофамильцев.

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 (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 18.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);

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

18.2.11. Предикат match

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

match_predicate ::= row_value_constructor 
    MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] 
      query_expression

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

Пусть x обозначает строку-первый операнд. Тогда:

Если отсутствует спецификация вида сопоставления или специфицирован тип сопоставления SIMPLE, то:
- если значение некоторого столбца x является неопределенным, то значением условия является true;
- если в x нет неопределенных значений, то:
  - если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s в такая, что x = s, то значением условия является true;
  - если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значением условия является true;
  - в противном случае значением условия является false.
Если в условии присутствует спецификация PARTIAL, то:
- если все значения в x являются неопределенными, то значение условия есть true;
- иначе:
  - если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;
  - если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;
  - в противном случае значение условия есть false.
Если в условии присутствует спецификация FULL, то:
- если все значения в x неопределенные, то значение условия есть true;
- если ни одно значение в x не является неопределенным, то:
  - если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что x = s, то значение условия есть true;
  - если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значение условия есть true;
  - в противном случае значение условия есть false.
- в противном случае значение условия есть false.

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

Все примеры этого пункта основаны на запросе «Найти номера служащих и номера их отделов для служащих, для которых в отделе со «схожим» номером работает служащий со «схожей» датой рождения» c некоторыми уточнениями.

Пример 18.25

SELECT EMP_NO, DEPT_NO
FROM EMP
WHERE (DEPT_NO, EMP_BDATE) MATCH SIMPLE
    (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE 
      FROM EMP EMP1
    WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Этот запрос вернет данные о служащих, про которых:

либо неизвестны номер отдела или дата рождения (или и то, и другое);
либо в отделе данного служащего работает по крайней мере еще один человек с той же датой рождения.

Если использовать предикат MATCH UNIQUE SIMPLE, то мы получим данные о служащих, про которых:

либо неизвестны номер отдела или дата рождения (или и то, и другое);
либо в отделе данного служащего работает еще один человек с той же датой рождения.

Пример 18.26

SELECT EMP_NO, DEPT_NO
FROM EMP
WHERE (DEPT_NO, EMP_BDATE) MATCH PARTIAL
    (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE 
        FROM EMP EMP1
        WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Этот запрос вернет данные о служащих, про которых:

либо неизвестны номер отдела и дата рождения;
либо неизвестен номер отдела, но имеется по крайней мере еще один человек с той же датой рождения;
либо неизвестна дата рождения, но в отделе данного служащего работает по крайней мере еще один человек;
либо известны и номер отдела, и дата рождения, и в отделе данного служащего работает по крайней мере еще один человек с той же датой рождения.

Если использовать предикат MATCH UNIQUE PARTIAL, то мы получим данные о служащих, про которых:

либо неизвестны номер отдела и дата рождения;
либо неизвестен номер отдела, но имеется еще один человек с той же датой рождения;
либо неизвестна дата рождения, но в отделе данного служащего работает еще один человек;
либо известны и номер отдела, и дата рождения, и в отделе данного служащего работает еще один человек с той же датой рождения.

Пример 18.27

SELECT EMP_NO, DEPT_NO
FROM EMP
WHERE (DEPT_NO, EMP_BDATE) MATCH UNIQUE FULL
    (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE 
      FROM EMP EMP1
      WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Этот запрос вернет данные о служащих, о которых:

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

Если использовать предикат MATCH UNIQUE FULL, то мы получим данные о служащих, о которых:

либо неизвестны номер отдела и дата рождения;
либо в отделе данного служащего работает еще один человек с той же датой рождения.

18.2.12. Предикат is distinct

Предикат позволяет проверить, являются ли две строки дубликатами. Условие определяется следующим синтаксическим правилом:

distinct_predicate ::= row_value_constructor IS DISTINCT FROM
    row_value_constructor

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

Напомним, что две строки s1 с именами столбцов c1, c2, …, cn и s2 с именами столбцов d1, d2, …, dn считаются строками-дубликатами, если для каждого i ( i = 1, 2, …, n ) либо ci и di не содержат NULL, и (ci = di) = true, либо и ci, и di содержат NULL. Значением условия s1 IS DISTINCT FROM s2 является true в том и только в том случае, когда строки s1 и s2 не являются дубликатами. В противном случае значением условия является false.

Заметим, что отрицательная форма условия – IS NOT DISTINCT FROM – в стандарте SQL не поддерживается. Вместо этого можно воспользоваться выражением NOT s1 IS DISTINCT FROM s2.

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

Пример 18.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65.

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65
    AND (EMP_NAME, EMP_BDATE) IS DISTINCT FROM
      (SELECT EMP1.EMP_NAME, EMP1.EMP_BDATE
        FROM EMP EMP1, DEPT
        WHERE EMP1.DEPT_NO = EMP.DEPT_NO
          AND DEPT.DEPT_MNG = EMP1.EMP_NO);

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

SELECT EMP1.EMP_NO, EMP2.EMP_NO
FROM EMP EMP1, EMP EMP2
WHERE DEPT_NO = 65
    AND EMP1.EMP_NO <> EMP2.EMP_NO 
    AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) IS DISTINCT FROM
            (EMP2.EMP_NAME, EMP2.EMP_BDATE));

18.3. Заключение

В этой лекции мы обсудили наиболее важные возможности языка SQL, связанные с выборкой данных. Даже простые примеры, приводившиеся в лекции, показывают исключительную избыточность языка SQL. Еще в то время, когда действующим стандартом языка был SQL/92, была опубликована любопытная статья, в которой приводилось 25 формулировок одного и того же несложного запроса. При использовании всех возможностей SQL:1999 этих формулировок было бы гораздо больше.

Можно спорить, хорошо или плохо иметь возможность формулировать один и тот же запрос десятками разных способов. На мой взгляд, это не очень хорошо, поскольку увеличивает вероятность появления ошибок в запросах (особенно в сложных запросах). С другой стороны, таково объективное состояние дел, и мы стремились обеспечить в этой лекции материал, достаточный для того, чтобы прочувствовать различные возможности формулировки запросов. Как показывают следующие две лекции, возможности, предоставляемые оператором SELECT, в действительности гораздо шире.

Назад Содержание Вперёд