APPLY Table Operator in SQL Server 2005

APPLY Table Operator is available in SQL Server 2005 and later editions. This evaluates the right hand-side table expression to left hand-side table expression. Other way, it correlates each row of left-hand side table expression to right hand-side table expression.

Let’s try to make it clear by coding. You can use NorthWind, AdventureWorks or download the database here to run those scripts.


CREATE FUNCTION udf_Top_Products(@sup_id as int, @cat_id as int, @no as int)
RETURNS TABLE
AS
RETURN 
SELECT TOP(@no) WITH TIES ProductID,ProductName,UnitPrice
FROM Products
WHERE SupplierID = @sup_id AND CategoryID = @cat_id
ORDER BY UnitPrice DESC


This table valued function udf_Top_Products takes three arguments and returns a set of rows.


SELECT * FROM dbo.udf_Top_Products(1,1,2)


This query returns top 2 products in terms of unit price for Supplier ID 1 and Category ID 1. What if you want to get top 2 products for Category ID 1 and for each Supplier ID, APPLY Operator is here to do this efficiently.


SELECT CompanyName, ProductID, ProductName, UnitPrice
FROM Suppliers AS S
CROSS APPLY dbo.udf_Top_Products(S.SupplierID,1,2) 

CROSS APPLY eliminates rows that returns NULL in right-hand side table expression.


SELECT CompanyName, ProductID, ProductName, UnitPrice
FROM Suppliers AS S
OUTER APPLY dbo.udf_Top_Products(S.SupplierID,1,2) 

On the other hand, OUTER APPLY returns all of the rows.

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: