CROSS APPLY / OUTER APPLY

CROSS APPLY is operator that appeared in SQL Server 2005. It allows two table expressions to be joined together in the following manner: each row from the left-hand table is being combined with each row from the right-hand table.

Let’s try to find out the possibilities of this operator and what advantages we gain from usage of it.

The first example.

select * from
Product
cross apply
Laptop;

We got simply the cartesian product of Product and Laptop tables. The similar result we would obtain with aid of the following standard queries:

select * from
Product
cross join
Laptop;

select * from
Product, Laptop;

Let’s set more reasonable task.

This exercise we can solve with aid of ordinary join:

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

Usage of CROSS APPLY leads to the following solution:

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

 “Does it give us any innovations”? - You could ask. The query does not become shorter in size. This is true. But yet here we could notice very important feature which distinguishes CROSS APPLY from other joins. Namely, we use correlated subquery in FROM clause and are passing into it the values from left-hand table expression. In the given example this value is P.model. I.e. each row from left-hand table will have its own right-hand table to join.

As soon as understanding it, we can use this feature. One more task.

We can solve this task with aid of correlated subquery in the SELECT clause:

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;

So far, the solution using CROSS APPLY is similar to previous one generally:

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;

Let’s imagine that along with maximal price, you need minimal price, average price and so on. Correlated subquery in SELECT clause must return only single value, so we are forced to duplicate sql-code for each aggregate:

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;

But when using CROSS APPLY, we simply add into subquery a wanted aggregate function as a new column:

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;

Another example.

The code column is used in sorting to get single-valued ordering for rows with the same value in the model column. We’ll use CROSS APPLY operator to pass into subquery the parameters of a current row and take the first row under this current row in the given sorting. So,

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;

Try to solve this problem using standard means and compare the efforts required.

OUTER APPLY operator

As the results of above query have shown, we “lost” the last (sixth) row in Laptop table because there is no row to
combine it with. In other words, CROSS APPLY behaves itself as inner join. An analog of outer (left) join is just OUTER APPLY operator. The latter differs from CROSS APPLY in just that OUTER APPLY returns all the rows from left-hand table when replacing the missing values from right-hand table by NULLs.

Replacement CROSS APPLY by OUTER APPLY illustrates above said.

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;

Another popular problem is to output the same number of rows for each group, for example when it is needed to advertise the 5 most popular goods within each category. Let’s consider the following exercise.

Solutions to this problem on the site sql-ex.ru we’ll supplement with another one solution using CROSS APPLY.
The idea of the solution consists in joining of unique product types (the first query) with the query which returns three models of the each type from the first query in accordance with given sorting.

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;

At last, let’s consider an example of the task encountered frequently in practice, namely the task of putting values from rows in columns. To be specific, the formulation will be the following.

We’ll use table constructor where the values of table columns will be passed in with aid of CROSS APPLY operator. Let’s consider this method in details.

Table constructor can be used not only in INSERT statement but also for building the table in FROM clause of SELECT statement, for example,

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

This table in our example is named Spec and includes two columns - name (character strings) and value (numeric).

Now we use this table in CROSS APPLY operator which will combine each row in Laptop table with four rows from such generated table:

select code, name, value
from Laptop
CROSS APPLY (
values('speed', 1)
,('ram', 1)
,('hd', 1)
,('screen', 1)
) Spec(name, value)
where code < 4 -- for reducing the result set only
;

Actually, we need to use the main property of CROSS APPLY operator - using correlated subquery - and to replace units in the value column by column names of the table to be joined.

select code, name, value from Laptop
cross APPLY
(values('speed', speed)
,('ram', ram)
,('hd', hd)
,('screen', screen)
) spec(name, value)
where code < 4 -- for reducing the result set only
order by code, name, value;

Suggested exercises: 97, 117, 132, 145