New sql features

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.

Console

Execute

SELECT * FROM 
Product
CROSS APPLY
Laptop;

select * from Product cross apply Laptop;

    Scheme

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

Console

Execute

SELECT * FROM 
Product
CROSS JOIN
Laptop;

select * from Product cross join Laptop;

    Scheme

Console

Execute

SELECT * FROM 
Product, Laptop;

select * from Product, Laptop;

    Scheme

Let's set more reasonable task.

For each laptop, get additional column with maker name.

This exercise we can solve with aid of ordinary join:

Console

Execute

SELECT P.maker, L.*  FROM 
Product P JOIN Laptop L ON P.model= L.model;

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

    Scheme

Usage of CROSS APPLY leads to the following solution:

Console

Execute

SELECT P.maker, L.*  FROM 
Product P 
CROSS APPLY
(SELECT * FROM Laptop L WHERE P.model= L.model) L;

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

    Scheme

 "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.

For each laptop, find additionally out max price among all laptops which the maker of this laptop produces.

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

Console

Execute

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;

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;

    Scheme

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

Console

Execute

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;

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;

    Scheme

Next page