Фильтрация записей

Предложение WHERE

Предложение WHERE предназначено для ограничения количества возвращаемых строк, теми которые нас интересуют. Условие после ключевого слова WHERE может быть простым, как проверка “AMOUNT = 3”, так и сложным, запутанным выражением, содержащим подзапросы, предикаты, вызовы функций, математические и логические операторы, контекстные переменные и многое другое.

Условие в предложении WHERE часто называют условием поиска, выражением поиска или просто поиском. В DSQL выражение поиска может содержать параметры. Это полезно, если запрос должен быть повторен несколько раз с разными значениями входных параметров. В строке SQL запроса, передаваемого на сервер, вопросительные знаки используются как заполнители для параметров. Их называют позиционными параметрами, потому что они не могут сказать ничего кроме как о позиции в строке. Библиотеки доступа часто поддерживают именованные параметры в виде :id, :amount, :a и т.д. Это более удобно для пользователя, библиотека заботится о трансляции именованных параметров в позиционные параметры, прежде чем передать запрос на сервер.

Синтаксис:

SELECT ... FROM ... WHERE <условие поиска>

<условие поиска> - логическое выражение возвращающее TRUE, FALSE и возможно UNKNOWN (NULL).

Только те строки, для которых условие поиска истинно, будут включены в результирующий набор. Будьте осторожны с возможными получаемыми значениями NULL: если вы отрицаете выражение, дающее NULL с помощью NOT, то результат такого выражения все равно будет NULL и строка не пройдёт.

Примеры:

SELECT PROJ_NAME FROM PROJECT WHERE PRODUCT='software';

SELECT * FROM SALES WHERE ORDER_DATE BETWEEN '1992-01-01' AND '1993-01-01';

SELECT * FROM EMPLOYEES WHERE SALARY >= 10000 AND POSITION <> 'MANAGER';

SELECT PROJ_NAME FROM PROJECT P WHERE EXISTS (SELECT * FROM PROJ_DEPT_BUDGET B WHERE P.PROJ_ID=B.PROJ_ID AND B.PROJECTED_BUDGET > 200000);

SELECT FULL_NAME FROM EMPLOYEE WHERE JOB_COUNTRY ='England' AND SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE JOB_COUNTRY = 'Canada');

SELECT * FROM CUSTOMER WHERE CUSTOMER LIKE '%Inc%' AND CITY CONTAINING 'san';

SELECT SEC$DESCRIPTION FROM SEC$USERS WHERE SEC$USER_NAME = CURRENT_USER;

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

Таблица MARBLETABLE

CHILDMARBLES
Маша5
Миша15
ТаняNULL
Ваня0
Коля7
Даша17

Первое, обратите внимание на разницу между NULL и 0. Известно, что Ваня не имеет шариков вовсе, однако неизвестно количество шариков у Тани.

Теперь, если ввести этот SQL оператор:

SELECT CHILD FROM MARBLETABLE WHERE MARBLES > 10

запрос вернет

CHILDMARBLES
Миша15
Даша17

Если добавить отрицание NOT:

SELECT CHILD FROM MARBLETABLE WHERE NOT MARBLES > 10

запрос вернёт

CHILDMARBLES
Маша5
Ваня0
Даша17

Таня не попадет ни в одну из выборок, т.к. не известно сколько у нее шариков. Если изменить последний запрос так:

SELECT CHILD FROM MARBLETABLE WHERE MARBLES <= 100

Таня все равно не попадет в результат поскольку выражение NULL <= 100 даёт UNKNOWN. Это не тоже самое что TRUE, поэтому Таня не отображена. Если предполагается что NULL равносильно 0, то запрос можно изменить следующим образом:

SELECT CHILD FROM MARBLETABLE WHERE MARBLES <= 10 OR MARBLES IS NULL

Теперь условие поиска становится истинным для Тани, потому что условие MARBLES is NULL возвращает TRUE в этом случае.

Рассмотрим возможности для построения выражений более подробно.

Выражения

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

Подзапросы

Подзапрос — это специальный вид выражения, которое фактически является запросом SELECT к другой таблице, включённый в спецификацию основного запроса. Подзапросы пишутся как обычные SELECT запросы, но должны быть заключены в круглые скобки.

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

SELECT * FROM CUSTOMERS C
  WHERE EXISTS
        (SELECT * FROM ORDERS O
          WHERE C.CNUM = O.CNUM
          AND O.ADATE = DATE '10.03.1990');

Подзапросы, используемые в предикатах поиска, кроме предикатов существования и количественных предикатов, должны возвращать скалярный результат, то есть не более чем один столбец из одной отобранной строки или одно агрегированное значение, в противном случае, произойдёт ошибка времени выполнения ("Multiple rows in a singleton select...").

SELECT
    E.FIRST_NAME,
    E.LAST_NAME,
    E.SALARY
FROM EMPLOYEE E
WHERE
    E.SALARY = (SELECT MAX(IE.SALARY)
                FROM EMPLOYEE IE)

Предикаты

Предикат — это простое выражение, утверждающее некоторый факт. Предикат может быть истинным (TRUE), ложным (FALSE) и неопределённым (UNKNOWN). В SQL ложный и неопределённый результаты трактуются как ложь.

В SQL предикаты проверяют в ограничении CHECK, предложении WHERE, выражении CASE, условии соединения во фразе ON для предложений JOIN, а также в предложении HAVING. В PSQL операторы управления потоком выполнения проверяют предикаты в предложениях IF, WHILE и WHEN.

Проверяемые условия могут состоять из одного или нескольких предикатов, связанных логическими операторами AND, OR и NOT.

Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения. К операторам сравнения относятся традиционные операторы (< , > , =, ...), а также другие перечисленные далее операторы.

Оператор BETWEEN проверяет, попадает (или не попадает при использовании NOT) ли значение во включающий диапазон значений (включая границы).

<значение> [NOT] BETWEEN <значение 1> AND <значение 2>

Условие будет истинным, если значение присутствует в указанном диапазоне (от <значение 1> до <значение 2> включительно) при отсутствии ключевого слова NOT. При наличии ключевого слова NOT условие будет истинным, если значение отсутствует в указанном диапазоне, включая граничные значения.

Оператор BETWEEN использует два аргумента совместимых типов. В отличие от некоторых других СУБД в Ред Базе Данных оператор BETWEEN не является симметричным. Меньшее значение должно быть первым аргументом, иначе предикат BETWEEN всегда будет ложным.

Например, если в таблице сотрудников EMPLOYEES нужно выбрать только тех сотрудников, которые имеют оклады (столбец SALARY) в соответствующем диапазоне, включая граничные значения, то следует выполнить следующий оператор:

SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 10000 AND 20000;

Предикат LIKE сравнивает выражение символьного типа с шаблоном.

<значение> [NOT] LIKE <шаблон> [ESCAPE <символ экранирования>]

Этот оператор является чувствительным к регистру (за исключением случаев, когда само поле определено с сортировкой (COLLATION) нечувствительной к регистру).

В шаблоне можно указать трафаретные символы % и _. Символ процента задает произвольное количество, в том числе и нулевое, любых символов. Знак подчеркивания задает ровно один любой символ.

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

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

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
  WHERE RDB$RELATION_NAME LIKE '%#_%'' ESCAPE '#'

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

Оператор STARTING WITH ищет строку, которая начинается с символов в его аргументе.

<значение> [NOT] STARTING [WITH] <значение 1>

Оператор чувствителен к регистру, однако такое ограничение также можно легко обойти, используя функцию UPPER или функцию LOWER.

При использовании предиката STARTING WITH в поисковых условиях DML запросов, оптимизатор может использовать индекс по искомому столбцу, если он определён.

SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE
  WHERE LAST_NAME STARTING WITH 'Jo';

Оператор CONTAINING ищет строку, отыскивая последовательность символов, которая соответствует его аргументу.

<значение> [NOT] CONTAINING <значение 1>

Оператор может быть использован для алфавитно-цифрового (подобного строковому) поиска в числах и датах.

Поиск CONTAINING не чувствителен к регистру.

При использовании оператора CONTAINING во внимание принимаются все символы строки. Это касается так же начальных и конечных пробелов. Если операция сравнения в запросе должна вернуть все строки, содержащие строки CONTAINING 'абв ' (с символом пробела на конце), то строка, содержащая 'абв' (без пробела), не будет возвращена.

При использовании предиката CONTAINING в поисковых условиях DML запросов, оптимизатор не может использовать индекс по искомому столбцу.

SELECT * FROM PROJECT
  WHERE PROJ_NAME CONTAINING 'map';

Оператор SIMILAR TO используется для сопоставления строк с шаблоном на основе регулярных выражений. Он похож на оператор LIKE, но предоставляет более мощные возможности, так как поддерживает синтаксис регулярных выражений в стиле стандарта SQL.

По умолчанию SIMILAR TO регистрозависим. Для регистронезависимого поиска можно использовать функции, такие как LOWER() или UPPER().

SIMILAR TO использует символы подстановки, такие как % (любое количество символов) и _ (один символ), как в LIKE. Также поддерживаются регулярные выражения.

  • [] — класс символов.

