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

Выборка из производной таблицы (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;

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

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

Соединения 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

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