2008 г.

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

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

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

Пример 18.9. Найти номера, имена и номера отделов служащих, работающих в отделах 15, 17 и 19.

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

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

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

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

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

Запросы, содержащие предикат IN с подзапросом, легко переформулировать в запросы с соединениями. Например, запрос из примера 18.10 эквивалентен следующему запросу с соединениями (пример 18.10.1):

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, потому что в результате запроса во второй формулировке для каждого служащего будет содержаться столько строк, сколько существует руководителей отделов, получающих такую же зарплату, что и данный служащий.

18.2.5. Предикат 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) должны быть одного типа: либо типа символьных строк, либо типа битовых строк¹³⁹⁾. В первом случае значением последнего операнда должна быть строка из одного символа, во втором – строка из 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

Пример 18.11. Найти номера проектов, в названии которых присутствуют слова 'next' и 'step'. Слова должны следовать именно в такой последовательности, но слово 'next' может быть первым в названии проекта.

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

Это очень неудачный запрос, потому что его выполнение, скорее всего, вынудит СУБД просмотреть все строки таблицы PRO и для каждой строки выполнить две проверки столбца PRO_TITLE. Можно немного улучшить формулировку с небольшим риском получить неверный ответ (пример 18.11.1):

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

Пример 18.12. Найти номера отделов, служащие которых являются менеджерами проектов, и название каждого из этих проектов начинается с названия отдела.

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 || '%';

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

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

Пример 18.13. Найти номера отделов, названия которых не начинаются со слова 'Software'.

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

18.2.6. Предикат 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 22', '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, поставленный перед любым специальным символом, отменяет специальную интерпретацию этого символа.

В заключение данного пункта вернемся к отложенному в разделе 17.2 «Скалярные выражения» лекции 17 обсуждению функции 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

Пример 18.14. Найти номера и названия отделов, название которых начинается со слов 'Hardware' или 'Software', а за ними (не обязательно непосредственно) следует последовательность десятичных цифр, предваряемых символом подчеркивания.

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

Пример 18.15. Найти номера и названия проектов, название которых не начинается с последовательности цифр.

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

18.2.7. Предикат exists

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

exists_predicate ::= EXISTS (query_expression)

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

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

Пример 18.16. Найти номера отделов, среди служащих которых имеются менеджеры проектов.

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

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

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

Далее заметим, что по смыслу предиката EXISTS список выборки во вложенном подзапросе является несущественным, и формулировку запроса можно изменить, например, следующим образом (пример 18.16.2):

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

Запросы с предикатом EXISTS можно также переформулировать в виде запросов с предикатом сравнения (пример 18.16.3):

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;

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

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

18.2.8. Предикат unique

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

unique_predicate ::= UNIQUE (query_expression)

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

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

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

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

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

SELECT DEPT_NO
FROM DEPT
WHERE NOT ESISTS
    (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)));

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

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

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

Пример 18.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г.

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

Пример 18.20. Найти названия проектов, которые будут выполняться в течение следующего года.

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

139 В стандарте SQL:1999 разрешается применять предикат LIKE только для битовых строк типа BLOB. Битовые строки типов BIT и BIT VARYING не допускаются.

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