जगदीश खोलिया: APPLY operator (CROSS Apply and OUTER Apply) in SQL Server

Tuesday, June 5, 2012

APPLY operator (CROSS Apply and OUTER Apply) in SQL Server

The APPLY operator let's you join a table to a table-valued-function.UDFs can be used in queries at column level, table levels and on column definition while creating tables.
They can also be joined with other tables, but not by simple joins. They have special joins called APPLY operator.There are 2 types of APPLY:
- CROSS APPLY acts as INNER JOIN, returns only rows from the outer table that produce a result set from the table-valued function.
- OUTER APPLY acts as OUTER JOIN, returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
 eg:
CREATE Procedure [dbo].[Test_Apply]
(
 ContactId varchar(50)
)
As
BEGIN
SELECT name.Customer_Id,name.CustomerName,name.Email,name.MobileNo
,name.EntryDate,name.ProductID,name.Product
FROM Test_Lead lead
INNER JOIN customer cust ON cust.CustomerId = lead.CustomerID
outer apply dbo.fn_Test_GetLatestResponse_Customerwise(lead.Customer_ID) sales
cross apply dbo.fn_Test_GetCustomerDetails(lead.Customer_ID) name
inner join LMS_UserDetails agent on agent.ID = lead.Allocated_To
WHERE lead.ProductId = name.ProductID
END


No comments: