Руководство По Языку SQL СУБД Firebird 5.0

Типы данныхОператоры определения данных (DDL)

Выражения

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

Описание элементов языка

Имя столбца: Идентификаторы столбцов из указанных таблиц, используемые ввычислениях, или сравнениях, или в качестве условия поиска. Столбецтипа массив не может быть элементом выражения, если только он непроверяется на IS [NOT] NULL.
Элементы массива: В выражении может содержаться ссылка на элемент массива, т.е. <array_name>[s], где s — индекс элемента в массиве.
Арифметические операторы: Символы +, -, *, / используемые для вычисления значений.
Оператор конкатенации: Оператор || (“две вертикальные линии”) используется для соединения символьных строк.
Логические операторы: Зарезервированные слова NOT, AND и OR используются прикомбинировании простых условий поиска для создания сложныхутверждений.
Операторы сравнения: Символы =, <>, !=, ~=, ^=, <, <=, >, >=, !<, ~<, ^<, !>, ~> и ^>
Предикаты сравнения: LIKE, STARTING WITH, CONTAINING, SIMILAR TO, BETWEEN, IS [NOT]NULL, IS [NOT] {TRUE | FALSE | UNKNOWN}, и IS [NOT] DISTINCT FROM
Предикаты существования: Предикаты, используемые для проверки существования значений внаборе. Предикат IN может быть использован как с наборами констант,так и со скалярными подзапросами. Предикаты EXISTS, SINGULAR, ALLANY, SOME могут быть использованы только с подзапросами.
Константы и литералы: Числа, заключённые в апострофы строковые литералы, логическиезначения TRUE, FALSE и UNKNOWN, псевдозначение NULL.
Литералы дат и времени: Выражения, подобные строковым литералам, заключённые вапострофах, которые могут быть интерпретированы как значения даты,времени или даты-времени. Литералами дат могут быть строки изсимволов и чисел, такие как TIMESTAMP '25.12.201615:30:35', которые могут быть преобразованы в дату, времяили дату с временем.
Мнемоники дат и времени: Строковый литерал с описанием желаемого значения даты и/иливремени, которое можно привести к типу даты и/или времени. Дляпримера 'NOW', 'TODAY'.
Контекстные переменные: Встроенные контекстные переменные.
Локальные переменные: Локальные переменные, входные или выходные параметры PSQL модулей(хранимых процедур, триггеров, анонимных блоков PSQL).
Позиционные параметры: В DSQL в качестве параметров запроса могут быть использованытолько позиционные параметры. Позиционные параметры представляютсобой знаки вопроса (?) внутри DSQL оператора. Доступ к такимпараметрам осуществляется по его номеру (позиции в запросеотносительно предыдущего позиционного параметра) поэтому ониназываются позиционными. Обычно компоненты доступа позволяютработать с именованными параметрами, которые они самипреобразовывают в позиционные.
Подзапросы: Оператор SELECT заключённый в круглые скобки, который возвращаетодно единственное (скалярное) значение или множество значений (прииспользовании в предикатах существования).
Идентификаторы функций: Идентификаторы встроенных или внешних функций в функциональныхвыражениях.
Приведения типа: Выражение явного преобразования одного типа данных в другой с использованием CAST какCAST(<value> AS <datatype>).
Условные выражения: Выражение CASE и встроенные функции COALESCE, NULLIF.
Круглые скобки: Пара скобок ( … ) используются для группировки выражений.Операции внутри скобок выполняются перед операциями вне скобок. Прииспользовании вложенных скобок, сначала вычисляются значения самыхвнутренних выражений, а затем вычисления перемещаются наверх поуровням вложенности.
Предложение COLLATE: Предложение применяется к типам CHAR и VARCHAR, чтобы в указаннойкодировке установить параметры сортировки, используемые присравнении.
NEXT VALUE FOR sequence: Конструкция NEXT VALUE FOR позволяет получить следующее значениепоследовательности, то же самое делает встроенная функция GEN_ID().
Выражение AT: Выражение для изменения часового пояса даты и времени.

Литералы (константы)

Литерал или константа — это значение, подставляемое непосредственно в SQL оператор, которое не получено из выражения, параметра, ссылки на столбец или переменной.

Строковые литералы

Строковый литерал это последовательность символов, заключенных между парой апострофов (“одинарных кавычек”). Максимальная длина строковой константы составляет 65535 байт; максимальная количество символов будет определяться количеством байт, используемых для кодирования каждого символа.

Синтаксис:

<character string literal> ::=
  [ <introducer> <character set specification> ]
    <quote> [ <character representation>... ] <quote>
    [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

<separator> ::=
  { <comment> | <white space> }

<introducer> ::= underscore (U+005F)

<quote> ::= apostrophe (U+0027)

<char> ::= character representation;
apostrophe is escaped by doubling

Example 1. Простой строковый литерал

'Hello world'

Если литерал апострофа требуется в строковой константе, то он может быть “экранирован” другим предшествующим апострофом.

Example 2. Строковый литерал содержащий апостроф

'Mother O''Reilly's home-made hooch'

Другой способ записать данный строковый литерал использовать альтернативные кавычки:

q'{Mother O'Reilly's home-made hooch}'

При необходимости строковый литерал может быть "прерван" пробелом или комментарием.Это может быть использовано для разбиения длинного литерала на несколько строк или предоставления встроенных комментариев.

Example 3. Строковые литералы прерванные пробелом и комментарием

-- whitespace between literal
select 'ab'
       'cd'
from RDB$DATABASE;
-- output: 'abcd'

-- comment and whitespace between literal
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: 'abcd'

Note

Двойные кавычки не должны (допускаются 1 диалектом) использоваться для квотирования строк. В SQL они предусмотрены для других целей.
Необходимо быть осторожным с длиной строки, если значение должно быть записано в столбец типа VARCHAR. Максимальная длина строки для типа VARCHAR составляет 32765 байт (32767 для типа CHAR). Если значение должно быть записано в столбец типа BLOB, то максимальная длина строкового литерала составляет 65535 байт.

Предполагается, что набор символов строковой константы совпадает с набором символов столбца предназначенного для её сохранения.

Альтернативы для апострофов в строковых литералах

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

Ключевое слово q или Q предшествующее строке в кавычках сообщает парсеру, что некоторые левые и правые пары одинаковых символов являются разделителями для встроенного строкового литерала.

Синтаксис:

<alternate string literal> ::=
  { q | Q } <quote> <alternate start char>
  [ { <char> }... ]
  <alternate end char> <quote>

Note

Правила использования

Когда <alternate start char> является одним из символов '(', '{', '[' или '<', то <alternate end char> должен бытьиспользован в паре с соответствующим “партнёром”, а именно ')', '}', ']' или '>'. В других случаях <alternate end char> совпадает с <alternate start char>.

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

Example 1. Использование альтернативных апострофов в строковых литералах

-- result: abc{def}ghi
SELECT Q'{abc{def}ghi}' FROM rdb$database;

-- result: That's a string
SELECT Q'!That's a string!' FROM rdb$database;

Example 2. Динамическая сборка запроса использующего строковые литералы.

EXECUTE BLOCK
RETURNS (
  RDB$TRIGGER_NAME CHAR(64)
)
AS
  DECLARE VARIABLE S VARCHAR(8191);
BEGIN
  S = 'SELECT RDB$TRIGGER_NAME FROM RDB$TRIGGERS WHERE RDB$RELATION_NAME IN ';
  S = S || Q'! ('SALES_ORDER', 'SALES_ORDER_LINE')!';
  FOR
    EXECUTE STATEMENT :S
    INTO :RDB$TRIGGER_NAME
  DO
    SUSPEND;
END

Набор символов для строковых литералов

При необходимости строковому литералу может предшествовать имя набор символов, который начинается с префикса подчеркивания “_”.Это известно как вводный синтаксис (Introducer syntax). Его цель заключается в информировании Firebird о том, как интерпретировать и хранить входящую строку.

Example 1. Вводный синтаксис для строковых литералов

-- обратите внимание на префикс '_'
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

Строковые литералы для двоичных строк

Начиная с Firebird 2.5 строковые константы могут быть записаны в шестнадцатеричной нотации, так называемые “двоичные строки”.Каждая пара шестнадцатеричных цифр определяет один байт в строке.Строки введённые таким образом будут иметь кодировку OCTETS по умолчанию,но вводный синтаксис (introducer syntax) может быть использован для принудительной интерпретации строки в другом наборе символов.

Синтаксис:

<binary string literal> ::=
  {x | X} <quote> [<space>...] [ { <hexit> [<space>...] <hexit> [<space>...] }... ] <quote>
          [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
            <hexit> [ <space>... ] }... ] <quote> }... ]

<hexit> ::= an even number of <hexdigit>

<hexdigit> ::= 0..9 | A..F | a..f

Example 1. Примеры:

SELECT x'4E657276656E' FROM rdb$database
-- returns 4E657276656E, a 6-byte 'binary' string

SELECT _ascii x'4E657276656E' FROM rdb$database
-- returns 'Nerven' (same string, now interpreted as ASCII text)

SELECT _iso8859_1 x'53E46765' FROM rdb$database
-- returns 'Säge' (4 chars, 4 bytes)

SELECT _utf8 x'53C3A46765' FROM rdb$database
-- returns 'Säge' (4 chars, 5 bytes)

Note

Как будут отображена двоичная строка зависит от интерфейса клиента.Например, утилита isql использует заглавные буквы A-F, в то время как FlameRobin буквы в нижнем регистре.Другие могут использовать другие правила конвертирования, например отображать пробелы между парами байт: '4E 65 72 76 65 6E'.

Шестнадцатеричная нотация позволяет вставить любой байт (включая 00) в любой позиции в строке.

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

Example 2. Двоичный литерал прерванный пробелом

-- Group per byte (whitespace inside literal)
select _win1252 x'42 49 4e 41 52 59'
from RDB$DATABASE;
-- output: BINARY

-- whitespace between literal
select _win1252 x'42494e'
                 '415259'
from RDB$DATABASE;
-- output: BINARY

Числовые константы

Числовая константа — это любое правильное число в одной из поддерживаемых нотаций:

В SQL, для чисел в стандартной десятичной записи, десятичная точка всегда представлена символом точки и тысячи не разделены. Включение запятых, пробелов, и т.д. вызовет ошибки.
Экспоненциальная запись, например число 0.0000234 может быть записано как 2.34e-5.
Шестнадцатеричная запись (см. ниже) чисел поддерживается начиная с Firebird 2.5.

Далее показаны форматы числовых литералов и их типы.Где <d> - десятичная цифра, <h> - шестнадцатеричная цифра.

Table 1. Формат числовых констант
Формат	Тип
`<d>[<d> …]`	`INTEGER`, `BIGINT`, `INT128` или `DECFLOAT(34)` (зависит от того, подходит ли значение типу). `DECFLOAT(34)` используется для значений, которые не помещаются в `INT128`.
`0{x\|X} <h>[<h> …]`	`INTEGER` для 1-8 шестнадцатеричных цифр, `BIGINT` для 9-16 цифр, `INT128` для 17-32 цифр (доступно с Firebird 4.0.1).
`<d>[<d> …].[<d> …]`	`NUMERIC(18, n)`, `NUMERIC(38, n)` или `DECFLOAT(34)` где n зависит от количества цифр после десятичной точки, а точность от общего количества цифр. Для обеспечения обратной совместимости некоторые значения из 19 цифр отображаются на `NUMERIC(18, n)`.`DECFLOAT(34)` используется, когда немасштабированное значение не помещается в `INT128`.
`<d>[<d> …][. [<d> …]] E <d>[<d> …]`	`DOUBLE PRECISION` или `DECFLOAT(34)`, где `DECFLOAT` используется, только если количество цифр 20 или больше, или абсолютный показатель степени 309 или больше.

Шестнадцатеричная нотация чисел

Константы целочисленных типов можно указать в шестнадцатеричном формате.Начиная с Firebird 4.0.1 числа состоящие из 17-32 шестнадцатеричных цифр будут интерпретированы как INT128.

Синтаксис:

{x|X}<hexdigits>

<hexdigits> ::= 1-32 of <hexdigit>

<hexdigit> ::= 0..9 | A..F | a..f

Table 1. Константы целочисленных типов в шестнадцатеричном формате
Количество шестнадцатеричных цифр	Тип данных
1-8	INTEGER
9-16	BIGINT
17-32	INT128

Example 1. Шестнадцатеричные константы

SELECT 0x6FAA0D3 FROM rdb$database -- returns 117088467
SELECT 0x4F9 FROM rdb$database -- returns 1273
SELECT 0x6E44F9A8 FROM rdb$database -- returns 1850014120
SELECT 0x9E44F9A8 FROM rdb$database -- returns -1639646808 (an INTEGER)
SELECT 0x09E44F9A8 FROM rdb$database -- returns 2655320488 (a BIGINT)
SELECT 0x28ED678A4C987 FROM rdb$database -- returns 720001751632263
SELECT 0xFFFFFFFFFFFFFFFF FROM rdb$database -- returns -1

