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

Разбить на страницы

Показывать лекцию целиком

Введение

Конструкции оператора SELECT языка SQL в значительной степени ортогональны. В частности, выбор способа указания ссылки на таблицы в разделе FROM никак не влияет на выбор варианта формирования условия выборки в разделе WHERE. Это полезное свойство языка позволяет нам абстрагироваться от обсуждавшегося в предыдущей лекции многообразия способов указания ссылки на таблицу и сосредоточиться на возможностях формирования запросов при использовании различных предикатов, допускаемых стандартом SQL:1999 в логических выражениях раздела WHERE.

В стандарте SQL:1999 специфицированы 12 разновидностей предикатов, причем некоторые из них в действительности представляют собой семейства (например, под общим названием предиката сравнения скрываются шесть видов предикатов ). Набор допустимых предикатов в SQL явно избыточен, но тем не менее в языке SQL имеется явная тенденция расширения этого набора. В частности, в SQL:2003 в связи с введением генератора типов мультимножеств в дополнение ко всем разновидностям предикатов SQL:1999 появилось три новых вида предикатов: предикаты для проверки того, что заданное значение является элементом мультимножества ( MEMBER ); что одно мультимножество входит в другое мультимножество ( SUBMULTISET ) и что мультимножество не содержит дубликаты ( IS A SET ). В этом курсе мы не приводим подробного описания этих видов предикатов по нескольким причинам:

введение конструктора типов мультимножеств в стандарте SQL:2003 не означает, что достигнута общая цель разработчиков стандарта SQL по обеспечению полного набора типов коллекций; по всей видимости, в будущих версиях стандарта появятся дополнительные конструкторы типов коллекций, и набор видов предикатов изменится;

предикаты с мультимножествами трудно пояснять и иллюстрировать в отрыве от других объектно-реляционных средств языка SQL;

включение подобного материала в данную лекцию заметно увеличило бы ее объем и затруднило понимание более традиционных конструкций.

В лекции содержится много примеров запросов с использованием различных видов предикатов. Для полного усвоения материала требуется тщательно проанализировать эти примеры.

Логические выражения раздела WHERE

Синтаксически логическое выражение раздела WHERE определяется как булевское выражение ( Мы не обсуждаем в этом курсе предикаты, основанные на использовании выражений типа мультимножества, которые были введены в стандарте SQL:2003.

predicate ::= comparison_predicate
    | between_predicate
    | null_predicate 
    | in_predicate 
    | like_predicate
    | similar_predicate
    | exists_predicate 
    | unique_predicate 
    | overlaps_predicate 
    | quantified_comparison_predicate 
    | match_predicate 
    | distinct_predicate

Далее мы будем последовательно обсуждать разные виды предикатов и приводить примеры запросов с использованием базы данных СЛУЖАЩИЕ-ОТДЕЛЫ-ПРОЕКТЫ, определения таблиц которой на языке SQL были приведены в лекции 2. Для удобства повторим здесь структуру таблиц.

EMP:
`EMP_NO : EMP_NO`
`EMP_NAME : VARCHAR`
`EMP_BDATE : DATE`
`EMP_SAL : SALARY`
`DEPT_NO : DEPT_NO`
`PRO_NO : PRO_NO`

DEPT:
`DEPT_NO : DEPT_NO`
`DEPT_NAME : VARCHAR`
`DEPT_EMP_NO : INTEGER`
`DEPT_TOTAL_SAL : SALARY`
`DEPT_MNG : EMP_NO`

PRO:
`PRO_NO : PRO_NO`
`PRO_TITLE : VARCHAR`
`PRO_SDATE : DATEP`
`PRO_DURAT : INTERVAL`
`PRO_MNG : EMP_NO`
`PRO_DESC : CLOB`

Столбцы EMP_NO, DEPT_NO и PRO_NO являются первичными ключами таблиц EMP, DEPT и PRO соответственно. Столбцы DEPT_NO и PRO_NO таблицы EMP являются внешними ключами, ссылающимися на таблицы DEPT и PRO соответственно ( DEPT_NO указывает на отделы, в которых работают служащие, а PRO_NO - на проекты, в которых они участвуют; оба столбца могут принимать неопределенные значения). Столбец DEPT_MNG является внешним ключом таблицы DEPT ( DEPT_MNG указывает на служащих, которые исполняют обязанности руководителей отделов; у отдела может не быть руководителя, и один служащий не может быть руководителем двух или более отделов). Столбец PRO_MNG является внешним ключом таблицы PRO ( PRO_MNG указывает на служащих, которые являются менеджерами проектов, у проекта всегда есть менеджер, и один служащий не может быть менеджером двух или более проектов).

Предикат сравнения

Этот предикат предназначен для спецификации сравнения двух строчных значений. Синтаксис предиката следующий:

comparison_predicate ::= 
    row_value_constructor comp_op row_value_constructor
comp_op ::= = | <> ("неравно")| < | > 
    | <= "меньше или равно"| >= "больше или равно"

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

Пусть X и Y обозначают соответствующие элементы строк-операндов, а xv и yv - их значения. Тогда:

если xv и/или yv являются неопределенными значениями, то значение условия X comp_op Y -unknown ;

в противном случае значением условия X comp_op Y является true или false в соответствии с естественными правилами применения операции сравнения.

При этом:

Числа сравниваются в соответствии с правилами алгебры.

Сравнение двух символьных строк производится следующим образом:

если длина строки X не равна длине строки Y, то для выравнивания длин строк более короткая строка расширяется символами набивки ( pad symbol ); если для используемого набора символов порядок сортировки явным образом не специфицирован, то в качестве символа набивки используется пробел;

далее производится лексикографическое сравнение строк в соответствии с предопределенным или явно определенным порядком сортировки символов.

Сравнение двух битовых строк X и Y основано на сравнении соответствующих бит. Если X_i и Y_i - значения i -тых бит X и Y соответственно и если lx и ly обозначает длину в битах X и Y соответственно, то:

X равно Y тогда и только тогда, когда lx = ly и X_i = Y_i для всех i ;

X меньше Y тогда и только тогда, когда (a) lx < ly и X_i = Y_i для всех i меньших или равных lx, или (b) X_i = Y_i для всех i < n и X_n = 0, а Y_n =1 для некоторого n меньшего или равного min (lx, ly).

Сравнение двух значений типа дата-время производится в соответствии с видом интервала, который получается при вычитании второго значения из первого. Пусть X и Y - сравниваемые значения, а H - наименее значимое поле даты-времени X и Y. Результат сравнения X comp_op Y определяется как (X - Y) H comp_ op INTERVAL (0) H. (Два значения типа дата-время сравнимы только в том случае, если они содержат одинаковый набор полей даты-времени.)

Сравнение двух значений анонимного строкового типа производится следующим образом. Пусть Rx и Ry обозначают строки-операнды, а Rx_i и Ry_i - i -тые элементы Rx и Ry соответственно. Вот как определяется результат сравнения Rx comp_op Ry:

Rx = Ry есть true тогда и только тогда, когда Rx_i = Ry_i есть true для всех i ;

Rx <> Ry есть true тогда и только тогда, когда Rx_i <> Ry_i есть true для некоторого i ;

Rx < Ry есть true тогда и только тогда, когда Rx_i = Ry_i есть true для всех i < n, и Rx_n < Ry_n есть true для некоторого n ;

Rx > Ry есть true тогда и только тогда, когда Rx_i = Ry_i есть true для всех i < n, и Rx_n > Ry_n есть true для некоторого n ;

Rx <= Ry есть true тогда и только тогда, когда Rx = Ry есть true или Rx < Ry есть true ;

Rx >= Ry есть true тогда и только тогда, когда Rx = Ry есть true или Rx > Ry есть true ;

Rx = Ry есть false тогда и только тогда, когда Rx <> Ry есть true ;

Rx <> Ry есть false тогда и только тогда, когда Rx = Ry есть true ;

Rx < Ry есть false тогда и только тогда, когда Rx >= Ry есть true ;

Rx > Ry есть false тогда и только тогда, когда Rx <= Ry есть true ;

Rx <= Ry есть false тогда и только тогда, когда Rx > Ry есть true ;

Rx >= Ry есть false тогда и только тогда, когда Rx < Ry есть true ;

Rx comp_op Ry есть unknown тогда и только тогда, когда Rx comp_op Ry не есть true или false.

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

SELECT DISTINCT EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_NAME = 'Smith';

Мы добавили спецификацию ):

SELECT EMP.DEPT_NO, COUNT(*)
FROM EMP
WHERE EMP.NAME = 'Smith'
GROUP BY EMP.DEPT_NO;

В этом варианте запроса спецификация DISTINCT не требуется, поскольку в запросе содержится раздел GROUP BY, группировка производится в соответствии со значениями столбца EMP.DEPT_NO, и строка результата соответствует одной группе.

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_BDATE > DATE '1965-04-15';

