FirebirdSQL logo
 COMMENTSОператоры процедурного SQL (PSQL) 

Общие табличные выражения CTE ("WITH …​ AS …​ SELECT")

Общие табличные выражения (Common Table Expressions), сокращённо CTE, описаны как виртуальные таблицы или представления, определённые в преамбуле основного запроса, которые участвуют в основном запросе.Основной запрос может ссылаться на любое CTE из определённых в преамбуле, как и при выборке данных из обычных таблиц или представлений.CTE могут быть рекурсивными, т.е. ссылающимися сами на себя, но не могут быть вложенными.

Синтаксис
<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [   <rows-clause>
    | { [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <cte> [, <cte> ...]

<cte> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]
Table 1. Параметры CTE
Параметр Описание

query-name

Имя табличного выражения.

column-name

Псевдоним (алиас) для столбца табличного выражения.
Примечания

  • Определение CTE может содержать любой правильный оператор SELECT, если он не содержит преамбулы “WITH…​” (операторы WITH не могут быть вложенными);

  • CTE могут использовать друг друга, но ссылки не должны иметь циклов;

  • CTE могут быть использованы в любой части главного запроса или другого табличного выражения и сколько угодно раз;

  • Основной запрос может ссылаться на CTE несколько раз, но с разными алиасами;

  • CTE могут быть использованы в операторах INSERT, UPDATE и DELETE как подзапросы;

  • Если объявленное CTE не использовано, то будет выдано предупреждение “CTE cte is not used in query”. В более ранних версиях вместо предупреждения выдавалась ошибка;

  • CTE могут быть использованы и в PSQL в FOR циклах:

    FOR
  WITH
    MY_RIVERS AS (
      SELECT *
      FROM RIVERS
      WHERE OWNER = 'me'
    )
  SELECT
    NAME,
    LENGTH
  FROM MY_RIVERS
  INTO :RNAME,
       :RLEN
DO
BEGIN
  ...
END
Примеры
Example 1. Запрос с использованием CTE
WITH
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.DEPARTMENT,
  DYB_2008.BUDGET BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPARTMENT D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008
    ON D.DEPT_NO = DYB_2008.DEPT_NO AND
       DYB_2008.FISCAL_YEAR = 2008
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009
    ON D.DEPT_NO = DYB_2009.DEPT_NO AND
       DYB_2009.FISCAL_YEAR = 2009
WHERE EXISTS (SELECT *
              FROM PROJ_DEPT_BUDGET B
              WHERE D.DEPT_NO = B.DEPT_NO)

Рекурсивные CTE

Рекурсивное (ссылающееся само на себя) CTE это UNION, у которого должен быть, по крайней мере, один не рекурсивный элемент, к которому привязываются остальные элементы объединения.Не рекурсивный элемент помещается в CTE первым.Рекурсивные члены отделяются от не рекурсивных и друг от друга с помощью UNION ALL.Объединение не рекурсивных элементов может быть любого типа.

Рекурсивное CTE требует наличия ключевого слова RECURSIVE справа от WITH.Каждый рекурсивный член объединения может сослаться на себя только один раз и это должно быть сделано в предложении FROM.

Главным преимуществом рекурсивных CTE является то, что они используют гораздо меньше памяти и процессорного времени, чем эквивалентные рекурсивные хранимые процедуры.

Выполнение рекурсивного CTE

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

  • Сервер начинает выполнение с не рекурсивного члена;

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

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

Примеры
Example 1. Рекурсивное CTE
WITH RECURSIVE
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  ),
  DEPT_TREE AS (
    SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
    FROM DEPARTMENT
    WHERE HEAD_DEPT IS NULL
    UNION ALL
    SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
    FROM
      DEPARTMENT D
      JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.INDENT || D.DEPARTMENT DEPARTMENT,
  DYB_2008.BUDGET AS BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPT_TREE D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
    (D.DEPT_NO = DYB_2008.DEPT_NO) AND
    (DYB_2008.FISCAL_YEAR = 2008)
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
    (D.DEPT_NO = DYB_2009.DEPT_NO) AND
    (DYB_2009.FISCAL_YEAR = 2009)

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

WITH RECURSIVE
  PEDIGREE (
    CODE_HORSE,
    CODE_FATHER,
    CODE_MOTHER,
    NAME,
    MARK,
    DEPTH
  ) AS (
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      CAST('' AS VARCHAR(80)),
      0
    FROM HORSE
    WHERE
      HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'F' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
      –- ограничение глубины рекурсии
      PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'M' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
      –- ограничение глубины рекурсии
      PEDIGREE.DEPTH < :MAX_DEPTH
  )
SELECT
  CODE_HORSE,
  NAME,
  MARK,
  DEPTH
FROM
  PEDIGREE
Примечания для рекурсивного CTE:

  • В рекурсивных членах объединения не разрешается использовать агрегаты (DISTINCT, GROUP BY, HAVING) и агрегатные функции (SUM, COUNT, MAX и т.п.);

  • Рекурсивная ссылка не может быть участником внешнего объединения OUTER JOIN;

  • Максимальная глубина рекурсии составляет 1024;

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

Список полей SELECT

Список полей содержит одно или более выражений, разделённых запятыми.Результатом каждого выражения является значение соответствующего поля в наборе данных команды SELECT.Исключением является выражение * (“звёздочка”), которое возвращает все поля отношения.

Синтаксис
SELECT
  [...]
  [DISTINCT | ALL] <select-list>
  [...]
  FROM ...

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    <qualifier>.*
  | <value-expression> [COLLATE collation] [[AS] alias]



<value-expression> ::=
    [<qualifier>.]col_name
  | [<qualifier>.]selectable_SP_outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | <other-single-value-expr>
Table 1. Параметры списка полей оператора SELECT
Параметр Описание

qualifier

Имя таблицы (представления) или псевдоним таблицы (представления, хранимой процедуры, производной таблицы).

collation

Существующее имя сортировки (только для выражений символьных типов).

alias

Псевдоним поля.

col_name

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

selectable-SP-outparm

Выходной параметр селективной хранимой процедуры.

literal

Литерал.

context-variable

Контекстная переменная.

function-call

Вызов скалярной, агрегатной или оконной функции.

single-value-subselect

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

CASE-construct

Конструкция CASE.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

Хорошим тоном является уточнять имя поля (или “*”) именем таблицы/представления/хранимой процедуры (или их псевдонимом), к которой это поле принадлежит. Например, relationname.columnname, relationname.*, alias.columnname, alias.*.Уточнение имени становится обязательным в случае, если поле с одним и тем же именем находится в более чем одном отношении, участвующей в объединении.Уточнение для “*” всегда обязательна, если это не единственный элемент в списке столбцов.

Important
Обратите внимание

Алиасы (псевдонимы) заменяют оригинальное имя таблицы, представления или хранимой процедуры: как только определён алиас для соответствующего отношения, использовать оригинальное имя нельзя.

В начало списка полей могут быть добавлены ключевые слова DISTINCT или ALL:

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

  • ALL включает все строки в результирующий набор данных. ALL включено по умолчанию и поэтому редко используется: явное указание поддерживается для совместимости со стандартом SQL.

Выражение COLLATE не изменяет содержимое поля, однако, если указать COLLATE для определённого поля, то это может изменить чувствительность к регистру символов или к акцентам (accent sensitivity), что, в свою очередь, может повлиять на:

  • Порядок сортировки, в случае если это поле указано в выражении ORDER BY;

  • Группировку, в случае если это поле указано в выражении GROUP BY;

  • Количество возвращаемых строк, если используется DISTINCT.

Примеры операторов SELECT с различными типами полей

Простой SELECT использующий только имена полей:

SELECT cust_id, cust_name, phone
FROM customers
WHERE city = 'London'

Запрос с конкатенацией и вызовом функции в списке полей:

SELECT
  'Mr./Mrs. ' || lastname,
  street,
  zip,
  upper(city)
FROM contacts
WHERE date_last_purchase(id) = current_date

Запрос с двумя подзапросами:

SELECT
  p.fullname,
  (SELECT name FROM classes c
   WHERE c.id = p.class) AS class,
  (SELECT name FROM mentors m
   WHERE m.id = p.mentor) AS mentor
FROM pupils p

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

SELECT
  p.fullname,
  c.name AS class,
  m.name AS mentor
FROM pupils p
  JOIN classes c ON c.id = p.class
  JOIN mentors m ON m.id = p.mentor

Этот запрос использует конструкцию CASE для определения корректного обращения, например, при рассылке сообщений конкретному человеку:

SELECT
  CASE upper(sex)
    WHEN 'F' THEN 'Mrs.'
    WHEN 'M' THEN 'Mr.'
    ELSE ''
  END AS title,
  lastname,
  address
FROM employees

Запрос с использованием оконной функции.Выводит сотрудников отранжированных по заработной плате.

SELECT
    id,
    salary,
    name,
    DENSE_RANK() OVER(ORDER BY salary) AS EMP_RANK
FROM employees
ORDER BY salary;

Запрос к хранимой процедуре:

SELECT *
FROM interesting_transactions(2010, 3, 'S')
ORDER BY amount

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

SELECT
  fieldcount,
  COUNT(relation) AS num_tables
FROM
  (SELECT
     r.rdb$relation_name AS relation,
     COUNT(*) AS fieldcount
   FROM rdb$relations r
     JOIN rdb$relation_fields rf
       ON rf.rdb$relation_name = r.rdb$relation_name
   GROUP BY relation)
GROUP BY fieldcount

Запрос к контекстной переменной CURRENT_TIME:

SELECT current_time FROM rdb$database

Для тех, кто не знаком с RDB$DATABASE: это системная таблица, которая всегда существует во всех базах данных Firebird и всегда содержит только одну строку.И, хотя эта таблица не была создана специально для этой цели, стало распространённой практикой среди разработчиков Firebird выполнять запросы к этой таблице в случае, если нужно выполнить запрос, не привязанный ни к какой таблице, в котором результат получается из выражений, указанных в списке полей оператора SELECT.Например:

SELECT
  power(12, 2) AS twelve_squared,
  power(12, 3) AS twelve_cubed
FROM rdb$database

И, наконец, пример запроса к самой таблице RDB$DATABASE, с помощью которого можно получить кодировку по умолчанию данной БД:

SELECT rdb$character_set_name FROM rdb$database
См. также:

Агрегатные функции,Оконные (аналитические) функции,Контекстные переменные,CASE,Подзапросы.

FROM

Выражение FROM определяет источники, из которых будут отобраны данные.В простейшей форме это может быть единственная таблица или представление.Однако источниками также могут быть хранимая процедура, производная таблица или общее табличное выражение (CTE).Различные виды источников могут комбинироваться с использованием разнообразных видов соединений (JOIN).

Этот раздел посвящён запрос из единственного источника.Соединения рассматриваются в следующем разделе.

Синтаксис:
SELECT
...
FROM <table-reference> [, <table-reference> ...]
[...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<derived-table> ::= (<query-expression>)

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]
Table 1. Параметры предложения FROM
Параметр Описание

table-name

Имя таблицы или представления.

query-name

Имя CTE.

package-name

Имя пакета.

procedure-name

Имя селективной хранимой процедуры.

procedure-args

Аргументы селективной хранимой процедуры.

derived-table

Производная таблица.

correlation-name

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

column-name

Алиас столбца производной таблицы.

select-statement

Произвольный SELECT запрос.

Выборка из таблицы или представления

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

Примеры
SELECT id, name, sex, age
FROM actors
WHERE state = 'Ohio'

SELECT *
FROM birds
WHERE type = 'flightless'
ORDER BY family, genus, species

SELECT
  firstname,
  middlename,
  lastname,
  date_of_birth,
  (SELECT name FROM schools s WHERE p.school = s.id) schoolname
FROM pupils p
WHERE year_started = 2012
ORDER BY schoolname, date_of_birth
Important

Если вы дадите таблице или представлению псевдоним (алиас), то вы должны везде использовать этот псевдоним, а не имя таблицы, при обращении к именам столбцов.

Корректное использование:

SELECT PEARS
FROM FRUIT

SELECT FRUIT.PEARS
FROM FRUIT

SELECT PEARS
FROM FRUIT F

SELECT F.PEARS
FROM FRUIT F

Некорректное использование:

SELECT FRUIT.PEARS
FROM FRUIT F

Выборка из селективной хранимой процедуры

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

  • Содержать, по крайней мере, один выходной параметр;

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

Выходные параметры селективной хранимой процедуры с точки зрения команды SELECT соответствуют полям обычной таблицы.

Выборка из хранимой процедуры без входных параметров осуществляется точно так же, как обычная выборка из таблицы:

SELECT *
FROM suspicious_transactions
WHERE assignee = 'Dmitrii'

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

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, '22:30')
WHERE alt >= 20
ORDER BY az, alt

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

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

Если предположить, что процедура visible_stars из предыдущего примера имеет два опциональных параметра spectral_class (varchar(12)) и min_magn (numeric(3,1)), то следующие команды будут корректными:

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, '22:30')

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, '22:30', 4.0)

А вот этот запрос не будет корректным:

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, 4.0)

Алиас для селективной хранимой процедуры указывается после списка параметров:

SELECT
  number,
  (SELECT name FROM contestants c
   WHERE c.number = gw.number)
FROM get_winners('#34517', 'AMS') gw

Если вы указываете поле (выходной параметр) с полным именем процедуры, не включайте в это имя список параметров процедуры:

SELECT number,
  (SELECT name FROM contestants c
   WHERE c.number = get_winners.number)
FROM get_winners('#34517', 'AMS')
См. также:

Хранимые процедуры,CREATE PROCEDURE.

Выборка из производной таблицы (derived table)

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

Синтаксис
<derived table> ::=
  (<select-query>)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]

<lateral-derived-table> ::= LATERAL <derived-table>

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

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

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
          ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
      GROUP BY RELATION)
GROUP BY FIELDCOUNT

Тривиальный пример, демонстрирующий использование псевдонима производной таблицы и списка псевдонимов столбцов (оба опциональные):

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)
Note
Примечания:

  • Производные таблицы могут быть вложенными;

  • Производные таблицы могут быть объединениями и использоваться в объединениях. Они могут содержать агрегатные функции, подзапросы и соединения, и сами по себе могут быть использованы в агрегатных функциях, подзапросах и соединениях. Они также могут быть хранимыми процедурами или запросами из них. Они могут иметь предложения WHERE, ORDER BY и GROUP BY, указания FIRST, SKIP или ROWS и т.д.;

  • Каждый столбец в производной таблице должен иметь имя. Если этого нет по своей природе (например, потому что это — константа), то надо в обычном порядке присвоить псевдоним или добавить список псевдонимов столбцов в спецификации производной таблицы;

  • Список псевдонимов столбцов опциональный, но если он присутствует, то должен быть полным (т.е. он должен содержать псевдоним для каждого столбца производной таблицы);

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

  • Ключевое слово LATERAL позволяет производной таблице ссылаться на поля из ранее перечисленных таблиц в текущем <table reference list>.Подробнее смотрите в разделе Соединение с LATERAL производными таблицами.

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

Предположим, что у нас есть таблица COEFFS, содержащая коэффициенты для ряда квадратных уравнений, которые мы собираемся решить.Она может быть определена примерно так:

CREATE TABLE coeffs (
  a DOUBLE PRECISION NOT NULL,
  b DOUBLE PRECISION NOT NULL,
  c DOUBLE PRECISION NOT NULL,
  CONSTRAINT chk_a_not_zero CHECK (a <> 0)
)

В зависимости от значений коэффициентов a, b и c, каждое уравнение может иметь ноль, одно или два решения.Мы можем найти эти решения с помощью одноуровневого запроса к таблице COEFFS, однако код такого запроса будет громоздким, а некоторые значения (такие, как дискриминанты) будут вычисляться несколько раз в каждой строке.

Если использовать производную таблицу, то запрос можно сделать гораздо более элегантным:

SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM
  (SELECT b, b*b - 4*a*c, 2*a FROM coeffs) (b, D, denom)

Если мы захотим показывать коэффициенты рядом с решениями уравнений, то мы можем модифицировать запрос следующим образом:

SELECT
  a, b, c,
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) sol_2
FROM
  (SELECT a, b, c, b*b - 4*a*c AS D, 2*a AS denom
   FROM coeffs)

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

Note

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

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

результатом этого запроса будет

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Для материализации результата функции GEN_UUID вы можете воспользоваться следующим способом:

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1=0) T;

результатом этого запроса будет

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

или завернуть функцию GEN_UUID в подзапрос

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT
          (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

Эта особенность текущей реализации и она может быть изменена в следующих версиях сервера.

Латеральные производные таблицы

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

Example 1. Запросы с латеральными производными таблицами
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c,
LATERAL (select first 1 city_name, population
         from cities
         where cities.country_name = c.country_name
         order by population desc) AS dt;
select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
LATERAL ( select max(amount) as amount from all_sales
          where all_sales.salesperson_id = salespeople.id
         ) as max_sale,
LATERAL ( select customer_name from all_sales
          where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
        ) as customer_of_max_sale;

Выборка из общих табличных выражений (CTE)

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

Подробно CTE описываются в разделе Общие табличные выражения CTE (WITH …​ AS …​ SELECT),а здесь приведены лишь некоторые примеры использования.

Следующий запрос представляет наш пример с производной таблицей в варианте для общих табличных выражений:

WITH vars (b, D, denom) AS (
  SELECT b, b*b - 4*a*c, 2*a
  FROM coeffs
)
SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM vars

Это не слишком большое улучшение по сравнению с вариантом с производными таблицами (за исключением того, что вычисления проводятся до основного запроса).Мы можем ещё улучшить запрос, исключив двойное вычисление sqrt(D) для каждой строки:

WITH vars (b, D, denom) AS (
  SELECT b, b*b - 4*a*c, 2*a
  FROM coeffs
),
vars2 (b, D, denom, sqrtD) AS (
  SELECT
    b, D, denom,
    IIF (D >= 0, sqrt(D), NULL)
  FROM vars
)
SELECT
  IIF (D >= 0, (-b - sqrtD) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrtD) / denom, NULL) AS sol_2
FROM vars2

Текст запроса выглядит более сложным, но он стал более эффективным (предполагая, что исполнение функции SQRT занимает больше времени, чем передача значений переменных b, d и denom через дополнительное CTE).

Note

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

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T

результатом этого запроса будет

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Для материализации результата функции GEN_UUID вы можете воспользоваться следующим способом:

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE
    UNION ALL
    SELECT NULL FROM RDB$DATABASE WHERE 1=0)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

результатом этого запроса будет

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

или завернуть функцию GEN_UUID в подзапрос

