OUTER APPLY | SQL with Manoj

SQL Server CROSS APPLY and OUTER APPLY usage – MSDN TSQL forum

May 29, 2016 Leave a comment

–> Question:

I need to see two small scenario when people should use CROSS APPLY and OUTER APPLY.

Please discuss the scenario with code and example.

Thanks !

–> My Answer:

CROSS APPLY acts like an INNER JOIN, and OUTER APPLY acts like a LEFT OUTER JOIN.

–> The APPLY clause (irrespective of CROSS/OUTER option) gives you flexibility to pass table’s columns as parameters to UDFs/functions while Joining while that table. It was not possible with JOINS. The function will execute for each row value passed to the UDF as parameter. But the JOIN works as a whole set.

Check the blog post on CROSS APPLY vs OUTER APPLY operators, https://sqlwithmanoj.com/2010/12/11/cross-apply-outer-apply/

–> Apart from this you can also use APPLY clause with Tables/SubQueries, like if you want to get top 5 products sold by sales persons, or get top 10 populated Cities from all States.

Check here: Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived-Tables/Sub-Queries & XML data, https://sqlwithmanoj.com/2012/01/03/using-cross-apply-outer-apply-operators-with-udfs-derived-tables-xml-data/

–> Answer from Russ Loski:

First let’s start with the use for APPLY.

You would use APPLY if you need to use a column from a table as an argument in a derived table or function. For example, this query from http://blog.sqlauthority.com/2009/08/21/sql-server-get-query-plan-along-with-query-text-and-execution-count/:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

I need to use columns from dm_exec_cahced_plans to pass to two functions to get rows from those functions. I have to use the APPLY keyword (rather than Join) to be able to do that.

I can do the same with a derived table:

SELECT * 
FROM TableA 
outer apply (
SELECT * from TableB
where TableA.id = TableB.id
) tb2

That is a horrible example (you can do the same using standard join syntax). But there are very rare circumstances where I need to use a column in a where clause in a derived table, but I can’t use a join.

The difference between CROSS APPLY and OUTER APPLY is the difference between INNER JOIN and OUTER JOIN. CROSS APPLY will only return rows where there is a row in both the first table and the second table/function, while OUTER APPLY returns a row if there is a row in the first Table even if the second table/function returns no rows.

Ref link.

Categories: Differences Tags: APPLY Operator, CROSS APPLY, MSDN TSQL forum, OUTER APPLY

Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived tables & XML data

January 3, 2012 4 comments

In my previous posts CROSS APPLY vs OUTER APPLY I discussed on the same topic, but didn’t covered much on examples. Here we will see more examples on using APPLY operators, their workarounds and performance.

>> Using APPLY operator with User Define Functions:

USE [AdventureWorks]
GO

-- Create an "Inline Table-valued Function" that returns Sales Details of a Contact (Customer):
CREATE FUNCTION [dbo].[ufnGetSalesInfo](@ContactID int)
	RETURNS TABLE 
AS 
RETURN (
	SELECT SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, CustomerID, TotalDue
	FROM Sales.SalesOrderHeader
	WHERE ContactID = @ContactID
)
GO

-- We can use this function with SELECT FROM statement like we do for a table:
SELECT * FROM [dbo].[ufnGetSalesInfo](100)

Here, we cannot provide multiple IDs or any variable to make it work for multiple or dynamic Contacts. To return Sales details for multiple Customers we need to JOIN it with Contacts table, but JOINS are now allowed with UFDs, they only work with Tables/Views.

So to JOIN a UDF with a TABLE we need an APPLY operator, check examples below:

-- CROSS APPLY acts as INNER JOIN & will return only matching rows, returns 513 rows:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
CROSS APPLY [dbo].[ufnGetSalesInfo](con.ContactID) x
WHERE con.ContactID <= 100

-- OUTER APPLY acts as OUTER LEFT JOIN & will return all matching & non-matching rows, returns 529 rows:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
OUTER APPLY [dbo].[ufnGetSalesInfo](con.ContactID) x
WHERE con.ContactID <= 100


-- Final Cleanup
DROP FUNCTION [dbo].[ufnGetSalesInfo]
GO

>> Using APPLY operator with Derived tables:

[Scenario]: Pull upto 5 Sales Orders for each Contact (Customer):

This is not possible with JOINS, because the condition to SELECT max 5 Orders is applied after the JOIN is made, we need to add the condition before JOIN. Using APPLY operator we can do this, let’s see how:

-- With Derived Table:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
CROSS APPLY (SELECT TOP 5 
				SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, CustomerID, TotalDue 
			 FROM Sales.SalesOrderHeader soh
			 WHERE soh.ContactID = con.ContactID) x
WHERE con.ContactID <= 100

-- We can also write the above query by using ROW_NUMBER() function & JOINs instead of the APPLY operator:
;WITH CTE AS (
	SELECT 
		SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, ContactID, TotalDue,
		ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY ContactID, SalesOrderID) AS ROWNUM
		FROM Sales.SalesOrderHeader)
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	CTE.*
FROM Person.Contact con
JOIN CTE cte
ON cte.ContactID = con.ContactID
WHERE con.ContactID <= 100
AND cte.ROWNUM <= 5
GO

Both of these queries do the same task. So to check which one is more optimized we can check the cost of each query in the batch. So the cost of query using APPLY operator is 84% and query using ROW_NUMBER & JOIN is 16%. Hence the second approach is much more performant & should be considered.

>> Using APPLY operator with XML data:

APPLY operators can also we used with XML data when there is a need to query parent/child nodes, check my previous blog post on this topic.

Categories: SQL Server 2005 Tags: APPLY Operator, CROSS APPLY, OUTER APPLY

CROSS APPLY vs OUTER APPLY operators in SQL Server

December 11, 2010 7 comments

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.

Categories: Differences Tags: APPLY Operator, CROSS APPLY, OUTER APPLY