Advertisements

Archive

Posts Tagged ‘APPLY Operator’

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


–> 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.


Advertisements

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.

more examples on querying XML …with CROSS APPLY & XQuery – in SQL Server

September 22, 2011 1 comment

My quest to know more about XML and to use it in SQL language always encourages me to google this topic and religiously follow the MSDN’s SQL Server XML forum. I liked this post on XML, it contains some more ways to query XML data cited by some SQL experts.

In my previous posts I’ve shown some practical examples on how to query your XML data or string and get results in tabular format. Following are the links I’ve previously posted on this topic:

1. https://sqlwithmanoj.com/2011/01/28/select-an-xml-to-table/

2. https://sqlwithmanoj.com/2011/07/13/query-xml-string-in-tabular-format/

3. https://sqlwithmanoj.com/2011/07/13/select-or-query-nodes-in-hierarchial-or-nested-xml/
 

–> Let’s see one more example and multiple ways to query an XML string:

DECLARE @XML XML
SET @XML =
'<Input>
    <Courses>
        <Course>
            <Id>27</Id>
            <Students>
                <Id>19876</Id>
                <Id>19878</Id>
            </Students>
        </Course>
        <Course>
            <Id>29</Id>
            <Students>
                <Id>19879</Id>
            </Students>
        </Course>
    </Courses>
</Input>'
-> Desired Output: CourseId Students
27	 19876
27	 19878
29	 19879
-- Method# 1. Simple approach but bit costly | Query Cost: 83%
select t.c.value('../../Id[1]', 'INT') as CourseId,
	t.c.value('.', 'INT') as Students
from @XML.nodes('//Input/Courses/Course/Students/Id') as t(c)

-- Method# 2. By using Cross Apply | Query Cost: 17%
select t.c.value('Id[1]', 'INT') as CourseId,
	t1.c1.value('.', 'INT') as Students
from @XML.nodes('//Input/Courses/Course') as t(c)
cross apply t.c.nodes('Students/Id') as t1(c1)

The above 2 approaches shows that the second one with CROSS APPLY is much more performant.

– The traditional approach (#1) traverses the nodes (parent/child) and pulls the desired data.

– But the 2nd one with APPLY clause fetches specific node’s entire row and join it with the SELECTed data.
 

The NODES() function allows us to identify a particular node and map it into a new row. As the NODES function returns a rowset thus it can be queried by a SELECT statement or used like a UDF by applying APPLY clause. More on NODES(), here’s the link.
 

–> Let’s see an another approach by applying XQuery in a QUERY() function. The XQuery is a string, an XQuery expression, that queries for XML nodes such as elements, attributes, in an XML instance. More on QUERY(), here’s the link.

-- Method# 3. By using XML Query:

DECLARE @temp XML
set  @temp = @XML.query('
for $a in /Input/Courses/Course/Id,
    $b in /Input/Courses/Course/Students/Id
where $a/.. is $b/../..
return element detail {attribute CourseID {string($a)},attribute StudentID {string($b)}}')

select t.c.value('@CourseID','int') as [CourseID],
	t.c.value('@StudentID','int') as [StudentID]
from @temp.nodes('/detail') as t(c)

Will see some more examples & more stuff on XML in my forthcoming posts.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Categories: XML Tags: , , ,

SELECT or Query nodes in hierarchial or nested XML

July 13, 2011 15 comments

On this post I’ll show how to retrieve information from nested XML.

I’ve shown this with an example with 2 approaches/methods and it’s up to you what to chose based upon their performance. Here we go:

DECLARE @xml XML

SET @xml=N'<Root>
	<orderHRD ID="101">
		<custID>501</custID>
		<orderDTL ID="201">
			<prodID>1</prodID>
			<qty>5</qty>
			<cost>25.12</cost>
		</orderDTL>
		<orderDTL ID="202">
			<prodID>2</prodID>
			<qty>3</qty>
			<cost>30.00</cost>
		</orderDTL>
	</orderHRD>
	<orderHRD ID="102">
		<custID>502</custID>
		<orderDTL ID="203">
			<prodID>11</prodID>
			<qty>12</qty>
			<cost>140.78</cost>
		</orderDTL>
	</orderHRD>
	<orderHRD ID="103">
		<custID>503</custID>
		<orderDTL ID="204">
			<prodID>6</prodID>
			<qty>8</qty>
			<cost>60.35</cost>
		</orderDTL>
		<orderDTL ID="205">
			<prodID>9</prodID>
			<qty>2</qty>
			<cost>10.50</cost>
		</orderDTL>
		<orderDTL ID="206">
			<prodID>10</prodID>
			<qty>6</qty>
			<cost>120.89</cost>
		</orderDTL>
	</orderHRD>
</Root>';

-- Method #1: Query nested XML nodes by traversing backward and forward.
-- Query Cost: 81%
select
	Tab.Col.value('../@ID', 'int') as OrderHDR_ID,
	Tab.Col.value('../custID[1]', 'int') as Cust_ID,
	Tab.Col.value('@ID', 'int') as OrderDTL_ID,
	Tab.Col.value('prodID[1]', 'int') as Prod_ID,
	Tab.Col.value('qty[1]', 'int') as QTY,
	Tab.Col.value('cost[1]', 'float') as Cost,
	Tab.Col.value('count(../orderDTL)', 'int') as Cust_Ord_Count
from @xml.nodes('/Root/orderHRD/orderDTL') Tab(Col)

-- Method #2: Query nested XML nodes by using CROSS APPLY on appropriate node.
-- Query Cost: 19%
select
	Tab.Col.value('@ID', 'int') as OrderHDR_ID,
	Tab.Col.value('custID[1]', 'int') as Cust_ID,
	Tab1.Col1.value('@ID', 'int') as OrderDTL_ID,
	Tab1.Col1.value('prodID[1]', 'int') as Prod_ID,
	Tab1.Col1.value('qty[1]', 'int') as QTY,
	Tab1.Col1.value('cost[1]', 'float') as Cost,
	Tab.Col.value('count(./orderDTL)', 'int') as Cust_Ord_Count
from @xml.nodes('/Root/orderHRD') as Tab(Col)
cross apply Tab.Col.nodes('orderDTL') as Tab1(Col1)
GO

Output:-
OrderHDR_ID Cust_ID OrderDTL_ID Prod_ID QTY Cost
101	    501	    201		1	5   25.12
101	    501	    202		2	3   30
102	    502	    203		11	12  140.78
103	    503	    204		6	8   60.35
103	    503	    205		9	2   10.5
103	    503	    206		10	6   120.89

Method #2 is much more effecient than first one. As 1st method uses forward-backward traversal approach. The 2nd method uses APPLY operator which gets Nodes information from a specific parent.

This topic was also discussed on MSDN TSQL’s forum few weeks back, link.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


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.