   12:55

# CROSS APPLY / OUTER APPLY page 1

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
`SELECT * FROM ProductCROSS APPLYLaptop;`

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
`SELECT * FROM ProductCROSS JOINLaptop;`
or  Console
`SELECT * FROM Product, Laptop;`

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
`SELECT P.maker, L.*  FROM Product P JOIN Laptop L ON P.model= L.model;`

Usage of CROSS APPLY leads to the following solution:  Console
`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.

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
`SELECT *, (SELECT MAX(price) FROM Laptop L2JOIN  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:  Console
`SELECT * FROM laptop L1 CROSS APPLY (SELECT MAX(price) max_price FROM Laptop L2JOIN  Product P1 ON L2.model=P1.model WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) X;`

 Pages 1 2 3  