Диапазон значений шестнадцатеричных чисел

Шестнадцатеричные числа в диапазоне 0 .. 7FFF FFFF являются положительными INTEGER числа со значениями 0 .. 2147483647. Для того чтобы интерпретировать константу как BIGINT число, необходимо дописать необходимо количества нулей слева. Это изменит тип, но не значение.
Числа в диапазоне 8000 0000 .. FFFF FFFF требуют особого внимания:
- При записи восемью шестнадцатеричный числами, такие как 0x9E44F9A8, интерпретируется как 32-битное целое. Поскольку крайний левый (знаковый) бит установлен, то такие числа будут находиться в отрицательном диапазоне -2147483648 .. -1.
- Числа предварённые одним или несколькими нулями, такие как 0x09E44F9A8, будут интерпретированы как 64-разрядный BIGINT в диапазоне значений 0000 0000 8000 0000 .. 0000 0000 FFFF FFFF. В этом случае знаковый бит не установлен, поэтому они отображаются в положительном диапазоне 2147483648 .. 4294967295 десятичных чисел.
Таким образом, только в этом диапазоне числа, предварённые совершенно незначимым нулём, имеют кардинально разные значения.Это необходимо знать.
Шестнадцатеричные числа в диапазоне 1 0000 0000 .. 7FFF FFFF FFFF FFFF являются положительными BIGINT числами.
Шестнадцатеричные числа в диапазоне 8000 0000 0000 0000 .. FFFF FFFF FFFF FFFF являются отрицательными BIGINT числами.
Числа с типом SMALLINT не могут быть записаны в шестнадцатеричном виде, строго говоря, так как даже 0x1 оценивается как INTEGER. Тем не менее, если вы записываете положительное целое число в пределах 16-разрядного диапазона от 0x0000 (десятичный ноль) до 0x7FFF (десятичное 32767), то оно будет преобразовано в SMALLINT прозрачно.

Вы можете записать отрицательное SMALLINT число в шестнадцатеричном виде используя 4-байтное шестнадцатеричное число в диапазоне от 0xFFFF8000 (десятичное -32768) до 0xFFFFFFFF (десятичное -1).

Логические литералы

Логический литерал может быть одним из следующих значений: TRUE, FALSE или UNKNOWN.

Операторы SQL

SQL операторы включают в себя операторы для сравнения, вычисления, оценки и конкатенации значений.

Приоритет операторов

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

Все операторы разбиты на 4 типа.Каждый тип оператора имеет свой приоритет.Чем выше приоритет типа оператора, тем раньше он будет вычислен.Внутри одного типа операторы имеют собственный приоритет, который также определяет порядок их вычисления в выражении.Операторы с одинаковым приоритетом вычисляются слева направо.Для изменения порядка вычислений операции могут быть сгруппированы с помощью круглых скобок.

Table 1. Приоритеты типов операторов
Тип оператора	Приоритет	Пояснение
Конкатенация	1	Строки объединяются до выполнения любых других операций.
Арифметический	2	Арифметические операции выполняются после конкатенациистрок, но перед выполнением операторов сравнения илогических операций.
Сравнение	3	Операции сравнения вычисляются после конкатенации строк ивыполнения арифметических операций, но до логическихопераций.
Логический	4	Логические операторы выполняются после всех других типовоператоров.

Оператор конкатенации

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

Example 1. Оператор конкатенации

SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
FROM EMPLOYEE

Арифметические операторы

Table 1. Приоритет арифметических операторов
Оператор	Назначение	Приоритет
`+signed_number`	Унарный плюс	1
`-signed_number`	Унарный минус	1
`*`	Умножение	2
`/`	Деление	2
`+`	Сложение	3
`-`	Вычитание	3

Example 1. Арифметические операторы

UPDATE T
SET A = 4 + 1/(B-C)*D

Операторы сравнения

Table 1. Операторы сравнения
Оператор	Назначение	Приоритет
`IS`	Проверяет, что выражение в левой части является псевдозначением `NULL` или соответствует логическому значению вправой части.	1
`=`	Равно, идентично	2
`<>`, `!=`, `~=`, `^=`	Не равно	2
`>`	Больше	2
`<`	Меньше	2
`>=`	Больше или равно	2
`<=`	Меньше или равно	2
`!>`, `~>`, `^>`	Не больше	2
`!<`, `~<`, `^<`	Не меньше	2

В эту же группу входят предикаты сравнения IS DISTINCT FROM, BETWEEN, IN, LIKE, CONTAINING, SIMILAR TO и другие.

Example 1. Использование оператора сравнения

IF (SALARY > 1400) THEN
...

См. также:

Другие предикаты сравнения.

Логические операторы

Table 1. Приоритет логических операторов
Оператор	Назначение	Приоритет
`NOT`	Отрицание условия поиска.	1
`AND`	Объединяет два предиката и более, каждый из которыхдолжен быть истинным, чтобы истинным был и весьпредикат.	2
`OR`	Объединяет два предиката и более, из которых должен бытьистинным хотя бы один предикат, чтобы истинным был и весьпредикат.	3

Example 1. Использование логических операторов

IF (A > B OR (A > C AND A > D) AND NOT (C = D)) THEN
...

`AT`

Доступно в

DSQL, PSQL.

Синтаксис

<expr> AT {TIME ZONE <time zone string> | LOCAL}

<time zone string> ::=
    '<time zone>'

<time zone> ::=
    <time zone region> |
    [+/-] <hour displacement> [: <minute displacement>]

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

Example 1. Использование функции AT

select time '12:00 GMT' at time zone '-03'
  from rdb$database;

select current_timestamp at time zone 'America/Sao_Paulo'
  from rdb$database;

select timestamp '2018-01-01 12:00 GMT' at local
  from rdb$database;

`NEXT VALUE FOR`

Доступно вDSQL, PSQL.

Синтаксис

NEXT VALUE FOR sequence-name

Возвращает следующее значение в последовательности (SEQUENCE). SEQUENCE является SQL совместимым термином генератора в InterBase и Firebird.Оператор NEXT VALUE FOR полностью эквивалентен функции GEN_ID (sequence-name, 1) и является рекомендуемым синтаксисом.

Note	`NEXT VALUE FOR` не поддерживает значение приращения, отличное от того, что было указано при создании последовательности в предложении `INCREMENT [BY]`. Если требуется другое значение шага, то используйте старую функцию `GEN_ID`.

Example 1. Использование NEXT VALUE FOR

NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;

См. также:

