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

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 в следующих случаях:

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

  2. Когда проверяемое значение не имеет совпадений в списке 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)

docnext count = 3

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

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

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

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