WITH T(X)
AS (SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

Эта особенность текущей реализации и она может быть изменена в следующих версиях сервера.

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

См. также:

Общие табличные выражения CTE (WITH …​ AS …​ SELECT).

Соединения JOIN

Соединения объединяют данные из двух источников в один набор данных.Соединение данных осуществляется для каждой строки и обычно включает в себя проверку условия соединения (join condition) для того, чтобы определить, какие строки должны быть объединены и оказаться в результирующем наборе данных.

Результат соединения также может быть соединён с другим набором данных с помощью следующего соединения.

Существует несколько типов (INNER, OUTER) и классов (квалифицированные, натуральные, и др.) соединений, каждый из которых имеет свой синтаксис и правила.

Синтаксис
SELECT
...
FROM <table-reference> [, <table-reference> ...]
[...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<derived-table> ::= (<query-expression>)

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table>)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join> :: =
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  {   ON <search-condition>
    | USING (<column-name-list>) }
Table 1. Параметры предложения JOIN
Параметр Описание

table-name

Имя таблицы или представления.

query-name

Имя CTE.

package-name

Имя пакета.

procedure-name

Имя селективной хранимой процедуры.

procedure-args

Аргументы селективной хранимой процедуры.

derived-table

Производная таблица.

correlation-name

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

column-name

Имя или алиас столбца источника данных (таблицы, представления, хранимой процедуры, CTE или производной таблицы).

select-statement

Произвольный SELECT запрос.

search-condition

Условие соединения.

column-name-list

Список псевдонимов (алиасов) столбцов производной таблицы или список столбцов по которым происходит эквисоединение.

Внутренние (INNER) и внешние (OUTER) соединения

Соединение всегда соединяет строки из двух наборов данных (которые обычно называются “левый” и “правый”). По умолчанию, только строки, удовлетворяющие условию соединения (то есть, которым соответствует хотя бы одна строка из другого набора строк согласно применяемому условию) попадают в результирующий набор данных.Такой тип соединения называется внутренним (INNER JOIN). Поскольку внутреннее соединение является типом соединения по умолчнию, то ключевое слово INNER можно опустить.

Предположим, у нас есть 2 таблицы:

Таблица А
ID S

87

Just some text

35

Silence
Таблица B
CODE X

-23

56.7735

87

416.0

Если мы соединим эти таблицы с помощью вот такого запроса:

SELECT *
FROM A
JOIN B ON A.id = B.code

то результат будет:

ID S CODE X

87

Just some text

87

416.0

То есть, первая строка таблицы А была соединена со второй строкой таблицы B, потому что вместе они удовлетворяют условию соединения “A.id = B.code”. Другие строки не имеют соответствия и поэтому не включаются в соединение.Помните, что умолчанию соединение всегда внутреннее (INNER).

Мы можем сделать это явным, указав тип соединения:

SELECT *
FROM A
INNER JOIN B ON A.id = B.code

но обычно слово INNER опускается.

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

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

ID S CODE X

87

Just some text

87

416.0

87

Just some text

87

-1.0

-23

Don’t know

-23

56.7735

-23

Still don’t know

-23

56.7735

-23

I give up

-23

56.7735

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

Внешнее левое соединение (LEFT OUTER) включает все записи из левого набора данных, и те записи из правого набора, которые удовлетворяют условию соединения.

Внешнее правое соединение (RIGHT OUTER) включает все записи из правого набора данных и те записи из левого набора данных, которые удовлетворяют условию соединения.

Полное внешнее соединение (FULL OUTER) включает все записи из обоих наборов данных.

Во всех внешних соединениях, “дыры” (то есть поля набора данных, в которых нет соответствующей записи) заполняются NULL.

Для обозначения внешнего соединения используются ключевые слова LEFT, RIGHT или FULL с необязательным ключевым словом OUTER.

Рассмотрим различные внешние соединения на примере запросов с указанными выше таблицами A и B:

SELECT *
FROM A
LEFT OUTER JOIN B ON A.id = B.code

то же самое

SELECT *
FROM A
LEFT JOIN B ON A.id = B.code
ID S CODE X

87

Just some text

87

416.0

235

Silence

<null>

<null>

 
SELECT *
FROM A
RIGHT OUTER JOIN B ON A.id = B.code

то же самое

SELECT *
FROM A
RIGHT JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

 
SELECT *
FROM A
FULL OUTER JOIN B ON A.id = B.code

то же самое

SELECT *
FROM A
FULL JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

235

Silence

<null>

<null>

Квалифицированные соеденения

Синтаксис квалифицированного соединения требует указания условия соединения записей.Это условие указывается явно в предложении ON или неявно при помощи предложения USING.

Синтаксис
<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition> ::= ON <condition> | USING (<column-list>)
Соединения с явными условиями

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

Довольно часто, это условие — проверка на равенство (или ряд проверок на равенство объединённых оператором AND) использующая оператор "=". Такие соединения называются эквисоединениями.(Примеры в главе Внутренние (INNER) и внешние (OUTER) соединения были эквисоединениями).

Примеры соединений с явными условиями:

/*
 * Выборка всех заказчиков из города Детройт, которые
 * сделали покупку.
 */
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Тоже самое, но включает в выборку заказчиков, которые
 * не совершали покупки.
 */
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Для каждого мужчины выбрать женщин, которые выше него.
 * Мужчины, для которых такой женщины не существуют,
 * не будут выключены в выборку.
 */
SELECT
    m.fullname AS man,
    f.fullname AS woman
FROM males m
JOIN females f ON f.height > m.height

/*
 * Выборка всех учеников, их класса и наставника.
 * Ученики без наставника буду включены в выборку.
 * Ученики без класса не будут включены в выборку.
 */
SELECT
    p.firstname,
    p.middlename,
    p.lastname,
    c.name,
    m.name
FROM pupils p
JOIN classes c ON c.id = p.class
LEFT JOIN mentors m ON m.id = p.mentor
Соединения именованными столбцами

Эквисоединения часто сравнивают столбцы, которые имеют одно и то же имя в обеих таблицах.Для таких соединений мы можем использовать второй тип явных соединений, называемый соединением именованными столбцами (Named Columns Joins). Соединение именованными столбцами осуществляются с помощью предложения USING, в котором перечисляются только имена столбцов.

Note

Соединения именованными столбцами доступны только в диалекте 3.

Таким образом, следующий пример:

SELECT *
FROM flotsam f
  JOIN jetsam j
    ON f.sea = j.sea AND f.ship = j.ship

можно переписать так:

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

что значительно короче.Результирующий набор несколько отличается, по крайней мере, при использовании "SELECT *":

  • Результат соединения с явным условием соединения в предложении ON будет содержать каждый из столбцов SEA и `SHIP дважды: один раз для таблицы FLOTSAM и один раз для таблицы JETSAM. Очевидно, что они будут иметь они и те же значения;

  • Результат соединения именованными столбцами, с помощью предложения USING, будет содержать эти столбцы один раз.

Если вы хотите получить в результате соединения именованными столбцами все столбцы, перепишите запрос следующим образом:

SELECT f.*, j.*
FROM flotsam f
JOIN jetsam j USING (sea, ship)

Для внешних (OUTER) соединений именованными столбцами, существуют дополнительные нюансы, при использовании “SELECT *” или неполного имени столбца.Если столбец строки из одного источника не имеет совпадений со столбцом строки из другого источника, но все равно должен быть включён результат из-за инструкций LEFT, RIGHT или FULL, то объединяемый столбец получит не NULL значение.Это достаточно справедливо, но теперь вы не можете сказать из какого набора левого, правого или обоих пришло это значение.Это особенно обманывает, когда значения пришли из правой части набора данных, потому что “*” всегда отображает для комбинированных столбцов значения из левой части набора данных, даже если используется RIGHT соединение.

Является ли это проблемой, зависит от ситуации.Если это так, используйте “f.*, j.*” подход, продемонстрированный выше, где f и j имена или алиасы двух источников.Или лучше вообще избегать “*” в серьёзных запросах и перечислять все имена столбцов для соединяемых множеств.Такой подход имеет дополнительное преимущество, заставляя вас думать, о том какие данные вы хотите получить и откуда.

Вся ответственность за совместимость типов столбцов между соединяемыми источниками, имена которых перечислены в предложении USING, лежит на вас.Если типы совместимы, но не равны, то Firebird преобразует их в тип с более широким диапазоном значений перед сравнением.Кроме того, это будет типом данных объединённого столбца, который появится в результирующем наборе, если используются “SELECT *” или неполное имя столбца.Полные имена столбцов всегда будут сохранять свой первоначальный тип данных.

Tip

Если при соединении именованными столбцами вы используете столбцы соединения в условии отбора WHERE, то всегда используйте уточнённые имена столбцов.В противном случае индекс по этому столбцу не будет задействован.

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE x = 0;
PLAN JOIN (A NATURAL, B INDEX (RDB$2))

однако

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE a.x = 0; -- или 'b.x'
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE b.x = 0;
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Дело в том, неуточнённый столбец в данном случае неявно заменяется на COALESCE(a.x, b.x).Этот хитрый трюк применяется для устранения неоднозначности имён столбцов, но он же мешает применению индекса.

Естественные соединения (NATURAL JOIN)

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

Note

Естественные соединения доступны только в диалекте 3.
Синтаксис
<natural-join> ::= NATURAL [<join-type>] JOIN <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Даны две таблицы:

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

Естественное соединение таблиц TA и TB будет происходить по столбцам a и ins_date и два следующих оператора дадут один и тот же результат:

SELECT *
FROM TA
NATURAL JOIN TB;

SELECT *
FROM TA
JOIN TB USING (a, ins_date);

Как и все соединения, естественные соединения являются внутренними соединениями по умолчанию, но вы можете превратить их во внешние соединения, указав LEFT, RIGHT или FULL перед ключевым словом JOIN.

Important
Внимание

Если в двух исходных таблицах не будут найдены одноименные столбцы, то будет выполнен CROSS JOIN.

Перекрёстное соединение (CROSS JOIN)

Перекрёстное соединение или декартово произведение.Каждая строка левой таблицы соединяется с каждой строкой правой таблицы.

Синтаксис
<cross-join> ::=
    <table-reference> [, <table-reference> ...]
  | <table-reference> CROSS JOIN <table-primary>

Обратите внимание, что синтаксис с использованием запятой является устаревшим.

Перекрёстное соединение двух наборов эквивалентно их соединению по условию тавтологии (условие, которое всегда верно).

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

SELECT *
FROM TA
CROSS JOIN TB;

SELECT *
FROM TA
JOIN TB ON 1 = 1;

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

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

SELECT
    m.name,
    s.size,
    c.name
FROM materials m
CROSS JOIN sizes s
CROSS JOIN colors c
Неявные соединения

В стандарте SQL-89 таблицы, участвующие в соединении, задаются списком с разделяющими запятыми в предложении FROM.Условия соединения задаются в предложении WHERE среди других условий поиска.Такие соединения называются неявными.

Синтаксис неявного соединения может осуществлять только внутренние соединения.

Пример неявного соединения:

/*
 * Выборка всех заказчиков из города Детройт, которые
 * сделали покупку.
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'
Important

В настоящее время синтаксис неявных соединений не рекомендуется к использованию.
Смешивание явного и неявного соединения

Смешивание явных и неявных соединений не рекомендуется, но позволяется.Некоторые виды смешивания запрещены в Firebird.

Например, такой запрос вызовет ошибку "Column does not belong to referenced table"

SELECT *
FROM
TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Это происходит потому, что явный JOIN не может видеть таблицу TA.Однако следующий запрос будет выполнен без ошибок, поскольку изоляция не нарушена.

SELECT *
FROM
TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Неоднозначные имена полей в соединениях

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

SELECT a, b, c
FROM TA
JOIN TB ON TA.a = TB.a

Существует одно исключение из этого правила: соединения по именованным столбцам и естественные соединения, которые используют неполное имя поля в процессе подбора, могут использоваться законно.Это же относится и к одноименным объединяемым столбцам.Для соединений по именованным столбцам эти столбцы должны быть перечислены в предложении USING.Для естественных соединений это столбцы, имена которых присутствуют в обеих таблицах.Но снова замечу, что, особенно во внешних соединениях, плоское имя colname является не всегда тем же самым что left.colname или right.colname.Типы данных могут отличаться, и один из полных столбцов может иметь значение NULL, в то время как другой нет.В этом случае значение в объединённом, неполном столбце может замаскировать тот факт, что одно из исходных значений отсутствует.

Соединения с хранимыми процедурами

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

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

SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1

Запрос же написанный следующим образом вызовет ошибку

SELECT *
FROM MY_PROC(MY_TAB.F)
JOIN MY_TAB ON 1 = 1

Соединения с LATERAL производными таблицами

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

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

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

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

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

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
CROSS JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M

другой вариант написание этого запроса

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE,
     LATERAL(SELECT
               *
             FROM MEASURE
             WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
             ORDER BY MEASURE.BYDATE DESC
             FETCH FIRST ROW ONLY) M

Если необходимо выводить лошадей, не зависимо есть ли у них хотя бы один промер, то необходимо заменить CROSS JOIN на LEFT JOIN:

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
LEFT JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M ON TRUE

WHERE

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

Условие в предложении WHERE часто называют условием поиска, выражением поиска или просто поиск.

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

Условие поиска может также содержать локальные (PSQL) или хост (ESQL) имена переменных, предваряемых двоеточием.

Синтаксис
SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]
Table 1. Параметры предложения WHERE
Параметр Описание

search-condition

Логическое выражение возвращающее TRUE, FALSE и возможно UNKNOWN (NULL).

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

Примеры
SELECT genus, species
FROM mammals
WHERE family = 'Felidae'
ORDER BY genus;

SELECT *
FROM persons
WHERE birthyear IN (1880, 1881)
   OR birthyear BETWEEN 1891 AND 1898;

SELECT name, street, borough, phone
FROM schools s
WHERE EXISTS (SELECT * FROM pupils p WHERE p.school = s.id)
ORDER BY borough, street;

SELECT *
FROM employees
WHERE salary >= 10000 AND position <> 'Manager';

SELECT name
FROM wrestlers
WHERE region = 'Europe'
  AND weight > ALL (SELECT weight FROM shot_putters
                    WHERE region = 'Africa');

SELECT id, name
FROM players
WHERE team_id = (SELECT id FROM teams
                 WHERE name = 'Buffaloes');

SELECT SUM (population)
FROM towns
WHERE name LIKE '%dam'
  AND province CONTAINING 'land';

SELECT pass
FROM usertable
WHERE username = current_user;

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

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

CHILD MARBLES

Anita

23

Bob E.

12

Chris

<null>

Deirdre

1

Eve

17

Fritz

0

Gerry

21

Hadassah

<null>

Isaac

6

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

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

SELECT LIST(child) FROM marbletable WHERE marbles > 10

вы получите имена Anita, Bob E., Eve и Gerry.Все эти дети имеют более чем 10 шариков.

Если вы отрицаете выражение:

SELECT LIST(child) FROM marbletable WHERE NOT marbles > 10

запрос вернёт Deirdre, Fritz и Isaac.Chris и Hadassah не будут включены в выборку, так как не известно 10 у них шариков или меньше.Если вы измените последний запрос так:

SELECT LIST(child) FROM  marbletable WHERE marbles <= 100

результат будет тем же самым, поскольку выражение NULL <= 10 даёт UNKNOWN.Это не то же самое что TRUE, поэтому Chris и Hadassah не отображены.Если вы хотите что бы в списке были перечислены все "бедные" дети, то измените запрос следующим образом:

SELECT LIST(child)
FROM marbletable
WHERE marbles <= 10 OR marbles IS NULL

Теперь условие поиска становится истинным для Chris и Hadassah, потому что условие “marbles is null” возвращает TRUE в этом случае.Фактически, условие поиска не может быть NULL ни для одного из них.

Наконец, следующие два примера SELECT запросов с параметрами в условии поиска.Как определяются параметры запроса и возможно ли это, зависит от приложения.Обратите внимание, что запросы подобные этим не могут быть выполнены немедленно, они должны быть предварительно подготовлены.После того как параметризованный запрос был подготовлен, пользователь (или вызывающий код) может подставить значения параметров и выполнить его многократно, подставляя перед каждым вызовом новые значения параметров.Как вводятся значения параметров, и проходят ли они предварительную обработку зависит от приложения.В GUI средах пользователь, как правило, вводит значения параметров через одно и более текстовых полей, и щелкает на кнопку "Execute", "Run" или "Refresh".

SELECT name, address, phone
FROM stores
WHERE city = ? AND class = ?

SELECT *
FROM pants
WHERE model = :model AND size = :size AND color = :col

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

GROUP BY

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

Если список выборки содержит только агрегатные столбцы или столбцы, значения которых не зависит от отдельных строк основного множества, то предложение GROUP BY необязательно.Когда предложение GROUP BY опущено, результирующее множество будет состоять из одной строки (при условии, что хотя бы один агрегатный столбец присутствует).

Если в списке выборки содержатся как агрегатные столбцы, так и столбцы, чьи значения зависит от выбираемых строк, то предложение GROUP BY становится обязательным.

Синтаксис
SELECT ...
FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position
Table 1. Параметры предложения GROUP BY
Параметр Описание

non-aggr-expression

Любое не агрегатное выражение, которое не включено в список выборки, т.е.невыбираемые столбцы из набора источника или выражения, которые не зависит от набора данных вообще.

column-copy

Дословная копия выражения из списка выбора, не содержащего агрегатной функции.

column-alias

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

column-position

Номер позиции выражения (столбца) из списка выбора, не содержащего агрегатной функции.

Общее правило гласит, что каждый не агрегированный столбец в SELECT списке, должен быть так же включён в GROUP BY список.Вы можете это сделать тремя способами:

  1. Копировать выражение дословно из списка выбора, например “class” или “'D:' || upper(doccode)”;

  2. Указать псевдоним, если он существует;

  3. Задать положение столбца в виде целого числа, которое находится в диапазоне от 1 до количества столбцов в списке SELECT. Целые значения, полученные из выражений, параметров или просто инварианты будут использоваться в качестве таковых в группировке. Они не будут иметь никакого эффекта, поскольку их значение одинаково для каждой строки.

Important

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

В дополнении к требуемым элементам, список группировки так же может содержать:

  • Столбцы исходной таблицы, которые не включены в список выборки SELECT, или неагрегатные выражения, основанные на таких столбцах. Добавление таких столбцов может дополнительно разбить группы. Но так как эти столбцы не в списке выборки SELECT, вы не можете сказать, какому значению столбца соответствует значение агрегированной строки. Таким образом, если вы заинтересованы в этой информации, вы так же должны включить этот столбец или выражение в список выборки SELECT, что возвращает вас к правилу "каждый не агрегированный столбце в списке выборки SELECT должен быть включён в список группировки `GROUP BY`";

  • Выражения, которые не зависят от данных из основного набора, т.е. константы, контекстные переменные, некоррелированные подзапросы, возвращающие единственное значение и т.д. Это упоминается только для полноты картины, т.к. добавление этих элементов является абсолютно бессмысленным, поскольку они не повлияют на группировку вообще. "Безвредные, но бесполезные" элементы так же могут фигурировать в списке выбора SELECT без их копирования в список группировки GROUP BY.

Примеры

Когда в списке выбора SELECT содержатся только агрегатные столбцы, предложение GROUP BY необязательно:

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'

Этот запрос вернёт одну строку с указанием количества студентов мужского пола и их средний возраст.Добавление выражения, которое не зависит от строк таблицы STUDENTS, ничего не меняет:

SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M'

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

Тем не менее в обоих приведённых выше примерах это разрешено.Это совершенно справедливо и для запроса:

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

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

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

SELECT class, COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

Теперь у нас есть полезный запрос.Обратите внимание, что добавление столбца CLASS делает предложение GROUP BY обязательным.Мы не можем удалить это предложение, так же мы не можем удалить столбец CLASS из списка столбцов.

Результат последнего запроса будет выглядеть примерно так:

CLASS COUNT AVG

2A

12

13.5

2B

9

13.9

3A

11

14.6

3B

12

14.4

…​

…​

…​

Заголовки “COUNT” и “AVG” не очень информативны.В простейшем случае вы можете обойти это, но лучше, если мы дадим им значимые имена с помощью псевдонимов:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class

Как вы помните из формального синтаксиса списка столбцов, ключевое слово AS не является обязательным.

Добавление большего не агрегированных (или точнее строчно зависимых) столбцов требуется добавления их в предложения GROUP BY тоже.Например, вы хотите видеть вышеуказанную информацию о девочках то же, и хотите видеть разницу между интернатами и студентами дневного отделения:

SELECT
    class,
    sex,
    boarding_type,
    COUNT(*) AS anumber,
    AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type
CLASS SEX BOARDING_TYPE ANUMBER AVG_AGE

2A

F

BOARDING

9

13.3

2A

F

DAY

6

13.5

2A

M

BOARDING

7

13.6

2A

M

DAY

5

13.4

2B

F

BOARDING

11

13.7

2B

F

DAY

5

13.7

2B

M

BOARDING

6

13.8

…​

…​

…​

…​

…​

Каждая строка в результирующем наборе соответствует одной конкретной комбинации переменных CLASS, SEX и BOARDING_TYPE.Агрегированные результаты — количество и средний возраст — приведены для каждой из конкретизированной группы отдельно.В результате запроса вы не можете увидеть обобщённые результаты для мальчиков отдельно или для студентов дневного отделения отдельно.Таким образом, вы должны найти компромисс.Чем больше вы добавляете неагрегатных столбцов, тем больше вы конкретизируете группы, и тем больше вы упускаете общую картину из виду.Конечно, вы все ещё можете получить “большие” агрегаты, с помощью отдельных запросов.

HAVING

Так же, как и предложение WHERE ограничивает строки в наборе данных, теми которые удовлетворяют условию поиска, с той разницей, что предложение HAVING накладывает ограничения на агрегированные строки сгруппированного набора.Предложение HAVING не является обязательным и может быть использовано только в сочетании с предложением GROUP BY.

Условие(я) в предложении HAVING может ссылаться на:

  • Любой агрегированный столбец в списке выбора SELECT. Это наиболее широко используемый случай;

  • Любое агрегированное выражение, которое не находится в списке выбора SELECT, но разрешено в контексте запроса. Иногда это полезно;

  • Любой столбец в списке GROUP BY. Однако более эффективно фильтровать не агрегированные данные на более ранней стадии в предложении WHERE;

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

Предложение HAVING не может содержать:

  • Не агрегированные выражения столбца, которые не находятся в списке GROUP BY;

  • Позицию столбца. Целое число в предложении HAVING – просто целое число;

  • Псевдонимы столбца –- даже если они появляются в предложении GROUP BY.

Примеры

Перестроим наши ранние примеры.Мы можем использовать предложение HAVING для исключения малых групп студентов:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING COUNT(*) >= 5

Выберем только группы, которые имеют минимальный разброс по возрасту 1.2 года:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING MAX(age) - MIN(age) > 1.2

Обратите внимание, что если вас действительно интересует эта информация, то неплохо бы включить в список выбора min(age) и max(age) или выражение max(age) – min(age).

Следующий запрос отбирает только учеников 3 класса:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING class STARTING WITH '3'

Однако гораздо лучше переместить это условие в предложение WHERE:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M' AND class STARTING WITH '3'
GROUP BY class

WINDOW

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

Синтаксис
<query spec> ::=
  SELECT
    [<first clause>] [<skip clause>]
    [<distinct clause>]
    <select list>
    <from clause>
    [<where clause>]
    [<group clause>]
    [<having clause>]
    [<named windows clause>]
    [<order clause>]
    [<rows clause>]
    [<offset clause>] [<limit clause>]
    [<plan clause>]

<named windows clause> ::=
  WINDOW <window definition> [, <window definition>] ...

<window definition> ::=
  window-name AS <window specification>

<window specification> ::=
   ([window-name] [<window partition>] [<window order>] [<window frame>])


<window partition> ::= PARTITION BY <expr> [, <expr> ...]

<window order> ::=
  ORDER BY <expr> [<direction>] [<nulls placement>]
        [, <expr> [<direction>] [<nulls placement>] ...]

<direction> ::= {ASC | DESC}

<nulls placement> ::= NULLS {FIRST | LAST}

<window frame> ::=
  {ROWS | RANGE} <window frame extent>

<window frame extent> ::=
  <window frame preceding> | <window frame between>

<window frame preceding> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window frame between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <expr> PRECEDING | <expr> FOLLOWING | CURRENT ROW }
      AND { UNBOUNDED FOLLOWING | <expr> PRECEDING | <expr> FOLLOWING | CURRENT ROW }

Имя окна может быть использовано в предложении OVER для ссылки на определение окна, кроме того оно может бытьиспользовано в качестве базового окна для другого именованного или встроенного (в предложении OVER) окна.Окна с рамкой (с предложениями RANGE и ROWS) не могут быть использованы в качестве базового окна, но могут бытьиспользованы в предложении OVER window_name. Окно, которое использует ссылку на базовое окно, не может иметь предложение PARTITION BY и не может переопределять сортировку с помощью предложения ORDER BY.

Примеры
Example 1. Использование именованных окон
SELECT
    id,
    department,
    salary,
    count(*) OVER w1,
    first_value(salary) OVER w2,
    last_value(salary) OVER w2,
    sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary)
ORDER BY department, salary;
См. также:

Оконные (аналитические) функции.

PLAN

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

Синтаксис
PLAN <plan-expr>

<plan-expr> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-item>

<sorted-item> ::= SORT (<plan-item>)

<joined-item> ::= JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<hash-item> ::= HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expr>

<basic-item> ::= <relation> {
    NATURAL
  | INDEX (<indexlist>)
  | ORDER index [INDEX (<indexlist>)]
}

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]
Table 1. Параметры предложения PLAN
Параметр Описание

table

Имя таблицы или её алиас.

view

Имя представления.

index

Имя индекса.

Каждый раз, когда пользователь отправляет запрос ядру Firebird, оптимизатор вычисляет стратегию извлечения данных.Большинство клиентов Firebird имеют возможность отобразить пользователю план извлечения данных.В собственном инструменте isql это делается с помощью команды SET PLAN ON.Если вы хотите только изучить план запроса без его выполнения, то вам необходимо ввести команду SET PLANONLY ON, после чего будут извлекаться планы запросов без их выполнения.Для возврата isql в режим выполнения запросов введите команду SET PLANONLY OFF.

Note

Более подробный план можно получить при включении расширенного плана.В isql это делается с помощью команды SET EXPLAIN ON.Этот план выводит более подробную информацию о методах доступа используемых оптимизатором, однако его нельзя включить в запрос.Описание расширенного плана выходит за рамки данного руководства.

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

Простые планы

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

SELECT * FROM students
PLAN (students NATURAL)

План в EXPLAIN форме:

Select Expression
  -> Table "STUDENTS" Full Scan

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

SELECT *
FROM students
WHERE class = '3C'
PLAN (students INDEX (ix_stud_class))

План в EXPLAIN форме:

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Bitmap
              -> Index "IX_STUD_CLASS" Range Scan (full match)

Директива INDEX может использоваться также для условий соединения (которые будут обсуждаться чуть позже). Она содержит список индексов, разделённых запятыми.

Директива ORDER определяет индекс, который используется при сортировке набора данных, если присутствуют предложения ORDER BY или GROUP BY:

SELECT *
FROM students
PLAN (students ORDER pk_students)
ORDER BY id

План в EXPLAIN форме:

Select Expression
  -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan

Инструкции ORDER и INDEX могут быть объединены:

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER pk_students INDEX (ix_stud_class))
ORDER BY id

План в EXPLAIN форме:

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "PK_STUDENTS" Full Scan
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

В инструкциях ORDER и INDEX разрешено указывать один и тот же индекс:

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER ix_stud_class INDEX (ix_stud_class))
ORDER BY class

План в EXPLAIN форме:

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Для сортировки наборов данных, когда невозможно использовать индекс (или вы хотите подавить его использование), уберите инструкцию ORDER и предварите выражение плана инструкцией SORT:

SELECT *
FROM students
PLAN SORT (students NATURAL)
ORDER BY name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 128, key length: 56)
      -> Table "STUDENTS" Full Scan

Или когда индекс используется для поиска:

SELECT *
FROM students
WHERE class >= '3'
PLAN SORT (students INDEX (ix_stud_class))
ORDER BY name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 136, key length: 56)
      -> Filter
          -> Table "STUDENTS" Access By ID
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Обратите внимание, что инструкция SORT, в отличие от ORDER, находится за пределами скобок.Это отражает тот факт, что строки данных извлекаются неотсортированными и сортируются впоследствии.

При выборке из представления указывается само представление и участвующее в нем таблица.Например, если у вас есть представление FRESHMEN, которое выбирает только студентов первокурсников:

SELECT *
FROM freshmen
PLAN (freshmen students NATURAL)

План в EXPLAIN форме:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Или, например:

SELECT *
FROM freshmen
WHERE id > 10
PLAN SORT (freshmen students INDEX (pk_students))
ORDER BY name DESC

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 144, key length: 24)
      -> Filter
          -> Table "STUDENTS" as "FRESHMEN" Access By ID
              -> Bitmap
                  -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Обратите внимание: если вы назначили псевдоним таблице или представлению, то в предложении PLAN необходимо использовать псевдоним, а не оригинальное имя.

Составные планы

Если вы делаете соединение, то вы можете указать индекс, который будет использоваться для сопоставления.Кроме того, вы должны использовать директиву JOIN для двух потоков в плане:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s NATURAL, c INDEX (pk_classes))

План в EXPLAIN форме:

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

То же самое соединение, отсортированное по индексированному столбцу:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s ORDER pk_students, c INDEX (pk_classes))
ORDER BY s.id

План в EXPLAIN форме:

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Access By ID
          -> Index "PK_STUDENTS" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

И соединение, отсортированное не по индексированному столбцу:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN SORT (JOIN (S NATURAL, c INDEX (pk_classes))))
ORDER BY s.name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 152, key length: 12)
      ->  Nested Loop Join (inner)
          -> Table "STUDENTS" as "S" Full Scan
          -> Filter
              -> Table "CLASSES" as "C" Access By ID
                  -> Bitmap
                      -> Index "PK_CLASSES" Unique Scan

Соединение с добавленным условием поиска:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (s INDEX (fk_student_class), c INDEX (pk_classes)))
ORDER BY s.name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 152, key length: 12)
     ->  Nested Loop Join (inner)
         -> Filter
            -> Table "STUDENTS" as "S" Access By ID
               -> Bitmap
                  -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
         -> Filter
            -> Table "CLASSES" as "C" Access By ID
               -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

То же самое, но используется левое внешнее соединение:

SELECT s.id, s.name, s.class, c.mentor
FROM classes c
LEFT JOIN students s ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (c NATURAL, s INDEX (fk_student_class)))
ORDER BY s.name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 192, key length: 56)
      -> Filter
         ->  Nested Loop Join (outer)
             -> Table "CLASSES" as "C" Full Scan
             -> Filter
                -> Table "STUDENTS" as "S" Access By ID
                   -> Bitmap
                      -> Index "FK_STUDENT_CLASS" Range Scan (full match)

Если нет доступных индексов для условия соединения (или вы не хотите его использовать), то возможно соединение потоков с помощью метода HASH или MERGE.

Для соединения методом HASH в плане вместо директивы JOIN используется директива HASH.В этом случае меньший (ведомый) поток целиком вычитывается во внутренний буфер.В процессе чтения к каждому ключу связи применяется хеш-функция и пара {хеш, указатель в буфере} записывается в хеш-таблицу.После чего читается ведущий поток и его ключ связи апробируется в хеш-таблице.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN HASH (c NATURAL, s NATURAL)

План в EXPLAIN форме:

Select Expression
    -> Filter
        -> Hash Join (inner)
            -> Table "STUDENTS" as "S" Full Scan
            -> Record Buffer (record length: 145)
                -> Table "CLASSES" as "C" Full Scan

При выполнении соединения методом MERGE план должен сначала отсортировать оба потока по соединяемым столбцам и затем произвести слияние.Это достигается с помощью директив SORT (которую вы уже встречали) и MERGE используемую вместо JOIN.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN MERGE (SORT (c NATURAL), SORT (s NATURAL))

Добавление предложения ORDER BY означает, что результат слияния также должен быть отсортирован:

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN SORT (MERGE (SORT (c NATURAL), SORT (s NATURAL)))
ORDER BY c.name, s.id

И наконец, мы добавляем условие поиска на двух индексированных столбцах таблицы STUDENTS:

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
WHERE s.id < 10 AND s.class <= '2'
PLAN SORT (MERGE (SORT (c NATURAL),
                  SORT (s INDEX (pk_students, fk_student_class))))
ORDER BY c.name, s.id

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

Наконец, вместо MERGE вы можете писать SORT MERGE.Поскольку это не имеет абсолютно никакого значения и может создать путаницу с “настоящей” директивой SORT (которая действительно имеет значение), то вероятно лучше придерживаться простой директивы MERGE.

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

SELECT *
FROM COLOR
WHERE EXISTS(
             SELECT *
             FROM HORSE
             WHERE HORSE.CODE_COLOR = COLOR.CODE_COLOR
             PLAN (HORSE INDEX (FK_HORSE_COLOR)))
PLAN(COLOR NATURAL)

UNION

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

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

Синтаксис
<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [<rows-clause> | {[<result-offset-clause>] [<fetch-first-clause>]}]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body>
      [<order-by-clause>]
      [<result-offset-clause>] [<fetch-first-clause>]
    )

<query-specification> ::=
  SELECT
    <limit-clause>
    [{ ALL | DISTINCT }] <select-list>
  FROM <table-reference> [, <table-reference> ...]
  [WHERE <search-condition>]
  [GROUP BY <value-expression> [, <value-expression> ...]]
  [HAVING <search-condition>]
  [WINDOW <window-definition> [, <window-definition> ...]]
  [PLAN <plan-expression>]

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

Если объединение имеет предложение ORDER BY, то единственно возможными элементами сортировки являются целочисленные литералы, указывающие на позиции столбцов, необязательно сопровождаемые ASC | DESC и/или NULLS {FIRST | LAST} директивами.Это так же означает, что вы не можете упорядочить объединение ничем, что не является столбцом объединения.(Однако вы можете завернуть его в производную таблицу, которая даст вам все обычные параметры сортировки.)

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

Примеры

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

SELECT id, title, artist, len, 'CD' AS medium
FROM cds
UNION
SELECT id, title, artist, len, 'LP'
FROM records
UNION
SELECT id, title, artist, len, 'MC'
FROM cassettes
ORDER BY 3, 2 -- artist, title

Если id, title, artist и length – единственные поля во всех участвующих таблицах, то запрос может быть записан так:

SELECT c.*, 'CD' AS medium
FROM cds c
UNION
SELECT r.*, 'LP'
FROM records r
UNION
SELECT c.*, 'MC'
FROM cassettes c
ORDER BY 3, 2 -- artist, title

Уточнение “звёзд” необходимо здесь, потому что они не являются единственным элементом в списке столбцов.Заметьте, что псевдонимы “c” в первой и третьей выборке не кусают друг друга.Они не имеют контекста объединения, а лишь применяются к отдельным запросам на выборку.

Следующий запрос получает имена и телефонные номера переводчиков и корректоров.Те переводчики, которые также работают корректорами, будут отображены только один раз в результирующем наборе, если номера их телефонов одинаковые в обеих таблицах.Тот же результат может быть получен без ключевого слова DISTINCT.Если вместо ключевого слова DISTINCT, будет указано ключевое слово ALL, эти люди будут отображены дважды.

SELECT name, phone
FROM translators
UNION DISTINCT
SELECT name, telephone
FROM proofreaders

Пример использования UNION в подзапросе:

SELECT name, phone, hourly_rate
FROM clowns
WHERE hourly_rate < ALL
  (SELECT hourly_rate FROM jugglers
   UNION
   SELECT hourly_rate FROM acrobats)
ORDER BY hourly_rate

Использование выражений запроса в скобках для отображения сотрудников с самой высокой и самой низкой зарплатой:

(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

Полный синтаксис SELECT

В предыдущих разделах использовались неполные или упрощенные фрагменты синтаксиса оператора SELECT. Ниже приведен полный синтаксис.

Note

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

Несмотря на то, что здесь описан полный синтаксис, некоторые представления не отображаются (например, <value-expression>) и предполагается, что они понятны читателю, и в некоторых случаях мы используем сокращения, например, используя query-name или column-alias для идентификаторов в синтаксическом представлении.

Приведенный ниже синтаксис не включает PSQL синтаксис SELECT…​ INTO, который по сути представляет собой <cursor-specification> INTO <variable-list>.
Полный синтакси оператора SELECT
<cursor-specification> ::=
  <query-expression> [<updatability-clause>] [<lock-clause>]

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...]

<with-list-element> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body> [<order-by-clause>]
     [<result-offset-clause>] [<fetch-first-clause>])

<query-specification> ::=
  SELECT <limit-clause> [{ ALL | DISTINCT }] <select-list>
    FROM <table-reference> [, <table-reference> ...]
    [WHERE <search-condition>]
    [GROUP BY <value-expression> [, <value-expression> ...]]
    [HAVING <search-condition>]
    [WINDOW <window-definition> [, <window-definition> ...]]
    [PLAN <plan-expression>]

<limit-clause> ::= [FIRST <limit-expression>] [SKIP <limit-expression>]

<limit-expression> ::=
    <integer-literal>
  | <query-parameter>
  | (<value-expression>)

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    table-alias.*
  | <value-expression> [[AS] column-alias]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression> [, <value-expression> ...]

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<derived-table> ::= (<query-expression>)

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table>)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

<window-definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  [existing-window-name]
    [<window-partition-clause>]
    [<order-by-clause>]
    [<window-frame-clause>]

<window-partition-clause> ::=
  PARTITION BY <value-expression> [, <value-expression> ...]

<order-by-clause> ::=
  ORDER BY <sort-specification> [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST

<window-frame-clause> ::= { RANGE | ROWS } <window-frame-extent>

<window-frame-extent> ::=
    <window-frame-start>
  | <window-frame-between>

<window-frame-start> ::=
    UNBOUNDED PRECEDING
  | <value-expression> PRECEDING
  | CURRENT ROW

<window-frame-between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <value-expression> PRECEDING
          | CURRENT ROW | <value-expression> FOLLOWING }
  AND { <value-expression> PRECEDING | CURRENT ROW
      | <value-expression> FOLLOWING | UNBOUNDED FOLLOWING }

<rows-clause> ::= ROWS <value-expression> [TO <value-expression>]

<result-offset-clause> :: =
  OFFSET <offset-fetch-expression> { ROW | ROWS }

<offset-fetch-expression> ::=
    <integer-literal>
  | <query-parameter>

<fetch-first-clause> ::=
  [FETCH { FIRST | NEXT }
   [<offset-fetch-expression>] { ROW | ROWS } ONLY]

<updatability-clause> ::= FOR UPDATE [OF <column-name-list>]

<lock-clause> ::= WITH LOCK [SKIP LOCKED]

INSERT

Назначение

Вставка данных в таблицу.

Доступно в

DSQL, ESQL, PSQL

Синтаксис
INSERT INTO target [(<column_list>)]
  [OVERRIDE {SYSTEM | USER} VALUE]
  {DEFAULT VALUES | <value_source>}
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col-name [, col-name ...]

<value_source> ::= VALUES (<value_list>) | <select_stmt>

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> :: = <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Параметры оператора INSERT
Параметр Описание

target

Имя таблицы или представления, в которую происходит вставка новой записи или записей.

col-name

Имя столбца таблицы или представления.

value_expression

Выражение, значение которого используется для вставки в таблицу или возврата в RETURNING

literal

Литерал.

context-variable

Контекстная переменная.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

return_expression

Выражение, возвращаемое в предложении RETURNING.

collation

Существующее имя сортировки (только для символьных типов).

alias

Псевдоним для выражения, возвращаемого в предложении RETURNING.

varname

Имя PSQL переменной.

Оператор INSERT добавляет строки в таблицу или в одну, или более таблиц представления.Если значения столбцов указаны в разделе VALUES, то будет вставлена одна строка.Значения столбцов также могут быть получены из оператора SELECT, в этом случае может быть вставлено от нуля и более строк.В случае DEFAULT VALUES, значения можно не указывать, и вставлена будет одна строка.

Note

  • Один столбец не может быть указан более одного раза в списке столбцов;

  • При возвращении значений столбцов вставленной записи с помощью INTO в контекстные переменные NEW.columnname в триггерахнеобязательно использоваться префикс двоеточия (“:”).

INSERT …​ VALUES

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

Note

Вводный синтаксис даёт возможность определить набор символов для значений строковых констант (литералов).Вводный синтаксис работает только с литералами строк: он не может быть применён к строковым переменным, параметрам, ссылкам на столбцы или значения, выражениям.
Example 1. Использование INSERT с предложением VALUES
INSERT INTO cars (make, model, byyear)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

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

Ключевое слово DEFAULT

В списке VALUES вместо значения столбца можно использовать ключевое слово DEFAULT.В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

Если ключевое слово DEFAULT указано для столбца, определенного как GENERATED BY DEFAULT AS IDENTITY, то столбец получит следующее значение идентификации, так как будто этот столбец не был указан в запросе вовсе.

Example 1. Использование ключевого слова DEFAULT в операторе INSERT
CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- в столбец BYYEAR попадёт значение 1990
INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus');

-- в столбец id попадёт значение 2, как будто мы не указывали значение для id
INSERT INTO cars (id, byyear, name)
VALUES (DEFAULT, 1996, 'Ford Mondeo');

INSERT …​ SELECT

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

Example 1. Использование оператора в форме INSERT …​ SELECT
INSERT INTO cars (make, model, byyear)
SELECT make, model, byyear
FROM new_cars;

INSERT INTO cars
SELECT *
FROM new_cars;

INSERT INTO Members (number, name)
SELECT number, name
FROM NewMembers
WHERE Accepted = 1
UNION ALL
SELECT number, name
FROM SuspendedMembers
WHERE Vindicated = 1

INSERT INTO numbers(num)
WITH RECURSIVE r(n) AS (
  SELECT 1 FROM rdb$database
  UNION ALL
  SELECT n+1 FROM r where n < 100
)
SELECT n FROM r

Конечно, имена столбцов в таблице источнике необязательно должны быть такими же, как и в таблице приёмнике.

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

INSERT …​ DEFAULT VALUES

Предложение DEFAULT VALUES позволяет вставлять записи без указания значений вообще, ни непосредственно (в предложении VALUES), ни из оператора SELECT.Это возможно, только если для каждого NOT NULL поля и полей, на которые наложены другие ограничения, или имеются допустимые объявленные значения по умолчанию, или эти значения устанавливаются в BEFORE INSERT триггере.

Example 1. Использование DEFAULT VALUES в операторе INSERT
INSERT INTO journal
DEFAULT VALUES
RETURNING entry_id

Директива OVERRIDING

Значения столбцов идентификации (GENERATED BY DEFAULT AS IDENTITY) могут быть переопределены в операторах INSERT, UPDATE OR INSERT, MERGE.Для этого просто достаточно указать значение столбца в списке значений.Однако для столбцов определённых как GENERATED ALWAYS это недопустимо.

Директива OVERRIDING SYSTEM VALUE позволяет заменить сгенерированное системой значение на значение указанное пользователем.Директива OVERRIDING SYSTEM VALUE вызовет ошибку, если в таблице нет столбцов идентификации или если они определены как GENERATED BY DEFAULT AS IDENTITY.

Example 1. Использование директивы OVERRIDING SYSTEM VALUE в операторе INSERT
CREATE TABLE objects (
  id INT GENERATED ALWAYS AS IDENTITY,
  name CHAR(50));

-- будет вставлено значение с кодом 11
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (11, 'Laptop');

Директива OVERRIDE USER VALUE выполняет обратную задачу, т.е.заменяет значение указанное пользователем на значение сгенерированное системой, если столбец идентификации определён как GENERATED BY DEFAULT AS IDENTITY.Директива OVERRIDING USER VALUE вызовет ошибку, если в таблице нет столбцов идентификации или если они определены как GENERATED ALWAYS AS IDENTITY.

Example 2. Использование директивы OVERRIDING USER VALUE в операторе INSERT
CREATE TABLE objects (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name CHAR(50));

-- значение 12 будет проигнорировано
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (12, 'Laptop');
См. также:

Столбцы идентификации (автоинкремент).

RETURNING

Оператор INSERT может включать необязательное предложение RETURNING для возврата значений из вставленной записи.Если предложение указано, то оно может содержать любые столбцы, указанные в операторе, или другие столбцы и выражения.Вместо списка столбцов вы можете указать звёздочку (*) для возврата всех значений столбцов таблицы.Возвращаемые значения содержат все изменения, произведённые в триггерах BEFORE.

Important

  • В DML оператор INSERT …​ SELECT с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).

  • В настоящее время операторы с предложением RETURNING не могут быть применены вместе с предложением FOR для цикла по курсору в PSQL.Это поведение может быть изменено в последующих версиях Firebird.

  • Оператор INSERT …​ VALUES с предложением RETURNING всегда возвращает одну запись. Если запись не была вставлена на самом деле, то все поля в возвращаемой строке будут иметь значения NULL. Это поведение может быть изменено позже. В PSQL, если ни одна запись не вставлена, то ничего не возвращается и все целевые переменные сохраняют свои прежние значения.
Example 1. Использование предложения RETURNING в операторе INSERT
INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (first_name, last_name, iq)
SELECT fname, lname, iq
FROM Friends
ORDER BY iq ROWS 1
RETURNING id, first_name, iq
INTO :id, :fname, :iq;

Вставка столбцов BLOB

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

  1. Клиентское приложение вставляет BLOB посредством Firebird API. В этом случае все зависит от приложения, и не рассматривается в этом руководстве;

  2. Длина строкового литерала не может превышать 65,533 байт (64K - 3).

    Note

    Предел в символах вычисляется во время выполнения. Для мультибайтовых наборов символов он может отличаться.Например, для строки UTF8 (4 байта на символ) ограничение строкового литерала, вероятно, будет около (floor (65533/4)) = 16383 символов.

  3. Если источником данных является столбец BLOB или выражение, возвращающее BLOB. Например, при использовании формы INSERT …​ SELECT иливнутри PSQL кода, когда в качестве параметра подставляется переменная типа BLOB.

UPDATE

Назначение

Обновление данных в таблице.

Доступно в

DSQL, ESQL, PSQL

Синтаксис
UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Параметры оператора UPDATE
Параметр Описание

target

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

alias

Псевдоним таблицы или представления.

col_name

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

upd_value

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

literal

Литерал.

context-variable

Контекстная переменная.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

search-conditions

Условие поиска, ограничивающее набор обновляемых строк.

cursorname

Имя курсора, по которому позиционируется обновляемая запись.

plan_items

Части плана запроса.

sort_items

Столбцы, перечисленные в предложении ORDER BY.

m, n

Целочисленные выражения для ограничения количества обновляемых строк.

return_expression

Выражение, возвращаемое в предложении RETURNING.

collation

Существующее имя сортировки (только для символьных типов).

ret_alias

Псевдоним для выражения, возвращаемого в предложении RETURNING.

varname

Имя PSQL переменной.

Оператор UPDATE изменяет значения столбцов в таблице, или в одной, или нескольких таблицах, лежащих в основе представления.Новые значения столбцов указываются в предложении SET.Затронутые строки могут быть ограничены предложениями WHERE и ROWS.Если нет ни WHERE, ни ROWS, все записи в таблице будут обновлены.

Использование псевдонима

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

Примеры

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

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Неправильное использование:

update Fruit F set Fruit.soort = 'pisang' where ...

SET

Изменяемые столбцы указываются в предложении SET.Столбцы и их значения перечисляются через запятую.Слева имя столбца, и справа значение или выражение.

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

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

Данные в таблице TSET:

A B
---
1 0
2 0

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

update tset set a = 5, b = a
A B
---
5 1
5 2

Обратите внимание, что старые значения (1 и 2) используются для обновления столбца b, даже после того как столбцу a были назначено новое значение (5).

Ключевое слово DEFAULT

В предложении SET вместо значения столбца можно использовать ключевое слово DEFAULT.В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

Example 1. Использование ключевого слова DEFAULT в операторе UPDATE
CREATE TABLE cars (
  ID INTEGER NOT NULL,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

INSERT INTO cars (1, byyear, name)
VALUES (1, 1985, 'Ford Focus');

-- столбцу BYYEAR будет присвоено значение 1990
UPDATE cars
SET BYYEAR = DEFAULT
WHERE ID = 1;

WHERE

Предложение WHERE ограничивает набор обновляемых записей заданным условием, или — в PSQL — текущей строкой именованного курсора, если указано предложение WHERE CURRENT OF.

Note

Предложение WHERE CURRENT OF используется только в PSQL, т.к.в DSQL нет оператора DSQL для создания курсора.

Строковые литералы могут предваряться именем набора символов, для того чтобы Firebird понимал, как интерпретировать данные.

Example 1. Использование предложения WHERE в операторе UPDATE
UPDATE addresses
SET city = 'Saint Petersburg', citycode = 'PET'
WHERE city = 'Leningrad';

UPDATE employees
SET salary = 2.5 * salary
WHERE title = 'CEO';

-- обратите внимание на префикс '_'
UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

UPDATE employee e
SET salary = salary * 1.05
WHERE EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);

PLAN

Предложение PLAN позволяет вручную указать план для оптимизатора.

Example 1. Использование предложения PLAN в операторе UPDATE
UPDATE company c SET c.company_name =
    ( SELECT k.contact_name
      FROM contact k
      WHERE k.id = c.contact_id
      PLAN (K INDEX (CONTACT_ID)))
WHERE c.company_name IS NULL OR c.company_name = ''
PLAN (C NATURAL)

ORDER BY и ROWS

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

Предложение ROWS имеет смысл только вместе с предложением ORDER BY.Однако его можно использовать отдельно.

При одном аргументе m, ROWS ограничивает update первыми m записями.

Особенности:

  • Если m больше количества обрабатываемых записей в целевой таблице, то обновляется весь набор строк;

  • Если m = 0, ни одна запись не обновляется;

  • Если m < 0, выдаётся ошибка.

При двух аргументах m и n, ROWS ограничивает update записей от m до n включительно.Оба аргумента – целочисленные, и начинаются с 1.

Особенности:

  • Если m больше количества записей в целевой таблице, ни одна запись не обновляется;

  • Если n больше количества записей в целевой таблице, то обновляются записи от m до конца набора;

  • Если m < 1 или n < 1, выдаётся ошибка;

  • Если n = m - 1, ни одна запись не обновляется;

  • Если n < m - 1, выдаётся ошибка.

Example 1. Использование предложения ROWS в операторе UPDATE
-- дать надбавку 20ти сотрудникам с наименьшей зарплатой
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

SKIP LOCKED

Назначение

Пропустить заблокированное.

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

Такая функциональность полезна для реализации рабочих очередей, когда один или несколько процессов отправляютданные в таблицу и выдают событие, в то время как рабочие процессы прослушивают эти события и читают/удаляют элементы из таблицы.Используя SKIP LOCKED, несколько рабочих потоков могут получать эксклюзивные рабочие элементы из таблицы без конфликтов.

Note

Если предложение SKIP LOCKED используется совместно с FIRST/SKIP/ROWS/OFFSET/FETCH, то сначала пропускаются заблокированные записи,а затем применяются ограничители FIRST/SKIP/ROWS/OFFSET/FETCH к оставшимся записям.

См. также: SELECT …​ SKIP LOCKED,DELETE FROM …​ SKIP LOCKED.

RETURNING

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

Возвращаемые значения содержат изменения, произведённые в триггерах BEFORE UPDATE, но не в триггерах AFTER UPDATE.Выражения OLD.fieldname и NEW.fieldname могут быть использованы в качестве имён столбцов.Если OLD. или NEW. не указано, возвращаются новые значения столбцов NEW..

Вместо списка столбцов вы можете указать звёздочку (*). В этом случае будут возвращены все значения столбцов таблицы.Звёздочку можно применять со спецификаторами NEW или OLD.

Note

  • В DML оператор UPDATE с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).

  • В настоящее время операторы с предложением RETURNING не могут быть применены вместе с предложением FOR для цикла по курсору в PSQL.Это поведение может быть изменено в последующих версиях Firebird.

  • Если записи не были обновлены оператором, то возвращаемые значения содержат NULL.

INTO

Предложение INTO предназначено для передачи значений в локальные переменные.Оно доступно только в PSQL.Если записи не было обновлены, ничего не возвращается, и переменные, указанные в RETURNING, сохранят свои прежние значения.

Example 1. Использование предложения RETURNING в операторе UPDATE
UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING id, old.last_name, new.last_name;
Example 2. Использование * в предложении RETURNING в операторе UPDATE
UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING old.*;

Обновление столбцов BLOB

Обновление столбцов BLOB всегда полностью меняет их содержимое.Даже идентификатор BLOB (ID), который является ссылкой на данные BLOB и хранится в столбце, меняется.Столбцы типа BLOB могут быть изменены, если:

  1. Клиентское приложение меняет BLOB посредством Firebird API. В этом случае все зависит от приложения, и не рассматривается в этом руководстве;

  2. Длина строкового литерала не может превышать 65,533 байт (64K - 3).

    Note

    Предел в символах вычисляется во время выполнения. Для многобайтных наборов символов он может отличаться.Например, для строки UTF8 (4 байта на символ) ограничение строкового литерала, вероятно, будет около (floor (65533/4)) = 16383 символов.

  3. Если источником данных является столбец типа BLOB или выражение, возвращающее BLOB.

UPDATE OR INSERT

Назначение

Добавление новой или обновление существующей записи в таблице.

Доступно в

DSQL, PSQL

Синтаксис
UPDATE OR INSERT INTO target [(<column_list>)]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name [, col_name ...]

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> ::= <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Параметры оператора UPDATE OR INSERT
Параметр Описание

target

Имя таблицы или представления, запись в которой будет обновлена или произойдет вставка новой записи.

col_name

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

ins_value

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

literal

Литерал.

context-variable

Контекстная переменная.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

return_expression

Выражение, возвращаемое в предложении RETURNING.

alias

Псевдоним для выражения, возвращаемого в предложении RETURNING.

varname

Имя PSQL переменной.

Оператор UPDATE OR INSERT вставляет или обновляет одну, или более существующих записей.Производимое действие зависит от значений столбцов в предложении MATCHING (или, если оно не указано, то от значений столбцов первичного ключа — PK). Если найдены записи, совпадающие с указанными значениями, то они обновляются.Если нет, то вставляется новая запись.

Совпадением считается полное совпадение значений столбцов MATCHING или PK.Совпадение проверяется с использованием IS NOT DISTINCT, поэтому NULL совпадает с NULL.

Note
Ограничения

  • Если у таблицы нет первичного ключа, то указание MATCHING считается обязательным;

  • В списке MATCHING, так же как и в списке столбцов update/insert, каждый столбец может быть упомянут только один раз;

  • Предложение INTO доступно только в PSQL.

Ключевое слово DEFAULT

В списке VALUES вместо значения столбца можно использовать ключевое слово DEFAULT.В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

Note
Ограничение

Столбец для которого вместо значения использовано ключевое слово DEFAULT не может быть использован в предложении MATCHING.
Example 1. Использование ключевого слова DEFAULT в операторе UPDATE OR INSERT
CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- в столбец BYYEAR попадёт значение 1990
UPDATE OR INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus')
MATCHING (name);

RETURNING

Предложение RETURNING может содержать любые столбцы, указанные в операторе, или другие столбцы и выражения.Возвращаемые значения содержат все изменения, произведённые в триггерах BEFORE, но не в триггерах AFTER.Выражения OLD.fieldname и NEW.fieldname могут быть использованы в качестве возвращаемых значений.Для обычных имён столбцов возвращаются новые значения.

Вместо списка столбцов вы можете указать звёздочку (*). В этом случае будут возвращены все значения столбцов таблицы.Звёздочку можно применять со спецификаторами NEW или OLD.

Note

  • В DSQL оператор с RETURNING всегда возвращает только одну строку.
Example 1. Использование предложения RETURNING в операторе UPDATE OR INSERT
UPDATE OR INSERT INTO Cows (Name, Number, Location)
VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
MATCHING (Number)
RETURNING rec_id
INTO :id;

DELETE

Назначение

Удаление данных из таблицы.

Доступно в

DSQL, ESQL, PSQL

Синтаксис:
DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Параметры оператора DELETE
Параметр Описание

target

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

alias

Псевдоним таблицы или представления.

col-name

Имя столбца таблицы или представления.

search-conditions

Условие поиска, ограничивающее набор удаляемых записей.

cursorname

Имя курсора, по которому позиционируется удаляемая запись.

plan_items

Предложение плана.

sort_items

Предложение сортировки.

m, n

Целочисленные выражения для ограничения количества удаляемых записей.

return_expression

Выражение, возвращаемое в предложении RETURNING.

literal

Литерал.

context-variable

Контекстная переменная.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

collation

Существующее имя сортировки (только для символьных типов).

ret_alias

Псевдоним для выражения, возвращаемого в предложении RETURNING.

varname

Имя PSQL переменной.

Оператор DELETE удаляет строки из таблицы или из одной и более таблиц представления.

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

WHERE

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

Удаление с помощью WHERE CURRENT OF называется позиционированным удалением (positioned delete), потому что удаляется запись в текущей позиции.Удаление при помощи “WHERE условие” называется поисковым удалением (searched delete), поскольку Firebird ищет записи, соответствующие условию.

Note

В чистом DSQL выражение WHERE CURRENT OF не имеет смысла, т.к.в DSQL нет оператора для создания курсора.
Example 1. Использование предложения WHERE в операторе DELETE
DELETE FROM People
WHERE first_name <> 'Boris' AND last_name <> 'Johnson';
DELETE FROM employee e
WHERE NOT EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);
DELETE FROM Cities
WHERE CURRENT OF Cur_Cities; -- только в PSQL

PLAN

Предложение PLAN позволяет вручную указать план для оптимизатора.

Example 1. Использование предложения PLAN в операторе DELETE
DELETE FROM Submissions
WHERE date_entered < '1-Jan-2002'
PLAN (Submissions INDEX ix_subm_date)

ORDER BY и ROWS

Предложение ORDER BY упорядочивает набор перед его удалением.Что может быть важно в некоторых случаях.

Предложение ROWS позволяет ограничить количество удаляемых строк.Имеет смысл только в комбинации с предложением ORDER BY, но допустимо и без него.

В качестве m и n могут выступать любые целочисленные выражения.

При одном аргументе m, удаляются первые m записей.Порядок записей без ORDER BY не определён (случаен).

Замечания:

  • Если m больше общего числа записей в наборе, то весь набор удаляется;

  • Если m = 0, то удаление не происходит;

  • Если m < 0, то выдаётся сообщение об ошибке.

Если указаны аргументы m и n, удаление ограничено количеством записей от m до n, включительно.Нумерация записей начинается с 1.

Замечания по использованию двух аргументов:

  • Если m больше общего числа строк в наборе, ни одна строка не удаляется;

  • Если m > 0 и ⇐ числа строк в наборе, а n вне этих значений, то удаляются строки от m до конца набора;

  • Если m < 1 или n < 1, выдаётся сообщение об ошибке;

  • Если n = m – 1, ни одна запись не удаляется;

  • Если n < m – 1, выдаётся сообщение об ошибке.

Example 1. Использование ORDER BY и ROWS в операторе DELETE

Удаление самой старой покупки

DELETE FROM Purchases ORDER BY ByDate ROWS 1

Удаление заказов для 10 клиентов с самыми большими номерами

DELETE FROM Sales ORDER BY custno DESC ROWS 1 TO 10

Удаляет все записи из sales, поскольку не указано ROWS

DELETE FROM Sales ORDER BY custno DESC

Удаляет одну запись "с конца", т.е.от Z…​

DELETE FROM popgroups ORDER BY name DESC ROWS 1

Удаляет пять самых старых групп

DELETE FROM popgroups ORDER BY formed ROWS 5

Сортировка (ORDER BY) не указана, поэтому будут удалены 8 обнаруженных записей, начиная с пятой.

DELETE FROM popgroups ROWS 5 TO 12

SKIP LOCKED

Назначение

Пропустить заблокированное.

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

Такая функциональность полезна для реализации рабочих очередей, когда один или несколько процессов отправляютданные в таблицу и выдают событие, в то время как рабочие процессы прослушивают эти события и читают/удаляют элементы из таблицы.Используя SKIP LOCKED, несколько рабочих потоков могут получать эксклюзивные рабочие элементы из таблицы без конфликтов.

Note

Если предложение SKIP LOCKED используется совместно с FIRST/SKIP/ROWS/OFFSET/FETCH, то сначала пропускаются заблокированные записи,а затем применяются ограничители FIRST/SKIP/ROWS/OFFSET/FETCH к оставшимся записям.

См. также: SELECT …​ SKIP LOCKED,UPDATE …​ SKIP LOCKED.

Example 1. Использование предложения SKIP LOCKED для организации очереди

Подготовка метаданных.

create table emails_queue (
    subject varchar(60) not null,
    text blob sub_type text not null
);

set term !;

create trigger emails_queue_ins after insert on emails_queue
as
begin
    post_event('EMAILS_QUEUE');
end!

set term ;!

Отправка данных приложением или подпрограммой

insert into emails_queue (subject, text)
  values ('E-mail subject', 'E-mail text...');
commit;

Клиентское приложение может прослушивать событие EMAILS_QUEUE, чтобы отправлять электронные письма, используя этот запрос:

delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

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

RETURNING

Оператор DELETE может содержать конструкцию RETURNING для возвращения значений из удаляемых записей.В RETURNING могут быть указаны любые столбцы и выражения.Вместо списка столбцов может быть указана звёздочка (*), в этом случае будут возвращены все столбцы удалённой записи.

Note

  • В DML оператор DELETE с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).В настоящее время операторы с предложением RETURNING не могут быть применены вместе с предложением FOR для цикла по курсору в PSQL.Это поведение может быть изменено в последующих версиях Firebird.Если записи не были удалены, то возвращаемые столбцы содержат NULL;

  • В PSQL, если строка не была удалена, ничего не возвращается, и целевые переменные сохраняют свои значения;

  • Предложение INTO доступно только в PSQL.
Example 1. Использование предложения RETURNING в операторе DELETE
DELETE FROM Scholars
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING last_name, fullname, id

DELETE FROM Dumbbells
ORDER BY iq DESC
ROWS 1
RETURNING last_name, iq
INTO :lname, :iq;

DELETE FRMO TempSales ts
WHERE ts.id = tempid
RETURNING ts.qty
INTO new.qty;

MERGE

Назначение

Слияние записей источника в целевую таблицу (или обновляемое представление).

Доступно в

DSQL, PSQL

Синтаксис
MERGE
  INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join condition>
  <merge when> [<merge when> ...]
  [<plan clause>]
  [<order by clause>]
  [<returning clause>]

<source> ::= tablename | (<select_stmt>)

<merge when> ::=
    <merge when matched>
  | <merge when not matched by target>
  | <merge when not matched by source>

<merge when matched> ::=
  WHEN MATCHED [ AND <condition> ]
    THEN { UPDATE SET <assignment_list> | DELETE }

<merge when not matched by target> ::=
  WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ]
    THEN INSERT [ <left paren> <column_list> <right paren> ]
    VALUES <left paren> <value_list> <right paren>

<merge when not matched by source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
    { UPDATE SET <assignment list> | DELETE }

<assignment_list> ::=
  col_name = <m_value> [, colname = <m_value> ...]

<column_list> ::= col_name [, col_name ...]

<value_list> ::= <m_value> [, <m_value> ...]

<m_value> ::= <value_expression> | DEFAULT

<returning clause> ::= RETURNING <returning_list> [INTO <variable_list>]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Параметры оператора MERGE
Параметр Описание

target

Целевая таблица или обновляемое представление.

source

Источник данных.Может быть таблицей, представлением, хранимой процедурой или производной таблицей.

target_alias

Псевдоним целевой таблицы или представления.

source_alias

Псевдоним источника.

join condition

Условие соединения целевой таблицы и источника.

condition

Дополнительные условия проверки в предложениях WHEN MATCHED или WHEN NOT MATCHED.

col_name

Столбец целевой таблицы или представления.

m_value

Значение, присваиваемое столбцу целевой таблицы.Выражение, которое может содержать литералы, PSQL переменные, столбцы из источника.

return_expression

Выражение, возвращаемое в предложении RETURNING.

ret_alias

Псевдоним для выражения, возвращаемого в предложении RETURNING.

varname

Имя PSQL переменной.

Оператор MERGE производит слияние записей источника и целевой таблицы (или обновляемым представлением).В процессе выполнения оператора MERGE читаются записи источника и выполняются INSERT, UPDATE или DELETE для целевойтаблицы в зависимости от условий.

Источником может быть таблица, представление, хранимой процедурой или производной таблицей. При выполнении оператора MERGE производится соединение между источником (USING) и целевой таблицей. Тип соединения зависит от присутствия предложений WHEN NOT MATCHED:

  • <merge when not matched by target> и <merge when not matched by source> — FULL JOIN

  • <merge when not matched by source> — RIGHT JOIN

  • <merge when not matched by target> — LEFT JOIN

  • только <merge when matched> — INNER JOIN

Действие над целевой таблицей, а также условие при котором оно выполняется, описывается в предложении WHEN. Допускается несколько предложений WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] и WHEN NOT MATCHED BY SOURCE.

Если условие в предложении WHEN не выполняется, то Firebird пропускает его и переходим к следующему предложению. Так будет происходить до тех пор, пока условие для одного из предложений WHEN не будет выполнено. В этом случае выполняется действие, связанное с предложением WHEN, и осуществляется переход на следующую запись результата соединения между источником (USING) и целевой таблицей. Для каждой записи результата соединения выполняется только одно действие.

Note

WHEN NOT MATCHED [BY TARGET] оценивается с точки зрения источника, т.е.таблицы или набора данных указанного в предложения USING.Так сделано потому, что если запись источника не имеет совпадения с записью цели, то выполняется INSERT.Разумеется, если запись цели не соответствует записи в источнике, то никакие действия не производятся.

На данный момент переменная ROW_COUNT возвращает значение 1, даже если было модифицировано или вставлено более 1 записи.См. CORE-4400.

WHEN MATCHED

Указывает, что все строки target, которые соответствуют строкам, возвращенным выражением <source> ON <join condition>, и удовлетворяют дополнительным условиям поиска, обновляются (предложение UPDATE) или удаляются (предложение DELETE) в соответствии с предложением <merge when matched>.

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

Инструкция MERGE не может обновить одну строку более одного раза или одновременно обновить и удалить одну и ту же строку.

Note

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

До Firebird 4.0 UPDATE выполнится для всех совпадающих записей источника, и каждое последующее обновление перезапишет предыдущее.Это поведение не соответствует SQL стандарту.

В списке SET предложения UPDATE вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы. Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

WHEN NOT MATCHED [BY TARGET]

Указывает, что все строки target, которые не соответствуют строкам, возвращенным выражением <source> ON <join condition>, и удовлетворяют дополнительным условиям поиска, вставляются в целевую таблицу (предложение INSERT) в соответствии с предложением <merge when not matched by target>.

Допускается указывать несколько предложений WHEN NOT MATCHED [BY TARGET]. Если указано более одного предложенияWHEN NOT MATCHED [BY TARGET], то все их следует дополнять дополнительными условиями поиска, за исключением последнего.

В списке VALUES предложения INSERT вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

WHEN NOT MATCHED BY SOURCE

Указывает, что все строки target, которые не соответствуют строкам, возвращенным выражением <source> ON <join condition>, и удовлетворяют дополнительным условиям поиска, (предложение UPDATE) или удаляются (предложение DELETE) в соответствии с предложением <merge when not matched by source>.

Предложение WHEN NOT MATCHED BY SOURCE доступно начиная с Firebird 5.0.

Допускается указывать несколько предложений WHEN NOT MATCHED BY SOURCE. Если указано более одного предложенияWHEN NOT MATCHED BY SOURCE, то все их следует дополнять дополнительными условиями поиска, за исключением последнего.

В списке SET предложения UPDATE вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы. Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

Note

Обратите внимание! В списке SET предложения UPDATE не имеет смысла использовать выражения со ссылкой на <source>,поскольку ни одна запись из <source> не соответствует записям target.

Примеры

Example 1. Простые операторы MERGE
MERGE INTO books b
USING purchases p
ON p.title = b.title AND p.booktype = 'bk'
WHEN MATCHED THEN
  UPDATE SET b.descr = b.descr || '; ' || p.descr
WHEN NOT MATCHED THEN
  INSERT (title, descr, bought)
  VALUES (p.title, p.descr, p.bought);

-- с использованием производной таблицы
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
  UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (cd.id, cd.name);

-- совместно с рекурсивным CTE
MERGE INTO numbers
USING (
  WITH RECURSIVE r(n) AS (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 200
  )
  SELECT n FROM r
) t
ON numbers.num = t.n
WHEN NOT MATCHED THEN
  INSERT(num) VALUES(t.n);

-- с использованием предложения DELETE
MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE
Example 2. Использование оператора MERGE с дополнительными условиями в предложениях WHEN [NOT] MATCHED

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

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
Example 3. Использование оператора MERGE с WHEN NOT MATCHED BY SOURCE

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

MERGE
  INTO customers c
  USING new_customers nc
  ON (c.id = nc.id)
  WHEN MATCHED THEN
    UPDATE SET
	  name = cd.name
  WHEN NOT MATCHED BY SOURCE THEN
	DELETE
См. также:

SELECT, INSERT, UPDATE, DELETE.

RETURNING

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

Возвращаемые значения содержат изменения, произведённые в триггерах BEFORE.

Имена столбцов могут быть уточнены с помощью префиксов NEW и OLD для определения, какое именно значение столбца вы хотите получить до модификации или после.

Вместо списка столбцов может быть указана звёздочка (*), в этом случае будут возвращены все столбцы целевой таблицы.Префиксы NEW и OLD могут быть использованы совместно со звёздочкой.

Для предложений WHEN MATCHED UPDATE и MERGE WHEN NOT MATCHED неуточненные имена столбцов или уточнённые именамитаблиц или их псевдонимами понимаются как столбцы с префиксом NEW, для предложений MERGE WHEN MATCHED DELETE — с префиксом OLD.

Note

  • В DML оператор MERGE с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).В настоящее время операторы с предложением RETURNING не могут быть применены вместе с предложением FOR для цикла по курсору в PSQL.Это поведение может быть изменено в последующих версиях Firebird.

  • Предложение INTO доступно только в PSQL.
Example 1. Использование оператора MERGE с предложением RETURNING

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

MERGE INTO PRODUCT_IVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
    AND SL.ID_PRODUCT = :ID_PRODUCT
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO :OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

EXECUTE PROCEDURE

Назначение

Выполнение хранимой процедуры.

Доступно в

DSQL, ESQL, PSQL

Синтаксис
EXECUTE PROCEDURE procname
   [{ <inparam-list> | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list> ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname
Table 1. Параметры оператора EXECUTE PROCEDURE
Параметр Описание

procname

Имя хранимой процедуры.

inparam

Выражение совместимое по типу с входным параметром хранимой процедуры.

varname

PSQL переменная, в которую возвращается значение выходного параметра процедуры.

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

"Выполняемые" хранимые процедуры

Оператор EXECUTE PROCEDURE является наиболее часто используемым стилем вызова хранимой процедуры, которая написанадля модификации некоторых данных. Их код не содержит оператора SUSPEND.Такие хранимые процедуры могут возвратить набор данных, состоящий не более чем из одной строки.Этот набор может быть передан в переменные другой (вызывающей) процедуры с помощью предложения RETURNING_VALUES.Клиентские интерфейсы, как правило, имеют обертку API, которые могут извлекать выходные значения в однострочный буфер при вызове процедуры через EXECUTE PROCEDURE в DSQL.

При вызове с помощью EXECUTE PROCEDURE процедур другого типа (селективных процедур) будет возвращена только перваязапись из результирующего набора, несмотря на то, что эта процедура скорее всего должна возвращать многострочный результат."Селективные" хранимые процедуры должны вызываться с помощью оператора SELECT, в этом случае они ведут себя как виртуальные таблицы.

Note

  • В PSQL И DSQL входными параметрами могут быть любые совместимые по типу выражения;

  • Несмотря на то, что скобки для отделения списка передаваемых параметров необязательны после имени хранимой процедуры, желательно их использовать;

  • Предложение RETURNING_VALUES доступно только в PSQL.
Example 1. Использование оператора EXECUTE PROCEDURE в PSQL
EXECUTE PROCEDURE MakeFullName(:First_Name, :Middle_Name, :Last_Name)
RETURNING_VALUES :FullName;

В этом операторе использование двоеточия (“:”) для входных и выходных параметров необязательно.

Разрешено использовать выражения в качестве параметров.

EXECUTE PROCEDURE MakeFullName
   ('Mr./Mrs. ' || First_Name, Middle_Name, upper(Last_Name))
RETURNING_VALUES FullName;
Example 2. Вызов оператора EXECUTE PROCEDURE в isql
EXECUTE PROCEDURE MakeFullName
  'J', 'Edgar', 'Hoover';

EXECUTE BLOCK

Назначение

Выполнение анонимного PSQL блока.

Доступно в

DSQL

Синтаксис
EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-routine-body>

<inparams> ::= <param_decl> = ? [, <inparams> ]

<outparams> ::= <param_decl> [, <outparams>]

<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]

<type> ::=
    <non_array_datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<non_array_datatype> ::=
    <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::=  См. Синтаксис скалярных типов данных

<blob_datatype> ::= См. Синтаксис типа данных BLOB

<psql-routine-body> ::=
  См. Синтаксис тела модуля
Table 1. Параметры оператора EXECUTE BLOCK
Параметр Описание

param_decl

Описание входного или выходного параметра.

paramname

Имя входного или выходного параметра процедуры.Может содержать до 63 символов.Имя параметра должно быть уникальным среди входных и выходных параметров процедуры, а также её локальных переменных.

non_array_datatype

Тип данных SQL за исключение массивов.

collation

Порядок сортировки.

domain

Домен.

rel

Имя таблицы или представления.

col

Имя столбца таблицы или представления.

Выполняет блок PSQL кода, так как будто это хранимая процедура, возможно с входными и выходными параметрами и локальными переменными.Это позволяет пользователю выполнять “на лету” PSQL в контексте DSQL.

Примеры:

Этот пример вводит цифры от 0 до 127 и соответствующие им ASCII символов в таблицу ASCIITABLE:

EXECUTE BLOCK
AS
  DECLARE i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

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

EXECUTE BLOCK (
  x DOUBLE PRECISION = ?,
  y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = sqrt(x*y);
  SUSPEND;
END

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

Наш последний пример принимает два целочисленных значений, smallest и largest.Для всех чисел в диапазоне smallest..largest, блок выводит само число, его квадрат, куб и четвертую степень.

EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (
  number INT,
  square BIGINT,
  cube BIGINT,
  fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

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

См. также:

Операторы языка PSQL.

Входные и выходные параметры

Выполнение блока без входных параметров должно быть возможным с любым клиентом Firebird, который позволяет пользователю вводить свои собственные DSQL операторы.Если есть входные параметры, все становится сложнее: эти параметры должны получить свои значения после подготовки оператора, но перед его выполнением.Это требует специальных возможностей, которыми располагает не каждое клиентское приложение (Например, isql такой возможности не предлагает).

Сервер принимает только вопросительные знаки ("?") в качестве заполнителей для входных значений, а не ":а", ":MyParam" и т.д., или литеральные значения.Клиентское программное обеспечение может поддерживать форму ":ххх", в этом случае будет произведена предварительная обработка запроса перед отправкой его на сервер.

Если блок имеет выходные параметры, вы должны использовать SUSPEND, иначе ничего не будет возвращено.

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

Для получения дополнительной информации о параметрах и объявлениях переменных, [TYPE OF] domain, TYPE OF COLUMN и т.д.обратитесь к главе DECLARE VARIABLE.

Терминатор оператора

Некоторые редакторы SQL-операторов — в частности утилита isql, которая идёт в комплекте с Firebird, ивозможно некоторые сторонние редакторы — используют внутреннее соглашение, которое требует, чтобы все операторы были завершены с точкой с запятой.

Это создает конфликт с синтаксисом PSQL при кодировании в этих средах.Если вы не знакомы с этой проблемой и ее решением, пожалуйста, изучать детали в главе PSQL в разделе,озаглавленном Изменение терминатора в isql.