Статьи

Добавьте LATERAL Joins или CROSS APPLY в свою цепочку инструментов SQL

Апрель 28, 2018

Диалект T-SQL давно известен мощным синтаксисом CROSS APPLYи OUTER APPLYсинтаксисом JOIN. В стандарте SQL: 1999 были введены почти эквивалентные «боковые производные таблицы», которые в конечном итоге поддерживаются PostgreSQL 9.3 или Oracle 12c , которые приняли как стандартный LATERALсинтаксис SQL, так CROSS APPLYи специфичные для поставщика T-SQL и OUTER APPLYсинтаксисы.

Но о чем мы вообще говорим?

Функции SQL обладают уникальной чертой, которой обладают немногие другие языки. Они не понятны тем, кто их не знает, поскольку каждая языковая функция вводит новый синтаксис с новыми ключевыми словами. В этом случае: APPLYи LATERAL. Но это действительно не так сложно понять. Все, что вы делаете с a CROSS APPLY, это CROSS JOINмежду двумя таблицами, где правая часть выражения объединения может ссылаться на столбцы с левой стороны выражения соединения. Рассмотрим следующий пример Мартина Смита о переполнении стека :

Повторное использование псевдонимов столбцов

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (
  SELECT 2 * CAST(number AS BIGINT)
) CA1(doubled_number)
CROSS APPLY (
  SELECT doubled_number + 1
) CA2(doubled_number_plus_one)

Смотрите SQLFiddle из приведенного выше примера

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

Этот конкретный пример также может быть реализован с использованием подзапросов в SELECTпредложении. Но, как вы можете видеть в приведенном выше примере, doubled_number_plus_oneможно рассчитать из ранее рассчитанного столбца за один раз. Это не было бы так просто с подзапросами.

Применение табличных функций к каждой записи

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

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

ЛАТЕРАЛЬНЫЕ производные таблицы PostgreSQL

В PostgreSQL это можно сделать каким-то волшебным образом, поместив табличные функции в SELECTпредложении:

SELECT x, GENERATE_SERIES(0, x)
FROM (VALUES(0), (1), (2)) t(x)

Смотрите SQLFiddle из приведенного выше примера

Вышеуказанные урожаи

| X | GENERATE_SERIES |
|---|-----------------|
| 0 |               0 |
| 1 |               0 |
| 1 |               1 |
| 2 |               0 |
| 2 |               1 |
| 2 |               2 |

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

SELECT x, y
FROM (VALUES(0), (1), (2)) t(x),
LATERAL GENERATE_SERIES(0, t.x) u(y)

Смотрите SQLFiddle из приведенного выше примера

Уступая снова

| X | Y |
|---|---|
| 0 | 0 |
| 1 | 0 |
| 1 | 1 |
| 2 | 0 |
| 2 | 1 |
| 2 | 2 |

CROSS APPLY и OUTTER APPLY в jOOQ 3.3

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

DSL.using(configuration)
   .select()
   .from(AUTHOR)
   .crossApply(
        select(count().as("c"))
       .from(BOOK)
       .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
   .fetch();

Или боковые присоединения:

DSL.using(configuration)
   .select()
   .from(
        values(row(0), row(1), row(2))
            .as("t", "x"),
        lateral(generateSeries(0,
                fieldByName("t", "x"))
            .as("u", "y")))
   .fetch();

Неважно, используете ли вы jOOQ или собственный SQL, таблицы с боковыми производными или CROSS APPLYобязательно должны быть частью вашей замечательной цепочки инструментов SQL!

48
Share: