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

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

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

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

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

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

1. если xv и/или yv являются неопределенными значениями, то значение условия X comp_op Y -unknown ;
2. в противном случае значением условия 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';

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

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 традиционно называются корреляционными, и их следует понимать следующим образом^{2Здесь снова идет речь о семантике выполнения оператора SELECT. В стандарте, естественно, не требуется, чтобы в реализации языка запросы с корреляционными подзапросами выполнялись в точности так, как описывается ниже. Суть в том, что какой бы реальный алгоритм выполнения такого запроса не использовался, результат выполнения должен быть точно таким же, как если бы запрос выполнялся по описываемой схеме.}.

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

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

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 с образованием расширенного декартова произведения таблиц-операндов. ^{3Кстати, в этом случае можно было бы обойтись введением одного псевдонима, оставив в качестве неявного второго псевдонима имя таблицы - EMP .} Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением ), поскольку в списке выборки присутствуют имена столбцов каждой из них.

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