CROSS APPLY vs OUTER APPLY operators in SQL Server
In my [previous post] we learnt about UDFs, their types and implementations. 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 use special join-keyword called APPLY operator.
According to MS BOL an APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
–> There are 2 forms of APPLY operators:
1. CROSS APPLY acts as INNER JOIN, returns only rows from the outer table that produce a result set from the table-valued function.
2. 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.
Lets take 2 tables: Person.Contact & Sales.SalesOrderHeader
SELECT * FROM Person.Contact WHERE ContactID = 100 SELECT * FROM Sales.SalesOrderHeader WHERE ContactID = 100
You have a UDF that returns Sales Order Details of a Particular Contact. Now you want to use that UDF to know what all Contacts have Ordered what with other details, let’s see:
–> First creating a UDF to test with JOINS & APPLY:
--// Create Multiline UserDefinedFunction [dbo].[ufn_mtv_GetContactSales] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_mtv_GetContactSales](@ContactID int) RETURNS @retSalesInfo TABLE ( [ContactID] INT NOT NULL, [SalesOrderID] INT NULL, [ProductID] INT NULL, [Name] NVARCHAR(50) NULL, [OrderDate] DATETIME NULL, [DueDate] DATETIME NULL, [ShipDate] DATETIME NULL, [TotalDue] MONEY NULL, [Status] TINYINT NULL, [SalesPersonID] INT NULL) AS BEGIN IF @ContactID IS NOT NULL BEGIN INSERT @retSalesInfo SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate], h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID] FROM Sales.SalesOrderHeader AS h JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID JOIN Production.Product AS p ON p.ProductID = d.ProductID WHERE ContactID = @ContactID END -- Return the recordsets RETURN END --// Test the UDF SELECT * FROM dbo.ufn_mtv_GetContactSales(100)
–> Trying to JOIN UDF with a table, problem is you need to apply a parameter and it can’t be a column, but a value:
--// UDF with JOIN, try it out!!! SELECT * FROM Person.Contact c JOIN dbo.ufn_mtv_GetContactSales(100) f -- You will have to pass the ContactID parameter, so no use of joining. ON f.ContactID = c.ContactID
–> Testing with CROSS APPLY:
--// CROSS APPLY -- 279 records (All matched records, 1 missing out of 280) SELECT c.[ContactID], c.[FirstName], c.[LastName], c.[EmailAddress], c.[Phone], s.* FROM Person.Contact AS c CROSS APPLY ufn_mtv_GetContactSales(c.ContactID) AS s WHERE c.ContactID between 100 and 105 -- Same equivalent query without cross apply, using JOINs -- 279 records SELECT c.[ContactID], c.[FirstName], c.[LastName], c.[EmailAddress], c.[Phone], h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate], h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID] FROM Person.Contact AS c JOIN Sales.SalesOrderHeader AS h ON c.ContactID = h.ContactID JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID JOIN Production.Product AS p ON p.ProductID = d.ProductID WHERE c.ContactID between 100 and 105
–> Testing with OUTER APPLY:
--// OUTER APPLY -- 280 records (All 280 records with 1 not matched) SELECT c.[ContactID], c.[FirstName], c.[LastName], c.[EmailAddress], c.[Phone], s.* FROM Person.Contact AS c OUTER APPLY ufn_mtv_GetContactSales(c.ContactID) AS s WHERE c.ContactID between 100 and 105 -- Same equivalent query without OUTER APPLY, using LEFT JOINs -- 280 records SELECT c.[ContactID], c.[FirstName], c.[LastName], c.[EmailAddress], c.[Phone], h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate], h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID] FROM Person.Contact AS c LEFT JOIN Sales.SalesOrderHeader AS h ON c.ContactID = h.ContactID LEFT JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID LEFT JOIN Production.Product AS p ON p.ProductID = d.ProductID WHERE c.ContactID between 100 and 105
>> Check & Subscribe my [YouTube videos] on SQL Server.
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005
http://www.sqlservercentral.com/articles/T-SQL/63115/
http://www.mssqltips.com/tip.asp?tip=1958
Thanks for the article Manoj, I feel this is very good step by step explaination of APPLY.
I searched lot of sites and they don’t clearly explaing the concept of apply very well.
For those who don’t know APPLY and know Joins well then this is the blog to read to know apply.
Thanks @SQL Dev… appreciate that my article helped you 🙂
Sorry for responding late.
Thanks,
Manoj