Диалект T-SQL давно известен мощным синтаксисам CROSS APPLY и OUTER APPLY JOIN. Стандарт SQL: 1999 представил почти эквивалентные «производные от боков» таблицы, которые в конечном итоге поддерживаются PostgreSQL 9.3 или Oracle 12c , который принял как синтаксис LATERAL стандарта SQL, так и синтаксис CROSS APPLY и OUTER APPLY специфичный для поставщика T-SQL. ,
Но о чем мы вообще говорим?
Функции SQL обладают уникальной чертой, которой обладают немногие другие языки. Они не понятны тем, кто их не знает, поскольку каждая языковая функция вводит новый синтаксис с новыми ключевыми словами. В этом случае: APPLY и LATERAL . Но это действительно не так сложно понять. Все, что вы делаете с CROSS APPLY — это CROSS JOIN между двумя таблицами, где правая часть выражения объединения может ссылаться на столбцы с левой стороны выражения соединения. Рассмотрим следующий пример Мартина Смита о переполнении стека :
Повторное использование псевдонимов столбцов
|
01
02
03
04
05
06
07
08
09
10
|
SELECT number, doubled_number, doubled_number_plus_oneFROM master..spt_valuesCROSS APPLY ( SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)CROSS APPLY ( SELECT doubled_number + 1) CA2(doubled_number_plus_one) |
В этом примере мы выбираем числа из системной таблицы и применяем скалярный подвыбор, умножая каждое число на два. Затем ко всему продукту таблицы мы перекрестно применяем еще один скалярный подвыбор, добавляя его к последнему числу.
Этот конкретный пример также может быть реализован с использованием подзапросов в предложении SELECT . Но, как вы можете видеть в приведенном выше примере, doubled_number_plus_one может быть вычислен из предварительно рассчитанного столбца за один раз. Это не было бы так просто с подзапросами.
Применение табличных функций к каждой записи
|
1
2
3
|
SELECT *FROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) |
Этот пример может быть еще более полезен, когда вы хотите присоединить табличную функцию к каждой записи другой таблицы.
ЛАТЕРАЛЬНЫЕ производные таблицы PostgreSQL
В PostgreSQL это можно сделать каким-то волшебным образом, указав табличные функции в предложении SELECT :
|
1
2
|
SELECT x, GENERATE_SERIES(0, x)FROM (VALUES(0), (1), (2)) t(x) |
Вышеуказанные урожаи
|
1
2
3
4
5
6
7
8
|
| X | GENERATE_SERIES ||---|-----------------|| 0 | 0 || 1 | 0 || 1 | 1 || 2 | 0 || 2 | 1 || 2 | 2 | |
В качестве альтернативы, начиная с PostgreSQL 9.3, вы можете использовать явную боковую производную таблицу как таковую:
|
1
2
3
|
SELECT x, yFROM (VALUES(0), (1), (2)) t(x),LATERAL GENERATE_SERIES(0, t.x) u(y) |
Уступая снова
|
1
2
3
4
5
6
7
8
|
| X | Y ||---|---|| 0 | 0 || 1 | 0 || 1 | 1 || 2 | 0 || 2 | 1 || 2 | 2 | |
CROSS APPLY и OUTTER APPLY в jOOQ 3.3
Вышеуказанные пункты также будут поддерживаться в следующих выпусках jOOQ 3.3, где вы можете написать запросы, подобные этому:
|
1
2
3
4
5
6
7
8
|
DSL.using(configuration) .select() .from(AUTHOR) .crossApply( select(count().as("c")) .from(BOOK) .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))) .fetch(); |
Или боковые присоединения:
|
1
2
3
4
5
6
7
8
9
|
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!