SEQUENCE (GENERATOR), GEN_ID.

Условные выражения

Условное выражение — это выражение, которое возвращает различные значения в зависимости от истинности некоторого условия или условий.В данном разделе описано лишь одно условное выражение CASE.Остальные условные выражения являются производными встроенными функциями и описаны в разделе Условные функции.

CASE

Доступно в

DSQL, ESQL.

Оператор CASE возвращает только одно значение из нескольких возможных.Есть два синтаксических варианта:

Простой CASE, сравнимый с Pascal case или C switch;
Поисковый CASE, который работает как серия операторов “if … else if … else if”.

Простой CASE

Синтаксис

CASE <test-expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

При использовании этого варианта test-expr сравнивается с первым expr,затем вторым expr и так далее,до тех пор, пока не будет найдено совпадение, и тогда возвращается соответствующий результат.Если совпадений не найдено, то возвращается defaultresult из ветви ELSE.Если нет совпадений, и ветвь ELSE отсутствует, то возвращается значение NULL.

Совпадение эквивалентно оператору “=”, то естьесли test-expr имеет значение NULL, то он не соответствует ни одному из expr, даже тем, которые имеют значение NULL.

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

Example 1. Использование простого CASE

SELECT
  NAME,
  AGE,
  CASE UPPER(SEX)
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Unknown'
  END AS SEXNAME,
  RELIGION
FROM PEOPLE

Сокращённый вид простого оператора CASE используется в функции DECODE.

Поисковый CASE

Синтаксис

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> …]
  [ELSE <defaultresult>]
END

Здесь <bool_expr> выражение, которое даёт тройной логический результат: TRUE, FALSE или NULL.Первое выражение, возвращающее TRUE, определяет результат.Если нет выражений, возвращающих TRUE, то в качестве результата берётся defaultresult из ветви ELSE.Если нет выражений, возвращающих TRUE, и ветвь ELSE отсутствует, результатом будет NULL.

Как и в простом операторе CASE, результаты не обязаны быть литеральными значениями: они могут быть полями или именами переменных, сложными выражениями, или NULL.

Example 1. Использование поискового CASE

CANVOTE = CASE
  WHEN AGE >= 18 THEN 'Yes'
  WHEN AGE < 18 THEN 'No'
  ELSE 'Unsure'
END;

`NULL` в выражениях

NULL не является значением — это состояние, указывающее, что значение элемента неизвестно или не существует.Это не ноль, не пустота, не “пустая строка”, и оно не ведёт себя как какое-то из этих значений.

При использовании NULL в числовых, строковых выражениях или в выражениях, содержащих дату/время, в результате вы всегда получите NULL.При использовании NULL в логических (булевых) выражениях результат будет зависеть от типа операции и других вовлечённых значений.При сравнении значения с NULL результат будет неопределённым (UNKNOWN).

Important

Неопределённый логический результат UNKNOWN тоже представлен псевдо-значением NULL.

Выражения возвращающие `NULL`

Выражения в этом списке всегда возвратят NULL:

1 + 2 + 3 + NULL
'Home ' || 'sweet ' || NULL
MyField = NULL
MyField <> NULL
NULL = NULL
not (NULL)

Если вам трудно понять, почему, вспомните, что NULL — значит “неизвестно”.

`NULL` в логических выражениях

Мы уже рассмотрели, что not (NULL) даёт в результате NULL.Для операторов AND и OR взаимодействие несколько сложнее:

NULL or false → NULL
NULL or true → true
NULL or NULL → NULL
NULL and false → false
NULL and true → NULL
NULL and NULL → NULL

Example 1. NULL в логических выражениях

(1 = NULL) or (1 <> 1)    -- returns NULL
(1 = NULL) or FALSE       -- returns NULL
(1 = NULL) or (1 = 1)     -- returns TRUE
(1 = NULL) or TRUE        -- returns TRUE
(1 = NULL) or (1 = NULL)  -- returns NULL
(1 = NULL) or UNKNOWN     -- returns NULL
(1 = NULL) and (1 <> 1)   -- returns FALSE
(1 = NULL) and FALSE      -- returns FALSE
(1 = NULL) and (1 = 1)    -- returns NULL
(1 = NULL) and TRUE       -- returns NULL
(1 = NULL) and (1 = NULL) -- returns NULL
(1 = NULL) and UNKNOWN    -- returns NULL

Подзапросы

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

Для задания выходного столбца в списке выбора SELECT;
Для получения значений или условий для предикатов поиска (предложения WHERE, HAVING);
Для создания набора данных, из которого включающий запрос может выбирать, как будто это обычная таблица или представление.Подобные подзапросы появляются в предложении FROM (производные таблицы) или в общем табличном выражении (CTE).

Коррелированные подзапросы

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

Example 1. Коррелированный подзапрос

SELECT *
FROM Customers C
WHERE EXISTS
      (SELECT *
       FROM Orders O
       WHERE C.cnum = O.cnum
         AND O.adate = DATE '10.03.1990');

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

Подзапросы возвращающие скалярный результат

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

Note

Несмотря на то, что Firebird сообщает о подлинной ошибке, сообщение может немного вводить в заблуждение.“singleton SELECT” — это запрос, который не должен возвращать более одной строки.Однако “singleton” и “scalar” не являются синонимами: не все одноэлементные SELECTS должны быть скалярными; а выборка по одному столбцу может возвращать несколько строк для предикатов существования и количественных предикатов.

Example 1. Подзапрос в качестве выходного столбца в списке выбора

SELECT
    e.first_name,
    e.last_name,
    (SELECT
         sh.new_salary
     FROM
         salary_history sh
     WHERE
         sh.emp_no = e.emp_no
     ORDER BY sh.change_date DESC ROWS 1) AS last_salary
FROM
    employee e

Example 2. Подзапрос в предложении WHERE для получения значения максимальной зарплаты сотрудника и фильтрации по нему

SELECT
    e.first_name,
    e.last_name,
    e.salary
FROM
    employee e
WHERE
    e.salary = (SELECT
                    MAX(ie.salary)
                FROM
                    employee ie)

Предикаты

Предикат — это простое выражение, утверждающее некоторый факт, назовем его P.Если P разрешается как TRUE, он успешен.Если он принимает значение FALSE или NULL (UNKNOWN), он терпит неудачу.Однако здесь кроется ловушка: предположим, что предикат P возвращает FALSE.В этом случае NOT (P) вернет TRUE.С другой стороны, если P возвращает NULL (неизвестно), то NOT (P) также возвращает NULL.

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

Утверждения