Символы, заключенные в квадратные скобки [], определяют класс символов. Если символ в строке соответствует классу, то символ является элементом класса. Причем классу соответствует единственный символ строки.

Два символа, соединенные дефисом -, в определении класса определяют диапазон. Диапазон для сопоставления включает в себя эти два конечных символа и все символы, находящиеся между ними

'Class' SIMILAR TO 'Cla[o-y]s',   -- <true>
'Class' SIMILAR TO 'C[la]ss',     -- <false>
'Class' SIMILAR TO 'C[abd-sx]ass' -- <true>

Если определение класса запускается со знаком вставки ^, то все, что следует за ним, исключается из класса. Все остальные символы проверяются. Если знак вставки ^ находится не в начале последовательности, то класс включает в себя все символы до него и исключает символы после него.

'Error' SIMILAR TO 'Er[^a-g]or',      -- <true>
'Error' SIMILAR TO 'Err[^e-p][^a-g]', -- <false>
'Error' SIMILAR TO 'Er[wrt^a-d]or'    -- <true>
  • *, +, ? — кванторы.

Квантор после символа, символьного класса или группы определяет, сколько раз предшествующее выражение может встречаться. Вопросительный знак ? сразу после символа, класса или группы указывает на то, что для соответствия предыдущий элемент может встретиться 0 или 1 раз. Звёздочка * сразу после символа, класса или группы указывает на то, что для соответствия предыдущий элемент может встретиться 0 или более раз. Знак плюс + сразу после символа, класса или группы указывает на то, что для соответствия предыдущий элемент может встретиться 1 или более раз.

'Question' SIMILAR TO 'Questt?ion',  -- <true>
'Asterisk' SIMILAR TO 'Ast[c-s]*sk', -- <true>
'Plus' SIMILAR TO 'Plus[[:DIGIT:]]+' -- <false>

Если символ или класс сопровождаются числом, заключённым в фигурные скобки {n}, то для соответствия нужно повторение элемента точно это число раз. Если число сопровождается запятой {n,}, то для соответствия нужно повторение элемента как минимум это число раз. Если фигурные скобки содержат два числа {m,n} и второе число больше первого, то для соответствия элемент должен быть повторен как минимум m раз и не больше n раз.

'Braces' SIMILAR TO 'Bra{2}ces',      -- <false>
'Braces' SIMILAR TO 'Bra[aceg]{2,}s', -- <true>
'Braces' SIMILAR TO 'Br[aceg]{1,2}s'  -- <false>
  • | — логическое ИЛИ.

В условиях регулярных выражений можно использовать оператор ИЛИ |. Соответствие произошло, если строка параметра соответствует по крайней мере одному из условий.

'Condition' SIMILAR TO 'Condi|tion',              -- <false>
'Condition' SIMILAR TO 'Condition|Statement',     -- <true>
'Condition' SIMILAR TO 'Condi_+|Kondi_+|Ckondi_+' -- <true>
  • () — группировка.

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

'Groups' SIMILAR TO 'G(ru|ro|ra)ups' -- <true>

Для исключения из процесса сопоставления специальных символов (которые используются для записи самих регулярных выражений) их надо экранировать. Специальных символов экранирования по умолчанию нет — их при необходимости определяет пользователь.

'Russia(RU)' SIMILAR TO 'R[a-z]+\(R[A-Z]+\)' ESCAPE '\', -- <true>
'France[FR]' SIMILAR TO 'Fr[a-z]+#[F%#]' ESCAPE '#',     -- <true>
'Puerto-Rico' SIMILAR TO 'P%$-R%' ESCAPE '$'             -- <true>

Рассмотрим еще несколько примеров.

Найдем все строки, которые начинаются с “А” и заканчиваются на “й”:

SELECT * FROM users WHERE name SIMILAR TO 'А%й';

Найдем имена, которые начинаются на “А” или “М”:

SELECT * FROM users WHERE name SIMILAR TO 'А%|М%';

Найдем имена, которые начинаются с “А”, “Б” или “В”:

SELECT * FROM users WHERE name SIMILAR TO '[АБВ]%';

Найдем имена, в которых есть две буквы “а” подряд:

SELECT * FROM users WHERE name SIMILAR TO '%а+а%';

Найдем имена, которые начинаются с “Ан” или “Ма”:

SELECT * FROM users WHERE name SIMILAR TO '(Ан|Ма)%';

Оператор IS DISTINCT FROM используется для проверки на неравенство (равенство, если задано NOT) двух значений.

<значение 1> IS [NOT] DISTINCT FROM <значение 2>

Два операнда считают различными (DISTINCT), если они имеют различные значения, или если одно из них — NULL, и другое нет. Они считаются равными (NOT DISTINCT), если имеют одинаковые значения или оба имеют значение NULL.

SELECT ID, NAME, TEACHER FROM COURSES
  WHERE START_DAY IS NOT DISTINCT FROM END_DAY;

В отличие от операторов равно (=) и не равно (!=, <>) этот оператор трактует два сравниваемых пустых значения NULL как равные друг другу. Как и в случае оператора IS [NOT] NULL данный оператор всегда возвращает либо TRUE, либо FALSE.

Таблица истинности для разных операторов представлена в таблице.

=IS NOT DISTINCT FROM!=, <>IS DISTINCT FROM
Одинаковые значенияTRUETRUEFALSEFALSE
Различные значенияFALSEFALSETRUETRUE
Оба NULLUNKNOWNTRUEUNKNOWNFALSE
Одно NULLUNKNOWNFALSEUNKNOWNTRUE

Оператор IS проверяет, что выражение в левой части является псевдозначением NULL или соответствует логическому значению в правой части.

<значение> IS [NOT] {TRUE | FALSE | UNKNOWN | NULL}

Если в правой части предиката использованы литерал TRUE, FALSE или UNKNOWN, то выражение в левой части должно быть логического типа, иначе будет выдана ошибка.

Оператор может вернуть только истинное значение TRUE или ложное FALSE, значение UNKNOWN невозможно.

Предикаты существования

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

Предикат EXISTS используется, чтобы определить, что результат подзапроса содержит хотя бы одну запись. В таком случае предикат EXISTS возвращает TRUE, а иначе FALSE.

[NOT] EXISTS (<оператор SELECT>)

Аргументом предиката EXISTS является оператор SELECT, возвращающий произвольное количество любых столбцов таблицы.

SELECT * FROM employee 
  WHERE NOT EXISTS (SELECT * FROM employee_project ep
                      WHERE ep.emp_no = employee.emp_no);

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

<значение> [NOT] IN (<n1, n2, ... ,nN>)

Например,

SELECT * FROM EMPLOYEE WHERE FIRST_NAME IN ('Pete', 'Ann', 'Roger');

SELECT * FROM EMPLOYEE  WHERE FIRST_NAME = 'Pete' OR 
                              FIRST_NAME =  'Ann' OR 
                              FIRST_NAME =  'Roger';

Во второй форме предикат IN проверяет, присутствует (или отсутствует, при использовании NOT IN) ли значение выражения слева в результате выполнения подзапроса справа. Результат подзапроса должен содержать только один столбец, иначе будет выдана ошибка "count of column list and variable list do not match".

Например,

SELECT model, speed, hd FROM PC
  WHERE model IN (SELECT model FROM product WHERE maker = 'A');

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

[NOT] SINGULAR (<оператор SELECT>)

Аргументом предиката SINGULAR является оператор SELECT, возвращающий произвольное количество любых столбцов таблицы (обычно это *). Данный предикат может возвращать только два значения: TRUE или FALSE.

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

При использовании квантора ALL, предикат является истинным, если каждое значение выбранное подзапросом удовлетворяет условию.

<значение> <оператор сравнения> ALL (<оператор SELECT>)

Здесь используется операторы сравнения, описанные выше в этом разделе, а также оператор IS [NOT] DISTINCT FROM.

Если подзапрос не возвращает ни одной строки, то предикат автоматически считается верным.

SELECT * FROM CUSTOMERS 
  WHERE RATING > ALL (SELECT RATING
                    FROM CUSTOMERS
                    WHERE CITY = 'PARIS')

При использовании кванторов SOME или ANY, предикат является истинным, если хотя бы одно значение выбранное подзапросом удовлетворяет условию.

<значение> <оператор сравнения> { SOME | ANY } (<оператор SELECT>)

Ключевые слова SOME и ANY являются синонимами. Если подзапрос не возвращает ни одной строки, то предикат автоматически считается ложным.

Контрольные вопросы

  1. Как можно ограничить количество возвращаемых строк?
  2. Что произойдет, если условие поиска вернет NULL?
  3. Каким образом в выражении можно использовать результат SQL запроса?
  4. Какие операторы могут быть использованы в предикатах сравнения?
  5. Какие трафаретные символы применяются в операторе LIKE?
  6. Что делать, если трафаретный символ нужно использовать как обычный?
  7. Как обойти ограничение операторов, которые чувствительны к регистру?
  8. Для чего служат кванторы в операторе SIMILAR TO?
  9. Как указываются классы символов оператора SIMILAR TO?
  10. В чем заключается различие оператора IS DISTINCT FROM и оператора неравенства?
  11. Что является результатом предиката SINGULAR?
  12. В различие квантора ALL и ANY?
  13. В различие квантора SOME и ANY?