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

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

Выборка из общих табличных выражений (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	Список псевдонимов (алиасов) столбцов производной таблицы или список столбцов по которым происходит эквисоединение.

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

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

Соединения JOIN

Соединения `JOIN`