В результате этого запроса дубликатов быть не может, поскольку в список выборки включен столбец, являющийся первичным ключом таблицы EMP. Должно быть ясно, что по этой причине все строки результата будут различными.

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_SAL > 0.1 *
    (SELECT DEPT_TOTAL_SAL
    FROM DEPT
    WHERE DEPT.DEPT_NO = EMP.DEPT_NO);

В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение раздела WHERE этого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицы DEPT. Во-вторых, в условии раздела WHERE подзапроса используется ссылка на столбец таблицы EMP, указанной в разделе FROM "внешнего" запроса. Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образомЗдесь снова идет речь о семантике выполнения оператора SELECT. В стандарте, естественно, не требуется, чтобы в реализации языка запросы с корреляционными подзапросами выполнялись в точности так, как описывается ниже. Суть в том, что какой бы реальный алгоритм выполнения такого запроса не использовался, результат выполнения должен быть точно таким же, как если бы запрос выполнялся по описываемой схеме..

При выполнении внешнего запроса последовательно, строка за строкой, в некотором порядке, определяемом системой, производится проверка соответствия строк результирующей таблицы раздела FROM условию раздела WHERE. Если это условие включает корреляционные подзапросы, то внутри каждого из этих подзапросов ссылка на столбец внешней таблицы трактуется как ссылка на столбец текущей строки данной таблицы во внешнем цикле. Естественно, условие WHERE любого подзапроса может включать более глубоко вложенные подзапросы, на которые распространяется то же правило корреляции с внешними таблицами.

Кстати, эквивалентная формулировка на языке SQL примера 14.3 выглядит следующим образом ():

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND 
   EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;

Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL ) эквисоединения таблиц EMP и DEPT (по условию EMP.DEPT_NO = DEPT.DEPT_NO ). Подобную операцию часто называют полусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.

SELECT EMP1.EMP_NO, EMP1.EMP_NAME, 
       EMP1.DEPT_NO, EMP2.EMP_NAME
FROM EMP AS EMP1, EMP AS EMP2, DEPT
WHERE EMP1.EMP_SAL < 15000.00 AND
    EMP1.DEPT_NO = DEPT.DEPT_NO AND 
    DEPT.DEPT_MNG = EMP2.EMP_NO;

Этот запрос представляет собой эквисоединение ограничения таблицы Кстати, в этом случае можно было бы обойтись введением одного псевдонима, оставив в качестве неявного второго псевдонима имя таблицы - EMP . Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением ), поскольку в списке выборки присутствуют имена столбцов каждой из них.

Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки ():

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, 
    (SELECT EMP_NAME
      FROM EMP
      WHERE EMP_NO = DEPT_MNG)
FROM EMP, DEPT
WHERE EMP.EMP_SAL < 15000.00 AND
    EMP.DEPT_NO = DEPT.DEPT_NO;

Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в разделе "Общие синтаксические правила построения скалярных выражений" предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: "После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 или T3 соответственно". В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.

Предикат between

Предикат позволяет специфицировать условие вхождения в диапазон значений. Операндами являются строки:

between_predicate ::=
    row_value_constructor [ NOT ] BETWEEN
    row_value_constructor AND row_value_constructor

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

Пусть X, Y и Z обозначают первый, второй и третий операнды. Тогда по определению выражение X NOT BETWEEN Y AND Z эквивалентно выражению NOT (X BETWEEN Y AND Z). Выражение X BETWEEN Y AND Z по определению эквивалентно булевскому выражению X >= Y AND X <= Z.

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

SELECT EMP_NO, EMP_NAME, EMP_SAL 
FROM EMP
WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;

SELECT EMP_NO, EMP_NAME, EMP_SAL 
FROM EMP
WHERE EMP_SAL BETWEEN 
    (SELECT AVG(EMP1.EMP_SAL)
      FROM EMP EMP1
        WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
          AND
    (SELECT EMP1.EMP_SAL 
      FROM EMP EMP1
        WHERE EMP1.EMP_NO =
          (SELECT DEPT.DEPT_MNG
            FROM DEPT
            WHERE DEPT.DEPT_NO = EMP.DEPT_NO));

В этом запросе можно выделить три интересных момента. Во-первых, диапазон значений предиката BETWEEN задан двумя подзапросами, результатом каждого из которых является единственное значение. Первый подзапрос выдает единственное значение, поскольку в списке выборки содержится агрегатная функция ( AVG ) и отсутствует раздел GROUP BY, а второй - потому что в его разделе WHERE присутствует условие, задающее единственное значение первичного ключа. Во-вторых, в обоих подзапросах таблица EMP получает псевдоним EMP1 (в формулировке этого запроса мы старались использовать как можно меньше вспомогательных идентификаторов). Поскольку подзапросы выполняются независимо один от другого, использование общего имени не вызывает проблем. Наконец, в условии второго подзапроса присутствует более глубоко вложенный подзапрос, и в условии его раздела WHERE используется ссылка на столбец таблицы из самого внешнего раздела FROM.

Предикат is null

Предикат is null позволяет проверить, являются ли неопределенными значения всех элементов строки-операнда:

null_predicate ::= row_value_constructor IS [ NOT ] NULL

Пусть X обозначает строку-операнд. Если значения всех элементов X являются неопределенными, то значением условия X IS NULL является true ; иначе - false. Если ни у одного элемента X значение не является неопределенным, то значением условия X IS NOT NULL является true ; иначе - false.

Замечание: условие X IS NOT NULL имеет то же значение, что условие NOT X IS NULL для любого X в том и только в том случае, когда степень X равна 1. Полная семантика предиката null приведена в таблице 14.1.


Вид операнда	Вид условия
Вид операнда	`X IS X NULL`	`IS NOT NULL`	`NOT X IS NULL`	`NOT X IS NOT NULL`
Степень 1: значение `NULL`	`true`	`false`	`false`	`true`
Степень 1: значение отлично от `NULL`	`false`	`true`	`true`	`false`
Степень > 1: у всех элементов значение `NULL`	`true`	`false`	`false`	`true`
Степень > 1: у некоторых(не у всех) элементов значение `NULL`	`false`	`false`	`true`	`true`
Степень > 1: ни у одного элемента нет значения `NULL`	`false`	`true`	`true`	`false`

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

На самом деле, в нашей формулировке запроса из примера 14.6 есть одна неточность. Если у некоторого служащего номер отдела неизвестен (значение столбца EMP.DEPT_NO у соответствующей строки таблицы служащих является неопределенным), то бессмысленно вычислять средний размер зарплаты отдела этого служащего и находить размер зарплаты руководителя отдела. Формулировка из примера 14.6 приведет к правильному результату, но это неочевидноПокажем это в развернутой форме. Пусть s - текущая строка таблицы EMP, просматриваемой в цикле внешнего запроса, и пусть s.DEPT_NO содержит неопределенное значение. Тогда для строки s условие первого подзапроса будет иметь вид NULL = EMP1.DEPT_NO, и значением этого условия будет unknown для любой строки таблицы EMP ( EMP1 ), просматриваемой в цикле этого подзапроса. Поскольку unknown не является разрешающим условием, результирующая таблица подзапроса будет пуста, и агрегатная функция AVG выдаст значение NULL. По этому поводу значением условия внешнего запроса будет unknown, и строка s не войдет в результирующую таблицу. Чтобы сделать формулировку более понятной (и, возможно, помочь системе выполнить запрос более эффективно), нужно воспользоваться предикатом IS NOT NULL и переписать запрос следующим образом:

SELECT EMP_NO, EMP_NAME, EMP_SAL 
FROM EMP
WHERE DEPT_NO IS NOT NULL AND
    EMP_SAL BETWEEN 
    (SELECT AVG(EMP1.EMP_SAL)
      FROM EMP EMP1
      WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
        AND
    (SELECT EMP1.EMP_SAL 
      FROM EMP EMP1
      WHERE EMP1.EMP_NO =
        ( SELECT DEPT.DEPT_MNG
        FROM DEPT
        WHERE DEPT.DEPT_NO = EMP.DEPT_NO ) );

SELECT EMP_NO, EMP_NAME 
FROM EMP
WHERE DEPT_NO IS NULL;

Предикат in

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

in_predicate ::= row_value_constructor [ NOT ]
    IN in_predicate_value
in_predicate_value ::= table_subquery
    | (value_expression_comma_list)

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

Пусть X обозначает строку-первый операнд, а S - множество строк второго операнда. Обозначим через s строку-элемент этого множества. Тогда по определению условие X IN S эквивалентно булевскому выражению OR_sS (X = s). Другими словами, X IN S принимает значение true в том и только в том случае, когда во множестве S существует хотя бы один элемент s, такой, что значением предиката X = s является true. X IN S принимает значение false в том и только том случае, когда для всех элементов s множества S значением операции сравнения X = s является false. Иначе значением условия X IN S является unknown. Заметим, что для пустого множества S значением X IN S является false.

По определению условие X NOT IN S эквивалентно NOT (X IN S).

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

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMP
WHERE DEPT_NO IN (15, 17, 19);

Конечно, эта формулировка запроса эквивалентна следующей формулировке ():

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMP
WHERE DEPT_NO = 15
  OR DEPT_NO = 17
  OR DEPT_NO = 19;

SELECT EMP_NO 
FROM EMP
WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT)
    AND EMP_SAL IN (SELECT EMP_SAL FROM EMP, 
      DEPT WHERE EMP_NO = DEPT_MNG);

Запросы, содержащие предикат ):

