Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories



By:   |   Read Comments (48)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > JOIN Tables

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.


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]
IF EXISTS (SELECT FROM sys.objects WHERE OBJECT_ID OBJECT_ID(N'[Department]') AND type IN (N'U'))
[DepartmentID] [int] NOT NULL PRIMARY KEY,
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')
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),
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.


SELECT FROM Department D
WHERE E.DepartmentID D.DepartmentID
SELECT FROM Department D
INNER JOIN Employee E ON D.DepartmentID 

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.


SELECT FROM Department D
WHERE E.DepartmentID D.DepartmentID
SELECT FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID 
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'))
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT
WHERE E.DepartmentID @DeptID
SELECT FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
SELECT FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

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
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:

signup button

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

SQL tips:

*Enter Code refresh code     

Monday, October 23, 2017 - 4:55:56 PM - Roberto Back To Top

Excellent explained !!!!  


Tuesday, August 08, 2017 - 1:27:02 PM - Ted Higgins Back To Top

Great tip!  Thanks for providing this demo and explanation!


Tuesday, February 28, 2017 - 3:51:24 AM - Thomas Franz Back To Top

I like to use CROSS APPLY to prevent duplicate code, when I have to use the same CASE in multiple parts of a query. Furthermore it is nice to use multiple CROSS APPLY's when you have staggered calculations where the second value depends on the first result, the third is using the second resuld, the forth the third result and so on.



SELECT calc1.val1,

       ROW_NUMBER() OVER (PARTITION BY calc1.val1 ORDER BY calc5.price_gross) pos_number,
  FROM tbl t
                    t.price * (100 - t.discount1) / 100 AS price1) as calc1
CROSS APPLY (SELECT calc1.price1 * (100 - t.discount2) / 100 AS price2) as calc2
CROSS APPLY (SELECT calc2.price2 * (100 - t.discount3) / 100 AS price3) as calc3
CROSS APPLY (SELECT calc3.price3 * (100 - t.discount4) / 100 AS price4) as calc4
CROSS APPLY (SELECT calc4.price4 * (100 + t.VAT) / 100 AS price_gross) as calc5
INNER JOIN tbl2 t2
    ON t2.val1 = calc1.val1
 ORDER BY calc1.val1




Friday, October 28, 2016 - 12:24:00 AM - sharad Back To Top


Really a nice article. I appriciate this. Please keep doing the good work.

Monday, October 03, 2016 - 10:51:20 AM - Kiran Back To Top

 Excellent description. Thanks much.


Tuesday, July 26, 2016 - 3:24:04 PM - Tim Back To Top


 I have found these APPLY functions to be most beneficial for performing INNER/OUTER JOINS on derived tables.  It appears to delay the Join operation on the right side until the left data set has been built.

SELECT a.Employee, b.ToolItems
           UNION SELECT DISTINCT Employee FROM y
           UNION SELECT DISTINCT Employee FROM z
          ) a

OUTER APPLY (SELECT ToolItems FROM EETools WHERE Employee=a.Employee) b


Thursday, May 12, 2016 - 11:57:04 AM - Michele Wu Back To Top

 Great article.  Simple and clear.  Now I understand how to use APPLY operator.


Wednesday, February 24, 2016 - 10:49:38 PM - Shahim Back To Top

Very nice article and this is a new knowledge for me.

Thursday, February 18, 2016 - 1:48:21 PM - Mister M Back To Top

A good article to present. I would make clear that the cross apply is really a cross product operator. I would explain more what this is.  " The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression" is not the whole story. It servers to function as inner and outer joins by chance. 

Consider that if you only want to see the employees in department 2 , you must do 

SELECT * FROM Department D 

CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(d.departmentID) as f

WHERE d.departmentID = 2


The fololowing will not work because of the actual 'cross product' occuring  ...

SELECT * FROM Department D 

CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(2) as f


So cross product makes , at its core, makes something like this more readable:


from table1 t2, table2 t2


Wednesday, February 17, 2016 - 10:24:25 AM - Carlos B. Vasquez Back To Top


I wll bookmark this web page for future reference. Well-written articles with sample code.


Thursday, December 17, 2015 - 8:57:05 PM - Julian Montoya Back To Top


 Very clear. Excellent explanation. I looked for this operator in the Microsoft page, and I don't understand it......but you my friend, you make it look so easy. Thanks!

Monday, November 16, 2015 - 3:53:10 AM - Stefano Lepre Back To Top

Very clear and helpful, thank a lot!

Friday, October 30, 2015 - 5:18:17 PM - Shiraz Back To Top

how do I access to this database?

Tuesday, July 07, 2015 - 3:00:12 AM - Satish Kumar Back To Top

Thank you Arshad !

I understood the things well. 


Thanks again 



Thursday, July 02, 2015 - 8:34:09 AM - Srinikg Back To Top


Friday, June 12, 2015 - 2:12:55 PM - Georg Back To Top

Great article, thanks!

Up to now, I believed that APPLY can always be written as a JOIN, but now I understand the difference for table-value functions.


Friday, June 05, 2015 - 1:14:12 PM - Steve Holle Back To Top

I use your tip all the time.  It has been invaluable in splitting comments into limited length lines.  I did run into one anomaly though.  When splitting using this line:


*from dstools.dbo.zSplitlines('UN1016, CARBON MONOXIDE, COMPRESSED, 2.3 (2.1) POISON GAS, FLAMMABLE GAS, INHALATION HAZARD, ZONE D',32,' ')

I get:

LineNumber Line

Which is correct.

However, if I use a delim ',' I get this:

LineNumber Line

Missing last character?

Any ideas?

Tuesday, February 03, 2015 - 2:15:28 AM - kumar Back To Top


 I have one scalar function.. So i need to update the column row by row by using the output of the function .I tried to use the Cross apply by using this function. its not working for me.It says invalid object name. even though object is there. Can't we use the scalar function in cross apply ? Any help much appriciated..







  dbo.Product AS p


  fn_inventory(p.ProductID) AS fn

ORDER BY p.ProductID

Friday, September 26, 2014 - 11:07:33 AM - sumesh Back To Top

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting


Hi Arshad Ali,


Very nice article and very useful to me

Friday, August 22, 2014 - 5:16:02 AM - Keimo Back To Top

Hmm, seems that the writer nor the readers have understood the topic. The NO-answer to the critical question is wrong. The answer should be YES which takes the point from the whole article. A JOIN would do the same thing.

Tuesday, July 22, 2014 - 3:51:39 AM - Satyendra Verma Back To Top

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:

Please refre Kim text...same query...:)



Wednesday, July 16, 2014 - 12:16:31 AM - YOGESWARAN RAMANATHAN Back To Top

Hi Arshad,

I was accidently going through your article when i was searching solution for joining table and functions. I was wondering in early 2000 how nice it would be if we are able to join table and function and i am glad to see the same now. i was out of touch with SQL server for  few years. When i was going through your article, i was thinking CROSS APPLY & OUTER APPLY are equal to JOIN & LEFT OUTER JOIN... i could able to see the same comment in your article... also i was thinking of the special purpose which cannot be acheived in JOINs which can be acheived in APPLY and i was able to get clear idea about the same through your excellent examples.






Tuesday, July 15, 2014 - 7:14:25 AM - Alex Back To Top


HI Arshad,


Very nice and detailed explanation..Much appreciated!!


Thank you,

Alex Kuruvilla

Friday, June 27, 2014 - 1:34:45 AM - Nimesh Back To Top

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.



Tuesday, December 10, 2013 - 11:56:08 PM - Muhammad ADIL Back To Top

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

Friday, November 01, 2013 - 11:58:18 AM - Samus Arin Back To Top

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

Wednesday, August 14, 2013 - 12:17:00 AM - Fazarudeen Back To Top

Nice one. Thankyou



Thursday, July 18, 2013 - 8:30:32 PM - MarthaE Back To Top

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.

Tuesday, June 11, 2013 - 4:56:42 AM - JOEL DMELLO Back To Top

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.





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


+ TDSE.FileNameAS TDSEFileName


SalesOrder AS SO2 INNER







AS SQ2 ON SQD2.QuotationId = SQ2.QuotationId INNERJOIN


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


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


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


Thursday, May 30, 2013 - 6:34:29 AM - Sajad Manzoor Back To Top

WOW this article is realy great

Wednesday, May 29, 2013 - 4:35:08 PM - Oliveira Back To Top

Congratulations !!! Very good article !

Sunday, April 28, 2013 - 7:54:37 PM - Prasad Back To Top

Well done ! ... Nice explanation !

Monday, April 15, 2013 - 2:28:44 AM - BHEEMRAJ Back To Top


Wednesday, March 06, 2013 - 7:33:58 PM - glen Back To Top


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


Friday, January 11, 2013 - 6:32:34 AM - jose Back To Top

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.


Tuesday, January 01, 2013 - 10:20:02 PM - Abhijit Back To Top

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 :  



leftouterjoinAccountt2on (t1.AccountName=t2.Name)

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

from (










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





Sunday, November 04, 2012 - 1:43:16 PM - Bill Ross Back To Top

Very nice writeup.

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







etc, like this:


Customer 1

Order Number 1

Order Number 2

Customer 2

Order Number 1

Order Number 2



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?




Wednesday, September 12, 2012 - 7:18:02 AM - Asheej Back To Top

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





Thursday, July 12, 2012 - 2:30:45 AM - Brian Kirton Back To Top

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.



Sunday, April 29, 2012 - 4:35:26 PM - Jan Back To Top

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.



Monday, April 02, 2012 - 7:51:40 AM - Yorgos Back To Top

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

Monday, April 02, 2012 - 7:48:30 AM - Yorgos Back To Top

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





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.



Saturday, February 11, 2012 - 5:05:54 AM - harsh Back To Top

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

nice solution...:-)

Tuesday, February 07, 2012 - 9:05:43 AM - Crew Back To Top

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.

Sunday, January 15, 2012 - 5:43:54 AM - Neelam Back To Top

Excellent article!

Friday, September 02, 2011 - 10:24:50 AM - Kit Back To Top

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.



Wednesday, July 13, 2011 - 10:31:56 AM - Alexander Back To Top

Beautiful overview on the subject. Thank you!

Wednesday, November 17, 2010 - 5:02:11 AM - Sridhar Back To Top

Very good article.

Learn more about SQL Server tools