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

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

Предикат 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 некоторыми уточнениями.

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

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

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

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

  • либо неизвестны номер отдела или дата рождения (или и то, и другое);
  • либо в отделе данного служащего работает еще один человек с той же датой рождения.
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);
14.26.

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

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

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

  • либо неизвестны номер отдела и дата рождения;
  • либо неизвестен номер отдела, но имеется еще один человек с той же датой рождения;
  • либо неизвестна дата рождения, но в отделе данного служащего работает еще один человек;
  • либо известны и номер отдела, и дата рождения, и в отделе данного служащего работает еще один человек с той же датой рождения.
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);
14.27.

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

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

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

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

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

Заключение

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

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

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

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

CREATE DOMAIN EMP_NO AS INTEGER

    CHECK (VALUE BETWEEN 1 AND 10000);

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

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

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