Проверяемые условия не всегда являются простыми предикатами.Они могут быть группой предикатов, каждый из которых при вычислении делает вклад в вычислении общей истинности.Такие сложные условия называются утверждениями.Утверждения могут состоять из одного или нескольких предикатов, связанных логическими операторами AND, OR и NOT. Для группировки предикатов и управления порядком вычислений можно использовать скобки.

Каждый из предикатов может содержать вложенные предикаты.Результат вычисления истинности утверждения получается в результате вычисления всех предикатов по направлению от внутренних к внешним.Каждый “уровень” вычисляется в порядке приоритета до тех пор, пока не будет получено значение истинности окончательного утверждения.

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

Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения.Имеется шесть традиционных операторов сравнения:

=, >, <, >=, <=, <>

(Полный список операторов сравнения см. Операторы сравнения).

Если в одной из частей (левой или правой) предиката сравнения встречается NULL, то значение предиката будет неопределённым (UNKNOWN).

Example 1. Предикаты сравнения

Получить информацию о компьютерах, имеющих частоту процессора не менее 500 МГц и цену ниже $800

SELECT *
FROM Pc
WHERE speed >= 500 AND price < 800;

Получить информацию обо всех принтерах, которые являются матричными и стоят меньше $300

SELECT *
FROM Printer
WHERE type = 'matrix' AND price < 300;

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

SELECT *
FROM Printer
WHERE type = NULL AND price < 300;

Note	Замечание о сравнении строк При сравнении на равенство полей типов `CHAR` и `VARCHAR` завершающий пробелы игнорируются во всех случаях.

Другие предикаты сравнения

Другие предикаты сравнения состоят из ключевых слов.

`BETWEEN`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

<value> [NOT] BETWEEN <value_1> AND <value_2>

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

Операнды для предиката BETWEEN — это два аргумента совместимых типов.В отличие от некоторых других СУБД в Firebird предикат BETWEEN не является симметричным.Меньшее значение должно быть первым аргументом, иначе предикат BETWEEN всегда будет ложным.Поиск является включающим.Таким образом, предикат BETWEEN можно переписать следующим образом:

<value> >= <value_1> AND <value> <= <value_2>

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

Example 1. Использование предиката BETWEEN

SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN date '01.01.1992' AND CURRENT_DATE

`LIKE`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

<match value> [NOT] LIKE <pattern>
  [ESCAPE <escape character>]

<match value> ::= выражение символьного типа
<pattern> ::= шаблон поиска
<escape character> ::= символ экранирования

Предикат LIKE сравнивает выражение символьного типа с шаблоном, определённым во втором выражении.Чувствительность к регистру или диакритическим знакам при сравнении определяется используемым параметром сортировки (COLLATION).

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

Трафаретные символы

В шаблоне, разрешается использование двух трафаретных символов:

символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении;
символ подчёркивания (_), который можно применять вместо любого единичного символа в проверяемом значении.

Если проверяемое значение соответствует образцу с учётом трафаретных символов, то предикат истинен.

Использование управляющего символа в предложении `ESCAPE`

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

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

Example 1. Поиск строк начинающихся с заданной подстроки с использованеим предиката LIKE

Поиск номеров отделов, названия которых начинаются со слова “Software”

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

В данном запросе может быть использован индекс, если он построен на поле DEPT_NAME.

Note

Оптимизация LIKE

В общем случае предикат LIKE не использует индекс.Однако если предикат принимает вид LIKE 'string%', то он будет преобразован в предикат STARTING WITH, который будет использовать индекс.Если вам необходимо выполнить поиск с начала строки, то вместо предиката LIKE рекомендуется использовать предикат STARTING WITH.

Example 2. Использование трафаретного символа “_” в предикате LIKE

Поиск сотрудников, имена которых состоят из 5 букв, начинающихся с букв “Sm” и заканчивающихся на “th”.В данном случае предикат будет истинен для имен “Smith” и “Smyth”.

SELECT
    first_name
FROM
    employee
WHERE first_name LIKE 'Sm_th'

Example 3. Поиск внутри строки с использованием предиката LIKE

Поиск всех заказчиков, в адресе которых содержится строка “Ростов”.

SELECT *
FROM CUSTOMER
WHERE ADDRESS LIKE '%Ростов%'

Tip	Если вам необходимо выполнить поиск внутри строки, то вместо предиката `LIKE` рекомендуется использовать предикат CONTAINING.

