CROSS APPLY / OUTER APPLY

Новое в стандарте и реализациях языка SQL

Оператор CROSS APPLY появился в SQL Server 2005. Он позволяет выполнить соединение двух табличных выражений. При этом каждая строка из левой таблицы сочетается с каждой строкой из правой.

Давайте попробуем разобраться в том, какие преимущества дает нам использование этого нестандартного оператора.

Первый пример.

select * from
Product
cross apply
Laptop;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Мы получили просто декартово произведение таблиц Product и Laptop. Аналогичный результат мы можем получить с помощью следующих стандартных запросов:

select * from
Product
cross join
Laptop;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

или

select * from
Product, Laptop;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

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

Эту задачу мы можем решить с помощью обычного соединения:

select P.maker, L.*  from
Product P join Laptop L on P.model= L.model;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

С помощью CROSS APPLY решение этой же задачи можно написать так:

select P.maker, L.*  from
Product P
CROSS APPLY
(select * from Laptop L where P.model= L.model) L;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

“И что тут нового”? - спросите вы. Запрос стал даже более громоздким. Пока да, можно согласиться. Но уже здесь можно заметить весьма важную вещь, которая отличает CROSS APPLY от других видов соединений. А именно, мы используем коррелирующий подзапрос в предложении FROM, передавая в него значения из левого табличного выражения. В данном примере это значения из столбца P.model. Т.е. для каждой строки из левой таблицы правая таблица будет своя.

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

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

Эту задачу мы можем решить с помощью коррелирующего подзапроса в предложении SELECT:

select *, (select MAX(price) from Laptop L2
join  Product P1 on L2.model=P1.model
where maker = (select maker from Product P2 where P2.model= L1.model)) max_price
 from laptop L1;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Пока решение, использующее CROSS APPLY, будет мало чем отличаться от вышеприведенного:

select *
 from laptop L1
 cross apply
 (select MAX(price) max_price from Laptop L2
join  Product P1 on L2.model=P1.model
where maker = (select maker from Product P2 where P2.model= L1.model)) X;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

select *, (select MAX(price) from Laptop L2
join  Product P1 on L2.model=P1.model
where maker = (select maker from Product P2 where P2.model= L1.model)) max_price,
(select MIN(price) from Laptop L2
join  Product P1 on L2.model=P1.model
where maker = (select maker from Product P2 where P2.model= L1.model)) min_price
from Laptop L1;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

и т.д.

А при использовании CROSS APPLY мы просто добавим в подзапрос требуемую агрегатную функцию:

select *
 from laptop L1
 cross apply
 (select MAX(price) max_price, MIN(price) min_price  from Laptop L2
join  Product P1 on L2.model=P1.model
where maker = (select maker from Product P2 where P2.model= L1.model)) X;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Рассмотрим еще один пример.

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

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

select * from laptop L1
CROSS APPLY
(select top 1 * from Laptop L2
where L1.model < L2.model or (L1.model = L2.model and L1.code < L2.code)
order by model, code) X
order by L1.model;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Попробуйте решить эту задачу традиционными средствами, чтобы сравнить трудозатраты.

Оператор OUTER APPLY

Как показывают результаты предыдущего запроса, мы “потеряли” последнюю (шестую) строку из таблицы Laptop, поскольку ее не с чем соединять. Другими словами, CROSS APPLY ведет себя как внутренне соединение. Аналогом же внешнего (левого) соединения является оператор OUTER APPLY. Он отличается от CROSS APPLY только тем, что выводит все строки из левой таблицы, заменяя отсутствующие значения из правой таблицы NULL-значениями.

Замена CROSS APPLY на OUTER APPLY в предыдущем запросе иллюстрирует сказанное.

select * from laptop L1
OUTER APPLY
(select top 1 *
from Laptop L2
where L1.model < L2.model or (L1.model = L2.model and L1.code < L2.code)
order by model, code) X
order by L1.model;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Еще одной популярной задачей является вывод по N строк из каждой группы. Примером может служить вывод 5 наиболее популярных товаров в каждой категории. Рассмотрим следующую задачу.

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

Дополним решения, предложенные на сайте sql-ex.ru, решением, использующим CROSS APPLY. Идея заключается в соединении уникальных типов (первый запрос) с запросом, выводящих по 3 модели модели каждого типа из первого запроса в соответствии с требуемой сортировкой.

select X.* from
(select distinct type from product) Pr1
cross apply
(select top 3 * from product Pr2 where  Pr1.type=Pr2.type order by pr2.model) x;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

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

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

code, название характеристики (speed, ram, hd или screen), значение характеристики.

Метод решения состоит в использовании конструктора таблицы, куда с помощью CROSS APPLY будут передаваться значения столбцов. Давайте разберем этот метод подробно.

Конструктор таблицы может использоваться не только в операторе INSERT, но и для задания таблицы в предложении FROM, например,

select name, value
from (
values('speed', 1)
,('ram', 1)
,('hd', 1)
,('screen', 1)
) Spec(name, value);

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Эта таблица у нас называется Spec и содержит два столбца - name (символьные строки) и value (числа).

Давайте теперь включим эту таблицу в оператор CROSS APPLY, который будет соединять каждую строку из таблицы Laptop с четырьмя строками из сгенерированной таблицы:

select code, name, value
from Laptop
CROSS APPLY (
values('speed', 1)
,('ram', 1)
,('hd', 1)
,('screen', 1)
) Spec(name, value)
where code < 4 -- для уменьшения размера выборки
;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Собственно, нам осталось воспользоваться основным свойством оператора CROSS APPLY - коррелированностью табличного выражения - и заменить единички в столбце value на имена столбцов из соединяемой таблицы:

select code, name, value from Laptop
cross apply
(values('speed', speed)
,('ram', ram)
,('hd', hd)
,('screen', screen)
) spec(name, value)
where code < 4 -- для уменьшения размера выборки
order by code, name, value;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Рекомендуемые упражнения: 97, 117, 132, 145

PostgreSQL обладает аналогичной функциональностью. Синтаксические отличия незначительны и состоят в замене CROSS APPLY на CROSS JOIN LATERAL. Сравните три примера, которые рассматривались на предыдущих страницах этой главы.

Пример 1

SQL Server

SELECT *
     FROM laptop L1
     CROSS APPLY
     (SELECT MAX(price) max_price, MIN(price) min_price  FROM Laptop L2
    JOIN  Product P1 ON L2.model=P1.model
    WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) X;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

PostgreSQL

SELECT *
     FROM laptop L1
     CROSS JOIN LATERAL
     (SELECT MAX(price) max_price, MIN(price) min_price  FROM Laptop L2
    JOIN  Product P1 ON L2.model=P1.model
    WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) X;

Пример 2

SQL Server

SELECT code, name, value FROM Laptop
    CROSS APPLY
    (VALUES('speed', speed)
    ,('ram', ram)
    ,('hd', hd)
    ,('screen', screen)
    ) spec(name, value)
    WHERE code < 4
    ORDER BY code, name, value;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

PostgreSQL

SELECT code, name, value FROM Laptop
    CROSS JOIN LATERAL
    (VALUES('speed', speed)
    ,('ram', ram)
    ,('hd', hd)
    ,('screen', screen)
    ) spec(name, value)
    WHERE code < 4
    ORDER BY code, name, value;

Пример 3

SQL Server

SELECT * FROM laptop L1
    CROSS APPLY
    (SELECT TOP 1 * FROM Laptop L2
    WHERE L1.model < L2.model OR (L1.model = L2.model AND L1.code < L2.code)
    ORDER BY model, code) X
    ORDER BY L1.model;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

PostgreSQL

Дополнительное отличие в этом примере связано не с реализацией CROSS APPLY, а с тем, что для ограничения выборки PostgreSQL вместо конструкции TOP(n) использеут LIMIT n в предложении ORDER BY.

SELECT * FROM laptop L1
    CROSS JOIN LATERAL
    (SELECT  * FROM Laptop L2
    WHERE L1.model < L2.model OR (L1.model = L2.model AND L1.code < L2.code)
    ORDER BY model, code LIMIT 1) X
    ORDER BY L1.model;

OUTER APPLY

Для данного “внешнего” соединения в PostgreSQL используется LEFT JOIN LATERAL. Сравните запросы в примере 4.

Пример 4

SQL Server

SELECT * FROM laptop L1
    OUTER APPLY
    (SELECT TOP 1 *
    FROM Laptop L2
    WHERE L1.model < L2.model OR (L1.model = L2.model AND L1.code < L2.code)
    ORDER BY model, code) X
    ORDER BY L1.model;

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

PostgreSQL

Обратите внимание на предикат ON TRUE. Поскольку синтаксис соединения [LEFT|RIGHT [OUTER]] JOIN требует предиката, то для единообразия используется “фиктивный” предикат, имеющий значение ИСТИНА.

SELECT * FROM laptop L1
    LEFT JOIN LATERAL
    (SELECT *
    FROM Laptop L2
    WHERE L1.model < L2.model OR (L1.model = L2.model AND L1.code < L2.code)
    ORDER BY model, code LIMIT 1) X ON TRUE
    ORDER BY L1.model;

#SQL Server #Коррелирующие Подзапросы #CROSS APPLY #OUTER APPLY #Конструктор Таблицы #PostgreSQL #Cross Join Lateral #Lateral

Оператор MERGE Функция CONCAT