SELECT DISTINCT EMP_NO 
FROM EMP, EMP EMP1, DEPT
WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT)
 AND EMP_SAL = EMP1_SAL
 AND EMP1.EMP_NO = DEPT.DEPT_MNG;

По поводу этой второй формулировки следует сделать два замечания. Во-первых, как видно, мы изменили только ту часть условия, в которой использовался предикат IN , и не затронули предикат NOT IN. Запросы с предикатами NOT IN запросами с соединениями так просто не заменяются. Во-вторых, в разделе SELECT было добавлено ключевое слово DISTINCT, потому что в результате запроса во второй формулировке для каждого служащего будет содержаться столько строк, сколько существует руководителей отделов, получающих такую же зарплату, что и данный служащий.

Предикат like

Формально предикат like определяется следующими синтаксическими правилами:

like_predicate ::= source_value [ NOT ] 
    LIKE pattern_value [ ESCAPE escape_value ]
source_value ::= value_expression 
pattern_value ::= value_expression
escape_value ::= value_expression

Все три операнда ( source_value, pattern_value и escape_value ) должны быть одного типа: либо типа символьных строк, либо типа битовых строкВ стандарте SQL:1999 разрешается применять предикат LIKE только для битовых строк типа BLOB. Битовые строки типов BIT и BIT VARYING не допускаются.. В первом случае значением последнего операнда должна быть строка из одного символа, во втором - строка из 8 бит. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда исходная строка ( source_value ) может быть сопоставлена с заданным шаблоном ( pattern_value ).

Если обрабатываются символьные строки, и если раздел ESCAPE условия отсутствует, то при сопоставлении шаблона со строкой производится специальная интерпретация двух символов шаблона: символ подчеркивания (' _ ') обозначает любой одиночный символ; символ процента (' % ') обозначает последовательность произвольных символов произвольной длины (длина последовательности может быть нулевой). Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов " x_ " и " x% " представляют одиночные символы " _ " и " % " соответственно.

В случае обработки битовых строк сопоставление шаблона со строкой производится восьмерками соседних бит ( октетами ). В соответствии со стандартом SQL:1999, при сопоставлении шаблона со строкой производится специальная интерпретация октетов со значениями X'25' и X'5F' (коды символов подчеркивания и процента в кодировке ASCII). Первый октет обозначает любой одиночный октет, а второй - последовательность произвольной длины произвольных октетов (длина может быть нулевой). В разделе ESCAPE указывается октет, отменяющий специальную интерпретацию октетов X'25' и X'5F'.

Значение предиката like есть unknown, если значение первого или второго операндов является неопределенным. Условие x NOT LIKE y ESCAPE z эквивалентно условию NOT x LIKE y ESCAPE z.

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

SELECT PRO_TITLE
FROM PRO
WHERE PRO_TITLE LIKE '%next%step%'
    OR PRO_TITLE LIKE 'Next%step%';

Это очень неудачный запрос, потому что его выполнение, скорее всего, вынудит СУБД просмотреть все строки таблицы ):

SELECT PRO_TITLE
FROM PRO
WHERE PRO_TITLE LIKE '%ext%step%';

SELECT DISTINCT DEPT.DEPT_NO
FROM EMP, DEPT, PRO
WHERE EMP.EMP_NO = PRO.PRO_MNG
 AND EMP.DEPT_NO = DEPT.DEPT_NO 
 AND PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%';

