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

Александра Каева
Александра Каева
Валерий Ларионов
Валерий Ларионов
Россия, Саранск, Мордовский государственный университет им. Огарева, 2006
Анна Назарова
Анна Назарова
Россия