Example 4. Использование управляющего символа в предложении ESCAPE с предикатом `LIKE

Поиск таблиц, содержащих в имени знак подчёркивания.В данном случае в качестве управляющего символа задан символ “#”.

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

См. также:

STARTING WITH, CONTAINING, SIMILAR TO.

`STARTING WITH`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

<value> [NOT] STARTING WITH <start-value>

Предикат STARTING WITH ищет строку, которая начинается с символов в его аргументе start-value.Чувствительность к регистру и ударению в STARTING WITH зависит от сортировки (COLLATION) первого аргумента value.

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

Example 1. Использование предиката STARTING WITH

Поиск сотрудников, фамилия которых начинается с “Jo”.

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

См. также:

LIKE.

`CONTAINING`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

<value> [NOT] CONTAINING <substring>

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

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

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

Example 1. Поиск подстроки с использованием предиката CONTAINING

Поиск проектов в именах, которых присутствует подстрока “Map”:

SELECT *
FROM PROJECT
WHERE PROJ_NAME CONTAINING 'map';

В данном случае будет возвращены две строки с именами “AutoMap” и “MapBrowser port”.

Example 2. Поиск внутри даты с использованием предиката CONTAINING

Поиск записей об изменении зарплат с датой содержащей число 84 (в данном случае изменения, которые произошли в 1984 году):

SELECT *
FROM SALARY_HISTORY
WHERE CHANGE_DATE CONTAINING 84;

См. также:

LIKE.

`SIMILAR TO`

Доступно в

DSQL, PSQL.

Синтаксис

string-expression [NOT] SIMILAR TO <pattern> [ESCAPE <escape-char>]

<pattern> ::= регулярное выражение SQL
<escape-char> ::= символ экранирования

Оператор SIMILAR TO проверяет соответствие строки с шаблоном регулярного выражения SQL.В отличие от некоторых других языков для успешного выполнения шаблон должен соответствовать всей строке — соответствие подстроки недостаточно.Если один из операндов имеет значение NULL, то и результат будет NULL.В противном случае результат является TRUE или FALSE.

Синтаксис регулярных выражений SQL

Следующий синтаксис определяет формат регулярного выражения SQL.Это полное и корректное его определение.Он является весьма формальным и довольно длинным и, вероятно, озадачивает тех, кто не имеет опыта работы с регулярными выражениями.Не стесняйтесь пропустить его и начать читать следующий раздел, Создание регулярных выражений, использующий подход от простого к сложному.

<regular expression> ::= <regular term> ['|' <regular term> ...]

<regular term> ::= <regular factor> ...

<regular factor> ::= <regular primary> [<quantifier>]

<quantifier> ::= ? | * | + | '{' <m> [,[<n>]] '}'

<m>, <n> ::= целые положительные числа, если присутвуют оба числа, то <m> <= <n>

<regular primary> ::=
    <character> | <character class> | %
  | (<regular expression>)

<character> ::= <escaped character> | <non-escaped character>

<escaped character> ::=
  <escape-char> <special character> | <escape-char> <escape-char>

<special character> ::= любой из символов []()|^-+*%_?{}

<non-escaped character> ::=
  любой символ за исключением <special character>
  и не эквивалентный <escape-char> (если задан)

<character class> ::=
    '_' | '[' <member> ... ']' | '[^' <non-member> ... ']'
  | '[' <member> ... '^' <non-member> ... ']'

<member>, <non-member> ::= <character> | <range> | <predefined class>

<range> ::= <character>-<character>

<predefined class> ::= '[:' <predefined class name> ':]'

<predefined class name> ::=
  ALPHA | UPPER | LOWER | DIGIT | ALNUM | SPACE | WHITESPACE

Создание регулярных выражений

В этом разделе представлены элементы и правила построения регулярных выражений SQL.

Символы

В регулярных выражениях большинство символов представляет сами себя, за исключением специальных символов (special character):

[ ] ( ) | ^ - + * % _ ? { }

... и управляющих символов (escaped character), если они заданы.

Регулярному выражению, не содержащему специальных или управляющих символов, соответствует только полностью идентичные строки (в зависимости от используемой сортировки). То есть это функционирует точно так же, как оператор “=”:

'Apple' SIMILAR TO 'Apple' -- TRUE
'Apples' SIMILAR TO 'Apple' -- FALSE
'Apple' SIMILAR TO 'Apples' -- FALSE
'APPLE' SIMILAR TO 'Apple' -- в зависимости от сортировки

Шаблоны

Известным SQL шаблонам ‘_’ и ‘%’ соответствует любой единственный символ и строка любой длины, соответственно:

'Birne' SIMILAR TO 'B_rne' -- TRUE
'Birne' SIMILAR TO 'B_ne' -- FALSE
'Birne' SIMILAR TO 'B%ne' -- TRUE
'Birne' SIMILAR TO 'Bir%ne%' -- TRUE
'Birne' SIMILAR TO 'Birr%ne' -- FALSE

Обратите внимание, что шаблон ‘%’ также соответствует пустой строке.

Классы символов

Набор символов, заключённый в квадратные скобки определяют класс символов.Символ в строке соответствует классу в шаблоне, если символ является элементом класса:

'Citroen' SIMILAR TO 'Cit[arju]oen' -- TRUE
'Citroen' SIMILAR TO 'Ci[tr]oen' -- FALSE
'Citroen' SIMILAR TO 'Ci[tr][tr]oen' -- TRUE

Как видно из второй строки классу только соответствует единственный символ, а не их последовательность.

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

'Datte' SIMILAR TO 'Dat[q-u]e' -- TRUE
'Datte' SIMILAR TO 'Dat[abq-uy]e' -- TRUE
'Datte' SIMILAR TO 'Dat[bcg-km-pwz]e' -- FALSE

Предопределённые классы символов

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

[:ALPHA:]: Латинские буквы a…z и A…Z.Этот класс также включает символы с диакритическими знаками при нечувствительных к акцентам сортировках.
[:DIGIT:]: Десятичные цифры 0…9.
[:ALNUM:]: Объединение [:ALPHA:] и [:DIGIT:].
[:UPPER:]: Прописные (в верхнем регистре) латинские буквы A…Z.Также включает в себя символы в нижнем регистре при нечувствительных к регистру сортировках и символы с диакритическими знаками при нечувствительных к акцентам сортировках.
[:LOWER:]: Строчные (в нижнем регистре) латинские буквы a…z.Также включает в себя символы в верхнем регистре при нечувствительных к регистру сортировках и символы с диакритическими знаками при нечувствительных к акцентам сортировках.
[:SPACE:]: Символ пробела (ASCII 32).
[:WHITESPACE:]: Горизонтальная табуляция (ASCII 9), перевод строки (ASCII 10), вертикальная табуляция (ASCII 11), разрыв страницы (ASCII 12), возврат каретки (ASCII 13) и пробел (ASCII 32).

Включение в оператор SIMILAR TO предопределённого класса имеет тот же эффект, как и включение всех его элементов.Использование предопределённых классов допускается только в пределах определения класса.Если вам нужно сопоставление только с предопределённым классом и ничего больше, то поместите дополнительную пару скобок вокруг него.

'Erdbeere' SIMILAR TO 'Erd[[:ALNUM:]]eere' -- TRUE
'Erdbeere' SIMILAR TO 'Erd[[:DIGIT:]]eere' -- FALSE
'Erdbeere' SIMILAR TO 'Erd[a[:SPACE:]b]eere' -- TRUE
'Erdbeere' SIMILAR TO '[[:ALPHA:]]' -- FALSE
'E' SIMILAR TO '[[:ALPHA:]]' -- TRUE

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

'Framboise' SIMILAR TO 'Fra[^ck-p]boise' -- FALSE
'Framboise' SIMILAR TO 'Fr[^a][^a]boise' -- FALSE
'Framboise' SIMILAR TO 'Fra[^[:DIGIT:]]boise' -- TRUE

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

'Grapefruit' SIMILAR TO 'Grap[a-m^f-i]fruit' -- TRUE
'Grapefruit' SIMILAR TO 'Grap[abc^xyz]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abc^de]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abe^de]fruit' -- FALSE
'3' SIMILAR TO '[[:DIGIT:]^4-8]' -- TRUE
'6' SIMILAR TO '[[:DIGIT:]^4-8]' -- FALSE

Наконец, уже упомянутый подстановочный знак ‘_’ является собственным классом символов, соответствуя любому единственному символу.

Кванторы

Вопросительный знак (‘?’) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 0 или 1 раз:

'Hallon' SIMILAR TO 'Hal?on' -- FALSE
'Hallon' SIMILAR TO 'Hal?lon' -- TRUE
'Hallon' SIMILAR TO 'Halll?on' -- TRUE
'Hallon' SIMILAR TO 'Hallll?on' -- FALSE
'Hallon' SIMILAR TO 'Halx?lon' -- TRUE
'Hallon' SIMILAR TO 'H[a-c]?llon[x-z]?' -- TRUE

Звёздочка (‘*’) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 0 или более раз:

'Icaque' SIMILAR TO 'Ica*que' -- TRUE
'Icaque' SIMILAR TO 'Icar*que' -- TRUE
'Icaque' SIMILAR TO 'I[a-c]*que' -- TRUE
'Icaque' SIMILAR TO '_*' -- TRUE
'Icaque' SIMILAR TO '[[:ALPHA:]]*' -- TRUE
'Icaque' SIMILAR TO 'Ica[xyz]*e' -- FALSE

Знак плюс (‘+’) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 1 или более раз:

'Jujube' SIMILAR TO 'Ju_+' -- TRUE
'Jujube' SIMILAR TO 'Ju+jube' -- TRUE
'Jujube' SIMILAR TO 'Jujuber+' -- FALSE
'Jujube' SIMILAR TO 'J[jux]+be' -- TRUE
'Jujube' SIMILAR TO 'J[[:DIGIT:]]+ujube' -- FALSE

Если символ или класс сопровождаются числом, заключённым в фигурные скобки (‘{’ и ‘}’), то для соответствия необходимо повторение элемента точно это число раз:

'Kiwi' SIMILAR TO 'Ki{2}wi' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{2}i' -- TRUE
'Kiwi' SIMILAR TO 'K[ipw]{2}' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{3}' -- TRUE

Если число сопровождается запятой (‘,’), то для соответствия необходимо повторение элемента как минимум это число раз:

'Limone' SIMILAR TO 'Li{2,}mone' -- FALSE
'Limone' SIMILAR TO 'Li{1,}mone' -- TRUE
'Limone' SIMILAR TOto 'Li[nezom]{2,}' -- TRUE

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

'Mandarijn' SIMILAR TO 'M[a-p]{2,5}rijn' -- TRUE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}rijn' -- FALSE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}arijn' -- TRUE

Кванторы ‘?’, ‘*’ и ‘+’ являются сокращением для {0,1}, {0,} и {1,}, соответственно.

Термин ИЛИ

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

'Nektarin' SIMILAR TO 'Nek|tarin' -- FALSE
'Nektarin' SIMILAR TO 'Nektarin|Persika' -- TRUE
'Nektarin' SIMILAR TO 'M_+|N_+|P_+' -- TRUE

Подвыражения

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

'Orange' SIMILAR TO 'O(ra|ri|ro)nge' -- TRUE
'Orange' SIMILAR TO 'O(r[a-e])+nge' -- TRUE
'Orange' SIMILAR TO 'O(ra){2,4}nge' -- FALSE
'Orange' SIMILAR TO 'O(r(an|in)g|rong)?e' -- TRUE

Экранирование специальных символов

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

'Peer (Poire)' SIMILAR TO 'P[^ ]+ \(P[^ ]+\)' ESCAPE '\' -- TRUE
'Pera [Pear]' SIMILAR TO 'P[^ ]+ #[P[^ ]+#]' ESCAPE '#' -- TRUE
'Paron-Appledryck' SIMILAR TO 'P%$-A%' ESCAPE '$' -- TRUE
'Parondryck' SIMILAR TO 'P%--A%' ESCAPE '-' -- FALSE

`IS DISTINCT FROM`

Доступно в

DSQL, PSQL.

Синтаксис

<operand1> IS [NOT] DISTINCT FROM <operand2>

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

IS [NOT] DISTINCT FROM всегда возвращает TRUE или FALSE и никогда UNKNOWN (NULL) (неизвестное значение). Операторы ‘=’ и ‘<>’, наоборот, вернут UNKNOWN (NULL), если один или оба операнда имеют значение NULL.

Table 1. Результаты выполнения различных операторов сравнения
Характеристики операнда	Результаты различных операторов
Характеристики операнда	`=`	`IS NOT DISTINCT FROM`	`<>`	`IS DISTINCT FROM`
Одинаковые значения	`TRUE`	`TRUE`	`FALSE`	`FALSE`
Различные значения	`FALSE`	`FALSE`	`TRUE`	`TRUE`
Оба `NULL`	`UNKNOWN`	`TRUE`	`UNKNOWN`	`FALSE`
Одно `NULL`	`UNKNOWN`	`FALSE`	`UNKNOWN`	`TRUE`

Example 1. Использование предиката IS [NOT] DISTINCT FROM

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

IF (NEW.JOB IS DISTINCT FROM OLD.JOB) THEN
  POST_EVENT 'JOB_CHANGED';

См. также:

[fblangref-commons-isnotboolean], [fblangref-commons-isnotnull].

Логический `IS [NOT]`

Доступно в

DSQL, PSQL.

Синтаксис

<value> IS [NOT] {TRUE | FALSE | UNKNOWN}

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

Для логического типа данных предикат IS [NOT] UNKNOWN эквивалентен IS [NOT] NULL.

Note	Замечание: В правой части предиката могут быть использованы только литералы `TRUE`, `FALSE`, `UNKNOWN`, но не выражения.

Example 1. Использование оператора IS с логическим типом данных

-- Проверка FALSE значения
SELECT * FROM TBOOL WHERE BVAL IS FALSE

ID           BVAL
============ =======
2            <false>

-- Проверка UNKNOWN значения
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN

ID           BVAL
============ =======
3            <null>

`IS [NOT] NULL`

Доступно в

DSQL, PSQL.

Синтаксис

<value> IS [NOT] NULL

Поскольку NULL не является значением, эти операторы не являются операторами сравнения.Оператор IS [NOT] NULL проверяет, что выражение слева имеет значение (IS NOT NULL) или не имеет значения (IS NULL)

Example 1. Использование предиката IS [NOT] NULL

Поиск записей о продажах, для которых не установлена дата отгрузки:

SELECT *
FROM SALES
WHERE SHIP_DATE IS NULL;

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

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

`EXISTS`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

[NOT] EXISTS (<select_stmt>)

Предикат EXISTS использует подзапрос в качестве аргумента.Если результат подзапроса будет содержать хотя бы одну запись, то предикат оценивается как истинный (TRUE), в противном случае предикат оценивается как ложный (FALSE).

Результат подзапроса может содержать несколько столбцов, поскольку значения не проверяются, а просто фиксируется факт наличия строк результата.Данный предикат может принимать только два значения: истина (TRUE) и ложь (FALSE).

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

Example 1. Предикат EXISTS

Найти тех сотрудников, у которых есть проекты.

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

Example 2. Предикат NOT EXISTS

Найти тех сотрудников, у которых нет проектов.

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

`IN`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

<value> [NOT] IN (<select_stmt> | <value_list>)

<value_list> ::= <value_1> [, <value_2> ...]

Предикат IN проверяет, присутствует ли значение выражения слева в указанном справа наборе значений.Набор значений не может превышать 1500 элементов.Предикат IN может быть переписан в следующей эквивалентной форме:

(<value> = <value_1> [OR <value> = <value_2> ...])

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

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

Запросы с использованием предиката IN с подзапросом, можно переписать на аналогичный запрос с использованием предиката EXISTS.Например, следующий запрос:

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

Можно переписать на аналогичный запрос с использованием предиката EXISTS:

SELECT
  model, speed, hd
FROM PC
WHERE
  EXISTS (SELECT *
          FROM product
          WHERE maker = 'A'
            AND product.model = PC.model);

Однако, запрос с использованием NOT IN не всегда даст тот же результат, что запрос NOT EXISTS.Причина заключается в том, что предикат EXISTS всегда возвращает TRUE или FALSE, тогда как предикат IN может вернуть NULL в следующих случаях:

Когда проверяемое значение равно NULL и список в IN не пуст.
Когда проверяемое значение не имеет совпадений в списке IN и одно из значений является NULL.

В этих двух случаях предикат IN вернёт NULL, в то время как соответствующий предикат EXISTS вернёт FALSE.В поисковых условиях или операторе IF оба результата обозначают “провал” и обрабатываются одинаково.

Однако на тех же данных NOT IN вернёт NULL, в то время как EXISTS вернёт TRUE, что приведёт к противоположному результату.

Это можно продемонстрировать следующим примером.

Предположим у вас есть такой запрос:

-- Ищем людей, которые не родились в тот же день, что
-- известные жители Нью-Йорка
SELECT P1.name AS NAME
FROM Personnel P1
WHERE P1.birthday NOT IN (SELECT C1.birthday
                          FROM Celebrities C1
                          WHERE С1.birthcity = 'New York');

Можно предположить, что аналогичный результат даст запрос с использованием предиката NOT EXISTS:

-- Ищем людей, которые не родились в тот же день, что
-- известные жители Нью-Йорка
SELECT P1.name AS NAME
FROM Personnel P1
WHERE NOT EXISTS (SELECT *
                  FROM Celebrities C1
                  WHERE C1.birthcity = 'New York'
                    AND C1.birthday = P1.birthday);

Допустим, что в Нью-Йорке всего один известный житель, и его дата рождения неизвестна.При использовании предиката EXISTS подзапрос внутри него не выдаст результатов, так как при сравнении дат рождения с NULL результатом будет UNKNOWN.Это приведёт к тому, что результат предиката NOT EXISTS будет истинен для каждой строки основного запроса.В то время как результатом предиката NOT IN будет UNKNOWN и ни одна строка не будет выведена.

Example 1. Предикат IN

Найти сотрудников с именами “Pete”, “Ann” и “Roger”:

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

Example 2. Поисковый предикат IN

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

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

См. также:

EXISTS.

`SINGULAR`

Доступно в

DSQL, PSQL, ESQL.

Синтаксис

[NOT] SINGULAR (<select_stmt>)

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

Example 1. Предикат SINGULAR

Найти тех сотрудников, у которых есть только один проект.

SELECT *
FROM employee
WHERE SINGULAR (SELECT *
                FROM
                  employee_project ep
                WHERE
                  ep.emp_no = employee.emp_no)

Количественные предикаты подзапросов

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

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

<value expression> <comparison operator> <quantifier> <subquery>

`ALL`

Доступно в

DSQL, PSQL.

Синтаксис

<value> <op> ALL (<select_stmt>)

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

Example 1. Квантор ALL

Вывести только тех заказчиков, чьи оценки выше, чем у каждого заказчика в Париже.

SELECT *
FROM Customers
WHERE rating > ALL
      (SELECT rating
       FROM Customers
       WHERE city = 'Paris')

Important

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

Тем не менее это нормально согласуется с формальной логикой: если множество пусто, то предикат верен 0 раз, т.е.для каждой строки в множестве.

`ANY` и `SOME`

Доступно в

DSQL, PSQL.

Синтаксис

<value> <op> {ANY | SOME} (<select_stmt>)

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

Example 1. Квантор ANY

Вывести только тех заказчиков, чьи оценки выше, чем у какого-либо заказчика в Риме.

SELECT *
FROM Customers
WHERE rating > ANY
      (SELECT rating
       FROM Customers
       WHERE city = 'Rome')

Выражения

Выражения

Литералы (константы)

Строковые литералы

Альтернативы для апострофов в строковых литералах

Набор символов для строковых литералов

Строковые литералы для двоичных строк

Числовые константы

Шестнадцатеричная нотация чисел

Диапазон значений шестнадцатеричных чисел

Логические литералы

Операторы SQL

Приоритет операторов

Оператор конкатенации

Арифметические операторы

Операторы сравнения

Логические операторы

AT

NEXT VALUE FOR

Условные выражения

CASE

Простой CASE

Поисковый CASE

NULL в выражениях

Выражения возвращающие NULL

NULL в логических выражениях

Подзапросы

Коррелированные подзапросы

Подзапросы возвращающие скалярный результат

Предикаты

Утверждения

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

Другие предикаты сравнения

BETWEEN

LIKE

Трафаретные символы

Использование управляющего символа в предложении ESCAPE

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

STARTING WITH

CONTAINING

SIMILAR TO

Синтаксис регулярных выражений SQL

Создание регулярных выражений

Символы

Шаблоны

Классы символов

Предопределённые классы символов

Кванторы

Термин ИЛИ

Подвыражения

Экранирование специальных символов

IS DISTINCT FROM

Логический IS [NOT]

IS [NOT] NULL

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

EXISTS

IN

SINGULAR

Количественные предикаты подзапросов

ALL

ANY и SOME

`AT`

`NEXT VALUE FOR`

`NULL` в выражениях

Выражения возвращающие `NULL`

`NULL` в логических выражениях

`BETWEEN`

`LIKE`

Использование управляющего символа в предложении `ESCAPE`

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

`STARTING WITH`

`CONTAINING`

`SIMILAR TO`

`IS DISTINCT FROM`

Логический `IS [NOT]`

`IS [NOT] NULL`

`EXISTS`

`IN`

`SINGULAR`

`ALL`

`ANY` и `SOME`