2008 г.

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

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

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

Синтаксически логическое выражение раздела WHERE определяется как булевское выражение (boolean_value_expression), правила построения которого обсуждались в предыдущей лекции. Основой логического выражения являются предикаты. Предикат позволяет специфицировать условие, результатом вычисления которого может быть true, false или unknown. В языке SQL:1999 допустимы следующие предикаты:¹³⁵⁾

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 были приведены в лекции 16. Для удобства повторим здесь структуру таблиц.

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

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

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

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.

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

Пример 18.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'.

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

Мы добавили спецификацию DISTINCT в раздел SELECT, потому что в одном отделе могут работать несколько служащих с фамилией 'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией 'Smith' в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 18.1.1):

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

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

Пример 18.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г.

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

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

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

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 традиционно называются корреляционными, и их следует понимать следующим образом¹³⁶⁾.

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

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

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

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

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 (по условию EMP_SAL < 15000.00) с таблицами DEPT и EMP (по условиям EMP.DEPT_NO = DEPT.DEPT_NO и DEPT.DEPT_MNG = EMP2.EMP_NO соответственно). Таблица EMP участвует в качестве операнда операции эквисоединения два раза. Поэтому в разделе FROM ей присвоены два псевдонима – EMP1 и EMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицы EMP, требуемое для выполнения раздела FROM с образованием расширенного декартова произведения таблиц-операндов.¹³⁷⁾ Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.

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

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 видно, что в подразделе 17.2.1. «Общие синтаксические правила построения скалярных выражений» предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: «После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 илиT3 соответственно». В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.

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

Пример 18.5. Найти номера, имена и размер зарплаты служащих, получающих зарплату в размере от 12000 до 15000 руб.

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

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

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.

18.2.3. Предикат 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 приведена в таблице 18.1.
Таблица 18.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

На самом деле, в нашей формулировке запроса из примера 18.6 есть одна неточность. Если у некоторого служащего номер отдела неизвестен (значение столбца EMP.DEPT_NO у соответствующей строки таблицы служащих является неопределенным), то бессмысленно вычислять средний размер зарплаты отдела этого служащего и находить размер зарплаты руководителя отдела. Формулировка из примера 18.6 приведет к правильному результату, но это неочевидно.¹³⁸⁾ Чтобы сделать формулировку более понятной (и, возможно, помочь системе выполнить запрос более эффективно), нужно воспользоваться предикатом IS NOT NULL и переписать запрос следующим образом:

Пример 18.7.

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

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

SELECT EMP_NO, EMP_NAME 
FROM EMP
WHERE DEPT_NO IS NULL;

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

136 Здесь снова идет речь о семантике выполнения оператора SELECT. В стандарте, естественно, не требуется, чтобы в реализации языка запросы с корреляционными подзапросами выполнялись в точности так, как описывается ниже. Суть в том, что какой бы реальный алгоритм выполнения такого запроса не использовался, результат выполнения должен быть точно таким же, как если бы запрос выполнялся по описываемой схеме.

137 Кстати, в этом случае можно было бы обойтись введением одного псевдонима, оставив в качестве неявного второго псевдонима имя таблицы – EMP.

138 Покажем это в развернутой форме. Пусть s – текущая строка таблицы EMP, просматриваемой в цикле внешнего запроса, и пусть s.DEPT_NO содержит неопределенное значение. Тогда для строки s условие первого подзапроса будет иметь вид NULL = EMP1.DEPT_NO, и значением этого условия будет unknown для любой строки таблицы EMP(EMP1), просматриваемой в цикле этого подзапроса. Поскольку unknown не является разрешающим условием, результирующая таблица подзапроса будет пуста, и агрегатная функция AVG выдаст значение NULL. По этому поводу значением условия внешнего запроса будет unknown, и строка s не войдет в результирующую таблицу.

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