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)
SELECT TOP(@no) WITH TIES ProductID,ProductName,UnitPrice
FROM Products
WHERE SupplierID = @sup_id AND CategoryID = @cat_id

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.

A Rahim Khan

  1. No trackbacks yet.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: