Книга
Новое в стандарте и реализациях языка SQL
CROSS APPLY / OUTER APPLY

CROSS APPLY / OUTER APPLY

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

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

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

select * 
from Product
    cross apply Laptop;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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

select * 
from Product
    cross join Laptop;
🚫
[[ error ]]
[[ column ]]
[[ value ]]
или

select * 
from Product, Laptop;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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

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

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

select P.maker, L.* 
from Product P 
    join Laptop L on P.model= L.model;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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

select P.maker, L.* from
Product P
    CROSS APPLY (select * 
                 from Laptop L 
                 where P.model= L.model) L;
🚫
[[ 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;
🚫
[[ 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;
🚫
[[ 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;
🚫
[[ 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;
🚫
[[ 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;
🚫
[[ 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;
🚫
[[ 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;
🚫
[[ 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);
🚫
[[ 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 -- для уменьшения размера выборки
;
🚫
[[ 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;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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

PostgreSQL

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;
🚫
[[ 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;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Пример 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;
🚫
[[ 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;
🚫
[[ error ]]
[[ column ]]
[[ 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;
🚫
[[ 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;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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;
🚫
[[ 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;
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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

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