Вот как может выглядеть формулировка этого запроса, если использовать вложенные подзапросы ():

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE DEPT.DEPT_NO IN
    (SELECT EMP.DEPT_NO
    FROM EMP 
    WHERE EMP.EMP_NO IN 
      (SELECT PRO.PRO_MNG FROM PRO
    WHERE PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%'));

SELECT DEPT_NO
FROM DEPT
WHERE DEPT_NAME NOT LIKE 'Software%';

Предикат similar

Формально предикат similar определяется следующими синтаксическими правилами:

similar_predicate ::= source_value [ NOT ] 
    SIMILAR TO pattern_value [ ESCAPE escape_value ]
source_value ::= character_expression 
pattern_value ::= character_expression
escape_value ::= character_expression

Все три операнда ( source_value, pattern_value и escape_value ) должны иметь тип символьных строк. Значением последнего операнда должна быть строка из одного символа. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда шаблон ( pattern_value ) должным образом сопоставляется с исходной строкой ( source_value ).

Основное отличие предиката similar от рассмотренного ранее предиката like состоит в существенно расширенных возможностях задания шаблона, основанных на использовании правил построения регулярных выражений. Регулярные выражения предиката similar определяются следующими синтаксическими правилами:

regular_expression ::= regular_term
    | regular_expression vertical_bar regular_term
regular_term ::= regular_factor 
    | regular_term regular_factor
regular_factor ::= regular_primary
    | regular_primary *
    | regular_primary +
regular_primary ::= character_specifier
    | %
    | regular_character_set
    | ( regular_expression )
character_specifier ::= non_escape_character 
    | escape_character
regular_character_set ::= _
    | left_bracket 
        character_enumeration_list right_bracket 
    | left_bracket 
        ^ character_enumeration_list right_bracket
    | left_bracket : regular_charset_id : right_bracket
character_enumeration ::= character_specifier
    | character_specifier - character_specifier
regular_charset_id ::= ALPHA | UPPER | LOWER 
    | DIGIT | ALNUM

Поскольку в синтаксических правилах регулярных выражений символы " | ", " [ " и " ] ", используемые нами в качестве метасимволов в BNF, являются терминальными символами, они изображены как vertical_bar, left_bracket и right_bracket соответственно.

Создаваемое по приведенным правилам регулярное выражение представляет собой символьную строку, содержащую все символы, которые требуется явно сопоставлять с символами строки-источника. В строке могут находиться специальные символы, представляющие собой заменители обычных символов (" % " и " _ "), обозначения операций (" | "), показатели числа возможных повторений (" * " и " + ") и т. д. При вычислении регулярного выражения образуются все возможные символьные строки, не содержащие специальных символов и соответствующие исходному шаблону. Тем самым, значением предиката similar является true в том и только в том случае, когда среди всех символьных строк, генерируемых по регулярному выражению pattern_value, найдется символьная строка, совпадающая с source_value.

Рассмотрим несколько примеров регулярных выражений.

Выражение '(This is string1)|(This is string2)' производит две символьные строки: '(This is string1)' и '(This is string2)'. В общем случае в круглых скобках могут находиться произвольные регулярные выражения rexp1 и rexp2. Результатом вычисления '(rexp1)|(rexp2)' является множество символьных строк, генерируемых выражением rexp1, объединенное с множеством символьных строк, генерируемых выражением rexp2.

Выражение 'This is string [12]*' генерирует символьные строки 'This is string ', 'This is string 1', 'This is string 2', 'This is string 11', 'This is string 22', 'This is string 12', 'This is string 21', 'This is string 111' и т. д. Конструкция в квадратных скобках представляет собой один из вариантов определения набора символов ( regular_character_set ). В данном случае символы, входящие в определяемый набор, просто перечисляются. При вычислении регулярного выражения в каждой из генерируемых символьных строк конструкция в квадратных скобках заменяется одним из символов соответствующего набора.

Специальный символ " * ", стоящий после закрывающей квадратной скобки, является показателем числа повторений. "Звездочка" означает, что в генерируемых символьных строках элемент регулярного выражения, непосредственно предшествующий "звездочке", может появляться ноль или более раз. Использование в такой же ситуации специального символа " + " означает, что в генерируемых символьных строках элемент регулярного выражения, непосредственно предшествующий символу "плюс", может появляться один или более раз.

Другая форма определения набора символов иллюстрируется регулярным выражением 'This is string [:DIGIT:]'. В этом случае конструкция в квадратных скобках представляет любой одиночный символ, изображающий десятичную цифру. Другими допустимыми в SQL идентификаторами наборов символов (regular_charset_id) являются ALPHA (любой символ алфавита), UPPER (любой символ верхнего регистра), LOWER (любой символ нижнего регистра) и ALNUM (любой алфавитно-цифровой символ).

Определяемый набор символов может задаваться нижней и верхней границей диапазона значений кодов допустимых символов. Например, в регулярном выражении 'This is string [3-8]' конструкция в квадратных скобках представляет собой любой одиночный символ, изображающий цифры от 3 до 8 включительно. Заметим, что при задании диапазона можно использовать любые символы, но требуется, чтобы значение кода символа левой границы диапазона было не больше значения кода символа правой границы.

Наконец, имеется еще одна возможность определения набора символов. Соответствующая конструкция позволяет указать, какие символы из общего набора символов SQL не входят в определяемый набор символов. Например, регулярное выражение '_S[^t]*ing%' генерирует все символьные строки, у которых вторым символом является " S ", за которым (не обязательно непосредственно) следует подстрока " ing ", но между " S " и " ing " отсутствуют вхождения символа " t ".

Как и в предикате like , символ, определенный в разделе ESCAPE , поставленный перед любым специальным символом, отменяет специальную интерпретацию этого символа.

В заключение данного пункта вернемся к отложенному в разделе "Скалярные выражения" лекции 13 обсуждению функции SUBSTRING ... SIMILAR ... ESCAPE . Напомним, что вызов этой функции определяется следующим синтаксисом:

SUBSTRING (character_value_expression
    SIMILAR character_value_expression
      ESCAPE character_value_expression)

Предположим, что в разделе ESCAPE (который должен присутствовать обязательно) задан символ " x ". Тогда символьная строка, задаваемая во втором операнде, должна иметь вид 'rexp1x"rexp2x"rexp3', где rexp1, rexp2 и rexp3 являются регулярными выражениями. Функция пытается разделить символьную строку первого операнда на три раздела, первый из которых определяется путем сопоставления начала строки со строками, генерируемыми rexp1, второй - путем сопоставления оставшейся части строки первого операнда с rexp2 и третий - путем сопоставления конца этой строки с rexp3. Возвращаемым значением функции является средняя часть символьной строки первого операнда.

Вот пример вызова функции:

SUBSTRING ( 'This is string22' 
    SIMILAR 'This is\"[:ALPHA:]+\"[:DIGIT:]+'
        ESCAPE '\' )

Результатом будет строка 'string'.

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

SELECT DEPT_NAME, DEPT_NO
FROM DEPT
WHERE DEPT_NAME SIMILAR TO 
    '(HARD|SOFT)WARE%\_[:DIGIT:]+' ESCAPE '\';

SELECT DEPT_NAME, DEPT_NO
FROM DEPT
WHERE DEPT_NAME SIMILAR TO '[^1-9]+%';

Предикат exists

Предикат exists определяется следующим синтаксическим правилом:

exists_predicate ::= EXISTS (query_expression)

Значением условия EXISTS (query_expression) является true в том и только в том случае, когда мощность таблицы-результата выражения запросов больше нуля, иначе значением условия является false.

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

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
    (SELECT EMP.EMP_NO
    FROM EMP
    WHERE EMP.DEPT_NO = DEPT.DEPT_NO 
        AND EXISTS
            (SELECT PRO.PRO_MNG
            FROM PRO
            WHERE PRO.PRO_MNG = EMP.EMP_NO));

Эту формулировку можно упростить, избавившись от самого вложенного запроса ():

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
    (SELECT EMP.EMP_NO
      FROM EMP, PRO
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO
        AND PRO.PRO_MNG = EMP.EMP_NO);

Далее заметим, что по смыслу ):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
    (SELECT *
      FROM EMP, DEPT
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO
        AND PRO.PRO_MNG = EMP.EMP_NO);

Запросы с предикатом ):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT COUNT(*)
    FROM EMP, DEPT
    WHERE EMP.DEPT_NO = DEPT.DEPT_NO
     AND PRO.PRO_MNG = EMP.EMP_NO ) >= 1;

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE NOT EXISTS
    (SELECT *
      FROM EMP EMP1, EMP EMP2
      WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND
        EMP2.DEPT_NO = DEPT.DEPT_NO AND
        EMP2.EMP_SAL > EMP1.EMP_SAL);

Предикат unique

Этот предикат позволяет сформулировать условие отсутствия дубликатов в результате запроса:

unique_predicate ::= UNIQUE (query_expression)

Результатом вычисления условия UNIQUE (query_expression) является true в том и только в том случае, когда в таблице-результате выражения запросов отсутствуют какие-либо две строки, одна из которых является дубликатом другой. В противном случае значение условия есть false.

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

SELECT DEPT_NO
FROM DEPT
WHERE UNIQUE
    (SELECT EMP_NAME, EMP_BDATE
      FROM EMP
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Возможна альтернативная, но более сложная формулировка этого запроса с использованием предиката ):

SELECT DEPT_NO
FROM DEPT
WHERE NOT EXISTS
    (SELECT *
      FROM EMP, EMP EMP1
      WHERE EMP1.EMP_NO <> EMP.EMP_NO
        AND EMP.DEPT_NO = DEPT.DEPT_NO
        AND EMP1.DEPT_NO = DEPT.DEPT_NO
        AND EMP1.EMP_NAME = EMP.EMP_NAME
        AND(EMP1.EMP_BDATE = EMP.EMP_BDATE
          OR (EMP.EMP_BDATE IS NULL
          AND EMP1.EMP_BDATE IS NULL)));

Если же ограничиться требованием уникальности имен служащих, то возможна следующая формулировка ():

SELECT DEPT_NO
FROM DEPT
WHERE (SELECT COUNT (EMP_NAME)
      FROM EMP
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO) =
    (SELECT COUNT (DISTINCT EMP_NAME)
      FROM EMP
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Предикат overlaps

Этот предикат служит для проверки перекрытия во времени двух событий. Условие определяется следующим синтаксисом:

overlaps_predicate ::= row_value_constructor OVERLAPS
    row_value_constructor

Степень каждой из строк-операндов должна быть равна 2. Тип данных первого столбца каждого из операндов должен быть типом даты-времени, и типы данных первых столбцов должны быть совместимы. Тип данных второго столбца каждого из операндов должен быть типом даты-времени или интервала. При этом:

если это тип интервала, то точность типа должна быть такой, чтобы интервал можно было прибавить к значению типа дата-время первого столбца;

если это тип дата-время, то он должен быть совместим с типом данных дата-время первого столбца.

Пусть D1 и D2 - значения первого столбца первого и второго операндов соответственно. Если второй столбец первого операнда имеет тип дата-время, то пусть E1 обозначает его значение. Если второй столбец первого операнда имеет тип INTERVAL, то пусть I1 -его значение, а E1 = D1 + I1. Если D1 является неопределенным значением или если E1 < D1, то пусть S1 = E1 и T1 = D1. В противном случае, пусть S1 = D1 и T1 = E1. Аналогично определяются S2 и T2 применительно ко второму операнду. Результат условия совпадает с результатом вычисления следующего булевского выражения:

(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) 
OR 
(S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) 
OR 
(S1 = S2 AND (T1 <> T2 OR T1 = T2))

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

SELECT PRO_NO
FROM PRO
WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS
    (DATE '2000-01-15', DATE '2002-12-31');

SELECT PRO_TITLE
FROM PRO
WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS
   (CURRENT_DATE, INTERVAL '1' YEAR);

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

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

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

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката ):

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

Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции ):

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

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

Заметим, что эта формулировка эквивалентна следующей формулировке ():

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

Возможна формулировка с использованием агрегатной функции ):

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;

Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения ():

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.

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

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

Можно сформулировать этот же запрос с использованием агрегатной функции ):

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

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

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

Предикат 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.

Примеры запросов с использованием предиката 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, в действительности гораздо шире.

Страницы:

Введение

Логические выражения раздела WHERE

predicate ::= comparison_predicate
    | between_predicate
    | null_predicate 
    | in_predicate 
    | like_predicate
    | similar_predicate
    | exists_predicate 
    | unique_predicate 
    | overlaps_predicate 
    | quantified_comparison_predicate 
    | match_predicate 
    | distinct_predicate

EMP:
`EMP_NO : EMP_NO`
`EMP_NAME : VARCHAR`
`EMP_BDATE : DATE`
`EMP_SAL : SALARY`
`DEPT_NO : DEPT_NO`
`PRO_NO : PRO_NO`

DEPT:
`DEPT_NO : DEPT_NO`
`DEPT_NAME : VARCHAR`
`DEPT_EMP_NO : INTEGER`
`DEPT_TOTAL_SAL : SALARY`
`DEPT_MNG : EMP_NO`

PRO:
`PRO_NO : PRO_NO`
`PRO_TITLE : VARCHAR`
`PRO_SDATE : DATEP`
`PRO_DURAT : INTERVAL`
`PRO_MNG : EMP_NO`
`PRO_DESC : CLOB`

Предикат сравнения

comparison_predicate ::= 
    row_value_constructor comp_op row_value_constructor
comp_op ::= = | <> ("неравно")| < | > 
    | <= "меньше или равно"| >= "больше или равно"