Предикаты раздела 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);

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

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

Если использовать предикат 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);

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

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

Если использовать предикат 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);

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

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

Если использовать предикат 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);

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

Заключение

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

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