solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















SQL Server CROSS APPLY and OUTER APPLY

MSSQLTips author Arshad Ali By:   |   Read Comments (24)   |   Related Tips: More > JOIN Tables
Problem

SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications.

Solution

The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. As you might have guessed, the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query. Let me explain you with help of some examples.

Script #1 creates a Department table to hold information about departments. Then it creates an Employee table which hold information about the employees. Please note, each employee belongs to a department, hence the Employee table has referential integrity with the Department table.

Script #1 - Creating some temporary objects to work on...

USE [tempdb]
GO
IF EXISTS (SELECT FROM sys.objects WHERE OBJECT_ID OBJECT_ID(N'[Employee]') AND type IN (N'U'))
BEGIN
   DROP TABLE 
[Employee]
END
GO
IF EXISTS (SELECT FROM sys.objects WHERE OBJECT_ID OBJECT_ID(N'[Department]') AND type IN (N'U'))
BEGIN
   DROP TABLE 
[Department]
END
CREATE TABLE 
[Department](
   
[DepartmentID] [int] NOT NULL PRIMARY KEY,
   
[Name] VARCHAR(250) NOT NULL,
ON [PRIMARY]
INSERT [Department] ([DepartmentID][Name]
VALUES (1N'Engineering')
INSERT [Department] ([DepartmentID][Name]
VALUES (2N'Administration')
INSERT [Department] ([DepartmentID][Name]
VALUES (3N'Sales')
INSERT [Department] ([DepartmentID][Name]
VALUES (4N'Marketing')
INSERT [Department] ([DepartmentID][Name]
VALUES (5N'Finance')
GO
CREATE TABLE [Employee](
   
[EmployeeID] [int] NOT NULL PRIMARY KEY,
   
[FirstName] VARCHAR(250) NOT NULL,
   
[LastName] VARCHAR(250) NOT NULL,
   
[DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
ON [PRIMARY]
GO
INSERT [Employee] ([EmployeeID][FirstName][LastName][DepartmentID])
VALUES (1N'Orlando'N'Gee')
INSERT [Employee] ([EmployeeID][FirstName][LastName][DepartmentID])
VALUES (2N'Keith'N'Harris')
INSERT [Employee] ([EmployeeID][FirstName][LastName][DepartmentID])
VALUES (3N'Donna'N'Carreras')
INSERT [Employee] ([EmployeeID][FirstName][LastName][DepartmentID])
VALUES (4N'Janet'N'Gates'
)

First query in Script #2 selects data from Department table and uses CROSS APPLY to evaluate the Employee table for each record of the Department table. Second query simply joins the Department table with the Employee table and all the matching records are produced.

Script #2 - CROSS APPLY and INNER JOIN

SELECT FROM Department D
CROSS APPLY
   
(
   
SELECT FROM Employee E
   
WHERE E.DepartmentID D.DepartmentID
   
A
GO
SELECT FROM Department D
INNER JOIN Employee E ON D.DepartmentID 
E.DepartmentID
GO

cross apply and inner join result set

If you look at the results they produced, it is the exact same result-set; not only that even the execution plan for these queries are similar to each other and has equal query cost, as you can see in the image below. So what is the use of APPLY operator?  How does it differ from a JOIN and how does it help in writing more efficient queries. I will discuss this later, but first let me show you an example of OUTER APPLY also.

cross apply and inner join query plan

The first query in Script #3 selects data from Department table and uses OUTER APPLY to evaluate the Employee table for each record of the Department table.  For those rows for which there is not a match in Employee table, those rows contains NULL values as you can see in case of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table.  As expected the query returns all rows from Department table; even for those rows for which there is no match in the Employee table.

Script #3 - OUTER APPLY and LEFT OUTER JOIN

SELECT FROM Department D
OUTER APPLY
   
(
   
SELECT FROM Employee E
   
WHERE E.DepartmentID D.DepartmentID
   
A
GO
SELECT FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID 
E.DepartmentID
GO
outer apply and left outer join result set

Even though the above two queries return the same information, the execution plan is a bit different. Although cost wise there is not much difference, the query with the OUTER APPLY uses a Compute Scalar operator (which has an estimated operator cost of 0.0000103 or almost 0% of total query cost) before Nested Loops operator to evaluate and produce the columns of Employee table.

outer apply and left outer join query plan

Now comes the time to see where the APPLY operator is really required. In Script #4, I am creating a table-valued function which accepts DepartmentID as its parameter and returns all the employees who belong to this department. The next query selects data from Department table and uses CROSS APPLY to join with the function we created.  It passes the DepartmentID for each row from the outer table expression (in our case Department table) and evaluates the function for each row similar to a correlated subquery. The next query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns.

Script #4 - APPLY with table-valued function

IF EXISTS (SELECT FROM sys.objects WHERE OBJECT_ID OBJECT_ID(N'[fn_GetAllEmployeeOfADepartment]') AND type IN (N'IF'))
BEGIN
   DROP FUNCTION 
dbo.fn_GetAllEmployeeOfADepartment
END
GO
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT
RETURNS TABLE
AS
RETURN
   
(
   
SELECT FROM Employee E
   
WHERE E.DepartmentID @DeptID
   
)
GO
SELECT FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
SELECT FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

apply with table valued function result set

So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, if you replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentID" could not be bound."  error. This is because with JOINs the execution context of outer query is different from the execution context of the function (or a derived table), and you can not bind a value/variable from the outer query to the function as a parameter.  Hence the APPLY operator is required for such queries.

So in summary the APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.

Now let me show you another query with a Dynamic Management Function (DMF). Script #5 returns all the currently executing user queries except for the queries being executed by the current session. As you can see the script below, the sys.dm_exec_requests dynamic management view is being CROSS APPLY'ed with the sys.dm_exec_sql_text dynamic management function which accepts a "plan handle" for the query and the same "plan handle" is being passed from the left/outer expression to the function to work and to return the data.

Script #5 - APPLY with Dynamic Management Function (DMF)

USE master
GO
SELECT DB_NAME(database_idAS [Database][text] AS [Query] 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handlest
WHERE session_Id 50           -- Consider spids for users only, no system spids.
AND session_Id NOT IN (@@SPID)  
-- Don't include request from current spid.

Please note the [text] column in the above query returns the all queries submitted in a batch; if you want to see only active (currently executing) query you can use statement_start_offset and statement_end_offset columns to trim the active part of the query. Tim Ford has provided a very good explanation of usage of these columns in his How to isolate the current running commands in SQL Server tip.

As I told you before there are certain scenarios where a query with APPLY operator performs better than a query with regular joins but I am not going to delve into much details rather here are some articles which discuss this topic in greater details.

Please note, APPLY operator is not an ANSI operator but rather an extension of SQL Server T-SQL (available in SQL Server 2005 and above), so if you plan to port your database to some other DBMS take this into your considerations.

Next Steps


Last Update: 3/9/2010


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Wednesday, November 17, 2010 - 5:02:11 AM - Sridhar Read The Tip

Very good article.


Wednesday, July 13, 2011 - 10:31:56 AM - Alexander Read The Tip

Beautiful overview on the subject. Thank you!


Friday, September 02, 2011 - 10:24:50 AM - Kit Read The Tip

Thanks for the overview, but I'm still unsure about the need for cross apply.  In your example you state that simple joins would not accomplish your cross apply results:

SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

But the following seems to do just that in a simpler and more readable format (because functionality is not black-boxed in a function):

select * from department d inner join employee e on d.departmentid=e.departmentid

If the performance is the same and the code is more readable, why was CROSS APPLY ever created and why use it? After reading several articles on this, I suspect that CROSS APPLY was created because table-valued functions are extremely limited without CROSS/OUTER APPLY.  This is readily apparent when you look at dynamic management functions (DMF) which are two-dimensional and hobbled without CROSS APPLY functionality.

So I guess this begs a larger question: are table-valued functions necessary?  I thought they were a great idea, but I've yet to actually use one in a production environment.  I've never needed them.

 

 


Sunday, January 15, 2012 - 5:43:54 AM - Neelam Read The Tip

Excellent article!


Tuesday, February 07, 2012 - 9:05:43 AM - Crew Read The Tip

I'm confused.  I still don't know why I would use APPLY.  Your examples show a more complicated way to create inner and outer joins, but I fail to see the benefit.  Please show an example that demonstrates beneifits not found in simple joins.


Saturday, February 11, 2012 - 5:05:54 AM - harsh Read The Tip

thanks for such a valueble post......

nice solution...:-)


Monday, April 02, 2012 - 7:48:30 AM - Yorgos Read The Tip

one of the reasons outer apply is useful is shown in the following example:

select

*from

sysusers

outer

apply(selecttop 1 *fromsysobjectswheresysobjects.uid=sysusers.uidorderbysysobjects.crdate desc)objects

What this does is for each user in sysusers, returns all the columns and also all the columns for the latest object they have created. Try to write this without the outer apply and you will see why it is useful.

I hope this helps.

 

 


Monday, April 02, 2012 - 7:51:40 AM - Yorgos Read The Tip

Sorry forgot to format the code :(

select * from sysusers
outer apply (select top 1 * from sysobjects where sysobjects.uid = sysusers.uid order by sysobjects.crdate desc) objects


Sunday, April 29, 2012 - 4:35:26 PM - Jan Read The Tip

Hi Arshad,

Thanks a lot for posting this, it helped me a great deal with understanding what APPLY does, and how it's different from a JOIN.

Cheers,

Jan


Thursday, July 12, 2012 - 2:30:45 AM - Brian Kirton Read The Tip

Absolutely STUNNING

 

For years I've been looking for a method of joining a table to a function that uses one or more of the Table columns as a parameter in the function I'm joining to.

 

And this is it!!

 

You have saved me from being forced to use a CURSOR and it's about double the speed.

 

THANK YOU   THANK YOU ! ! !


Wednesday, September 12, 2012 - 7:18:02 AM - Asheej Read The Tip

Nice article, I have been forced to use outer apply today so thought of reading an article related to the Apply operator... you have witten the article in simple words anybody can understand..keep posting...

 

Asheej

MVP in ASP.NET/IIS

 


Sunday, November 04, 2012 - 1:43:16 PM - Bill Ross Read The Tip

Very nice writeup.

Maybe you can answer this... I want to return data from multiple tables, collated, like this:

 

T1.Value

T2.Value

T3.Value

T2.Value

T3.Value

etc, like this:

 

Customer 1

Order Number 1

Order Number 2

Customer 2

Order Number 1

Order Number 2

etc..

 

How can I do that vertical alignment, rather than a table?

I could do UNION but the UNION will give me all values from Table 1, then from Table 2 etc.

How would I do the collation?

 

Thanks!

 


Tuesday, January 01, 2013 - 10:20:02 PM - Abhijit Read The Tip

Hi Arshad,

 

Thanks for the info.

 

we have below scenario, where we are comparing two tables & need to make sure data is proper or not.

 

Can we use the Corss Apply for Data Quality check like below query.

 

Sample Query :  

selectdiff.MismatchColumns,t1.*,t2.*

fromgsxaccountstestt1

leftouterjoinAccountt2on (t1.AccountName=t2.Name)

crossapply(selectstuff((select', '+t.nameas[text()]

from (

select'GlobalCRMId'asnamewheret1.globalcrmidisnullort2.Accountnumberisnull

unionallselect'AccountName'wherenot((t1.AccountNameisnullandt2.Nameisnull)or(t1.AccountName=t2.Name))

unionallselect'AccountID'wherenott1.globalcrmid=t2.Accountnumber

unionallselect'AccountName'wherenott1.AccountName=t2.ParentAccountIdName

unionallselect'StreetAddress1'wherenott1.StreetAddress1=t2.Address1_Line1

unionallselect'StreetAddress2'wherenott1.StreetAddress2=t2.Address1_Line2

unionallselect'StreetAddress3'wherenott1.StreetAddress3=t2.Address1_Line3

)t

forxmlpath(''),type

).value('.','varchar(max)'),1, 2,'')asMismatchColumns

)diff

wherediff.MismatchColumnsisnotnull

 

 


Friday, January 11, 2013 - 6:32:34 AM - jose Read The Tip

quote... "So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, if you replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentID" could not be bound."

I have to admit, I dont understand - it appears to me that the result set is exactly the same as the one for inner and that the answer to the question you ask is YES, not NO.

 


Wednesday, March 06, 2013 - 7:33:58 PM - glen Read The Tip

 

Thanks I learned something new. I don’t' see using this often but needed occasionally and needed when I use it.   

 


Monday, April 15, 2013 - 2:28:44 AM - BHEEMRAJ Read The Tip

VERY GOOD ARTICLE.TOTALLY UNDERSTANDABLE.


Sunday, April 28, 2013 - 7:54:37 PM - Prasad Read The Tip

Well done ! ... Nice explanation !


Wednesday, May 29, 2013 - 4:35:08 PM - Oliveira Read The Tip

Congratulations !!! Very good article !


Thursday, May 30, 2013 - 6:34:29 AM - Sajad Manzoor Read The Tip

WOW this article is realy great


Tuesday, June 11, 2013 - 4:56:42 AM - JOEL DMELLO Read The Tip

Nice Article

I Have Been Using OUTER APPLY To Skip Muliple SubQuery In My Main Query

This Ex. Is Very Easy To Understand. Most Of The Time When You Are Trying To Join Two Table Then OUTER APPLY Is UseLess,

It become Very Handy When You Have Complex Query.

Just For Ref.

SELECT

ROW_NUMBER()OVER (PARTITIONBY E1.EnquiryId ORDERBY E1.EnquiryId)AS RowNumber,*

FROM

(

SELECTDISTINCT SO2.OrderId, SQ2.QuotationId, SE2.EnquiryId, SE2.EnquiryNo, TDSE.FileCaption AS TDSEFileCaption,

'..\TransactionDocument\'

+ TDSE.FileNameAS TDSEFileName

FROM

SalesOrder AS SO2 INNER

JOIN

SalesOrderDetails

AS SOD2 ON SO2.OrderId = SOD2.OrderId LEFTOUTERJOIN

SalesQuotationDetails

AS SQD2 INNERJOIN

SalesQuotation

AS SQ2 ON SQD2.QuotationId = SQ2.QuotationId INNERJOIN

SalesEnquiry

AS SE2 ON SQD2.ReferenceID = SE2.EnquiryId ON SOD2.ReferenceID = SQ2.QuotationId LEFTOUTERJOIN

TransactionDocument

AS TDSE ON TDSE.RefId = SE2.EnquiryId AND TDSE.BookTypeCode ='SE'

WHERE

(SO2.OrderId IN(820,606)))AS E1

 


Thursday, July 18, 2013 - 8:30:32 PM - MarthaE Read The Tip

Hi Arshad, thanks a lot for your help.

I need two cross apply with pre_trimmed. it´s possible? In this moment I have the first, but another one  don´t function.

Please help me, answer me urgent, I need it.

 

I´ll like talk to you about this topic.


Wednesday, August 14, 2013 - 12:17:00 AM - Fazarudeen Read The Tip

Nice one. Thankyou

--Fazarudeen

919841807303


Friday, November 01, 2013 - 11:58:18 AM - Samus Arin Read The Tip

What a superbly written article. So clear and detailed. Thanks a lot!


Tuesday, December 10, 2013 - 11:56:08 PM - Muhammad ADIL Read The Tip

Thank you so much. This is very clear and understandable article




Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.