The Apply Operator was introduced in SQL Server 2005. It is very similar to the Join clause, since both allow joining between two table expressions. That means you can use this Apply Operator where you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
Cross Apply
The Apply Operator is offered in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns rows from the left table expression only if they match with the right table expression. In other words, the right table expression returns rows for a left table expression match only.
Outer Apply
The OUTER APPLY clause returns all the rows on the left side table expression whether or not it returns any rows in the right side table expression. If there are not any corresponding matches in the right table expression, it contains NULL values in the columns of the right table expression.
Hence we can conclude that CROSS APPLY is semantically equivalent to INNER JOIN and OUTER APPLY is equivalent to LEFT OUTER JOIN.
In order to study the CROSS APPLY and OUTER APPLY, execute the following queries:
CREATE TABLE Sales(intStoreId BIGINT,intSalesId BIGINT,Amount money)
INSERT INTO Sales VALUES(1,123,45156)
INSERT INTO Sales VALUES(1,234,557456)
INSERT INTO Sales VALUES(1,654,72346)
INSERT INTO Sales VALUES(1,789,90009)
INSERT INTO Sales VALUES(1,555,100678)
INSERT INTO Sales VALUES(1,298,87897)
INSERT INTO Sales VALUES(2,23,5156)
INSERT INTO Sales VALUES(2,34,57456)
INSERT INTO Sales VALUES(2,54,2346)
INSERT INTO Sales VALUES(2,89,20009)
INSERT INTO Sales VALUES(2,55,10678)
INSERT INTO Sales VALUES(2,98,7897)
INSERT INTO Sales VALUES(3,123,95156)
INSERT INTO Sales VALUES(3,134,857456)
INSERT INTO Sales VALUES(3,154,72346)
INSERT INTO Sales VALUES(3,189,620009)
INSERT INTO Sales VALUES(3,855,510678)
INSERT INTO Sales VALUES(3,798,37897)
CREATE TABLE Store(intStoreId INT,chvStoreName VARCHAR(100));
INSERT INTO Store VALUES(1,'Store A')
INSERT INTO Store VALUES(2,'Store B')
INSERT INTO Store VALUES(3,'Store C')
INSERT INTO Store VALUES(4,'Store D')
INSERT INTO Store VALUES(5,'Store E')
Now the table Sales is created with 18 rows and table Store is created with 5 rows
Using the TOP Clause
The following query will allow you to get the first 5 rows from the table sales.
select top 5 * from sales
You can use a variable in the Top clause.
The following query also will return the top 5 rows.
DECLARE @TopRows INT
set @TopRows=5
select top(@TopRows) * from sales
In the same manner you can use anything (Numbers) in the @TopRows place.
SELECT TOP (
SELECT COUNT(*) FROM sales WHERE intStoreId=1
) * FROM sales
There are 18 rows in the Sales Table, but the above query will return only 6 rows.
Also you can use the TOP clause for INSERT, UPDATE and DELETE statements.
Next we will create a function fn_GetTopRows() to return the Top X rows from the Sales Table based on the Amount field.
CREATE FUNCTION fn_GetTopRows
(
@storeid AS INT,
@n AS INT
)
RETURNS TABLE
AS
RETURN
SELECT TOP ( @n )
*
FROM sales
WHERE intstoreid = @storeid
ORDER BY Amount DESC
GO
Function fn_GetTopRows() accepts two parameters and will return TABLE as an output. The SELECT statement will return the top @n records based on the StoreId. Also notice that there is an ORDER BY clause in this function.
Using the CROSS APPLY Clause
The APPLY Clause joins a table to a table-valued-function. Consider the following query:
SELECT a.intStoreId,
b.intSalesId,
b.Amount
FROM Store AS a
CROSS APPLY fn_GetTopRows(a.intStoreId, 3) AS b
ORDER BY a.intStoreId ASC,
b.Amount DESC
Output
In the above example, if I pass just intStoreId to the function, it returns the top 3 rows based on the amount. When I use CROSS APPLY, a store without orders will not appear in the list. Remember, the function has an ORDER BY in it, so it always returns the top entries in order based on the amount. By changing the ORDER BY clause, you can display the records in Ascending form.
Example for OUTER APPLY
SELECT a.intStoreId,
b.intSalesId,
b.Amount
FROM Store AS a
OUTER APPLY fn_GetTopRows(a.intStoreId, 3) AS b
ORDER BY a.intStoreId ASC,
b.Amount DESC
Output
In the above example, I used the OUTER APPLY operator. As a result, all the records from the Store Table are returned, and null values are returned for the table-valued-function whenever there are no orders for the store.