New sql features

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:

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,
(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;

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;

    Scheme

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

Console

Execute

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;

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;

    Scheme

Another example.

Combine each row in the Laptop table with next row in the order of sorting by (model, code).

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,

Console

Execute

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;

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;

    Scheme

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.

Console

Execute

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;

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;

    Scheme

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.

From Product table, retrieve for each group characterized by the type of the product three models with the lowest numbers.

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.

Console

Execute

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;

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;

    Scheme

No scheme

Next page