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.
We got simply the cartesian product of Product and Laptop tables. The similar result we would obtain with aid of the following standard queries:
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:
Usage of CROSS APPLY leads to the following solution:
"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:
So far, the solution using CROSS APPLY is similar to previous one generally: