SQL Server CROSS APPLY and OUTER APPLY

By:   |   Updated: 2021-06-22   |   Comments (62)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > JOIN Tables


Problem

Microsoft SQL Server 2005 introduced the APPLY operator, which is like a join clause and it allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between the 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 the APPLY operator, how it differs from regular JOINs and some uses.

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 the right table expression is evaluated against each row of the left table expression for the final result set. The final result set contains all the selected columns from the left table expression followed by all the columns of the right table expression.

SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY

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

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

Create Sample Data for CROSS APPLY and OUTER APPLY examples

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

--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 (1, N'Engineering') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (2, N'Administration') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (3, N'Sales') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (4, N'Marketing') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (5, N'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 (1, N'Orlando', N'Gee', 1 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (2, N'Keith', N'Harris', 2 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (3, N'Donna', N'Carreras', 3 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (4, N'Janet', N'Gates', 3 ) 

SQL Server CROSS APPLY vs INNER JOIN example

The first query in Script #2 selects data from the Department table and uses a CROSS APPLY to evaluate the Employee table for each record of the Department table. The second query simply joins the Department table with the Employee table and all 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 

If you look at the results, you can see see they are the same.

cross apply and inner join result set

Also, the execution plans for these queries are similar and they have an equal query cost, as you can see in the image below.

cross apply and inner join query plan

So, why use the APPLY operator? How does it differ from a JOIN and how does it help in writing more efficient queries? I will discuss this later.

SQL Server OUTER APPLY vs LEFT OUTER JOIN example

The first query in Script #3 selects data from Department table and uses an 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 the Employee table, those rows contain NULL values as you can see in case of row 5 and 6 below. 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 (with estimated operator cost of 0.0000103 or around 0%) before the Nested Loops operator to evaluate and produce the columns of the Employee table.

outer apply and left outer join query plan

Joining table valued functions and tables using APPLY operators

 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 the Department table and uses a 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 the CROSS APPLY and hence unlike the 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

You might be wondering if we can use a simple join in place of the above queries, the answer is NO. If you replace the CROSS/OUTER APPLY in the above queries with an INNER JOIN/LEFT OUTER JOIN, specifying the ON clause with 1=1 and run the query, you will get the error "The multi-part identifier "D.DepartmentID" could not be bound.". This is because with JOINs the execution context of the outer query is different from the execution context of the function (or a derived table), and you cannot 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 a table-valued function in the query, but it can also be used with inline SELECT statements.

Joining table valued system functions and tables using APPLY operators

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 in 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 "plan handle" is being passed from the left/outer expression to the function to return the data.

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

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

Note, for the above query, the [text] column in the query returns all queries submitted in a batch. If you want to see only the active (currently executing) query you can use the statement_start_offset and statement_end_offset columns to trim the active part of the query. Refer to this tip How to isolate the current running commands in SQL Server for a good example.

Other Notes

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

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

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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


Article Last Updated: 2021-06-22

Comments For This Article




Thursday, March 2, 2023 - 12:08:38 AM - Subash Back To Top (90968)
Nice Explanation..!!!

Friday, January 20, 2023 - 8:57:06 AM - steve gray Back To Top (90837)
Nicely done, well explained.

Tuesday, October 18, 2022 - 5:20:58 AM - AndresDwhTester Back To Top (90613)
I see comments as to why to use this instead of the standard JOIN statements.
Well, I'm using some complex queries and this outer/cross apply boosts the performance significantly.
Great article.

Monday, June 20, 2022 - 5:15:08 AM - Alex Back To Top (90170)
Hi,
I don't get what is the point of using table-valued function? As you can just simply use below to select:

SELECT * FROM Department D
INNER JOIN Employee E ON E.DepartmentID = D.DepartmentID

SELECT * FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID


Monday, August 9, 2021 - 7:23:30 AM - Iki Singh Back To Top (89105)
Brilliant explanation!

Thursday, June 17, 2021 - 8:58:43 PM - Sirish Back To Top (88872)
This is an excellent post!! I just recently used a Outer apply to parse through rows that have a long text string to extract numeric only values and with the help of a split function to convert 1 string into dynamic( number of numeric occurrences) number of rows

Tuesday, May 11, 2021 - 7:50:27 PM - Scott Ford Back To Top (88671)
Hi,
Not sure why we say a normal join could not produce the results. The following works just fine:

select department.*, employee.*
from department, employee
where department.departmentid = employee.departmentid

No need for function or apply to be used in this case.

Saturday, November 24, 2018 - 10:57:24 AM - Antonio Barros Back To Top (78317)

 Hello!.

Furst, I whant to thank you  for all these tips that are very hepfull to all those who are starting or that are developing with SQl Server.

I have a problem that I'm trying to solve, but after very long hours of trying, I couldn't found the solution. I have one table with four columns: FisrtName, MiddleName, LastName and Localidade. I whant to see and list all the columns where the name in the three first columns exist in some name of Localidade.

Example of two rows:

António, NULL, Trízio

NULL, NULL, NULL, Trízio.

As we can see, the name "Trízio" exists as LastName and as Localidade.

Can you help me? Thank you very much.

A. Barros


Sunday, August 19, 2018 - 11:28:06 PM - James Moore Back To Top (77240)

I think you’ve missed one of the best uses for cross apply…

select c.newcalc
from table_a a
left join table_b on a.coln=b.coln
cross apply (select a.somecolumn+b.somecolum ‘newcalc’) c /*enter any statement in here (i.e case statements)*/

THIS IS REALLY POWERFUL, as say if you need to reference the column ‘newcalc’ multiple times, you don’t need to repeat anything. Such a simple way too, and it just works


Thursday, August 2, 2018 - 1:09:15 PM - Jay Patterson Back To Top (76951)

Great post!  A little recommendation.  In Script #5, for better clarity and to remove the guessing of which table the columns in the WHERE clause belong to, it's best to use the table aliases you used within the FROM clause.  Thanks!


Monday, June 25, 2018 - 4:38:44 AM - Pawan Back To Top (76369)

So enabling using table valued functions in the query is the only thing for which CROSS and OUTER APPLY's are developed?

Or do they have any other uses also that are not achieved by the joins?


Sunday, February 25, 2018 - 2:21:05 PM - Prakash Shrivastav Back To Top (75296)

 Thanks sir, more n more updates u provide me.

 


Tuesday, December 12, 2017 - 2:41:49 PM - Kyle Johnson Back To Top (73935)

 

 This is exactly what I was look for! You are a great writer. CROSS APPLY - Very simply explained. Thank you


Monday, November 20, 2017 - 1:59:52 PM - Mahesh chavan Back To Top (70021)

 

 Great explanation sir.

 


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

Excellent explained !!!!  

 


Tuesday, August 8, 2017 - 1:27:02 PM - Ted Higgins Back To Top (64329)

Great tip!  Thanks for providing this demo and explanation!

 


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

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.

Example:

 

SELECT calc1.val1,

       ROW_NUMBER() OVER (PARTITION BY calc1.val1 ORDER BY calc5.price_gross) pos_number,
       calc1.price1,
       calc2.price2,
       calc3.price3,
       calc4.price4,
       calc5.price_gross
  FROM tbl t
 CROSS APPLY (SELECT CASE t.col1 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END val1,
                    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 (43647)

Marvelous!

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


Monday, October 3, 2016 - 10:51:20 AM - Kiran Back To Top (43482)

 Excellent description. Thanks much.

 


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

 

 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
FROM  (SELECT DISTINCT Employee FROM x 
           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 (41472)

 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 (40779)

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


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

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:

SELECT *

from table1 t2, table2 t2

 


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

 

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 (40273)

 

 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 (39076)

Very clear and helpful, thank a lot!


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

how do I access to this database?


Tuesday, July 7, 2015 - 3:00:12 AM - Satish Kumar Back To Top (38139)

Thank you Arshad !

I understood the things well. 

 

Thanks again 

Satish

 


Thursday, July 2, 2015 - 8:34:09 AM - Srinikg Back To Top (38108)

thanks


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

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 5, 2015 - 1:14:12 PM - Steve Holle Back To Top (37757)

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:

select

*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
1 UN1016, CARBON MONOXIDE,
2 COMPRESSED, 2.3 (2.1) POISON
3 GAS, FLAMMABLE GAS, INHALATION
4 HAZARD, ZONE D

Which is correct.

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

LineNumber Line
1 UN1016, CARBON MONOXIDE,
2 COMPRESSED, 2.3 (2.1) POISON
3 GAS, FLAMMABLE GAS, INHALATION
4 HAZARD, ZONE

Missing last character?

Any ideas?


Tuesday, February 3, 2015 - 2:15:28 AM - kumar Back To Top (36140)

Hi,

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

 

SELECT

  p.ProductID,

  p.Name,

  fn.Quantity

FROM

  dbo.Product AS p

CROSS APPLY

  fn_inventory(p.ProductID) AS fn

ORDER BY p.ProductID


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

*** 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 (34234)

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 (32817)

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 (32725)

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.

THANKS FOR THE EXCELLENT ARTICLE!!!!!!!!!!!!!

-YOGESWARAN RAMANATHAN

 

 

 


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

 

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 (32429)

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,

Nimesh


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

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



Friday, November 1, 2013 - 11:58:18 AM - Samus Arin Back To Top (27364)

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


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

Nice one. Thankyou

--Fazarudeen

919841807303


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

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 (25387)

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, May 30, 2013 - 6:34:29 AM - Sajad Manzoor Back To Top (25196)

WOW this article is realy great


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

Congratulations !!! Very good article !


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

Well done ! ... Nice explanation !


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

VERY GOOD ARTICLE.TOTALLY UNDERSTANDABLE.


Wednesday, March 6, 2013 - 7:33:58 PM - glen Back To Top (22613)

 

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 (21397)

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 1, 2013 - 10:20:02 PM - Abhijit Back To Top (21221)

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

 

 


Sunday, November 4, 2012 - 1:43:16 PM - Bill Ross Back To Top (20209)

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!

 


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

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

 


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

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


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

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


Monday, April 2, 2012 - 7:51:40 AM - Yorgos Back To Top (16732)

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 2, 2012 - 7:48:30 AM - Yorgos Back To Top (16730)

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.

 

 


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

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

nice solution...:-)


Tuesday, February 7, 2012 - 9:05:43 AM - Crew Back To Top (15922)

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 (15655)

Excellent article!


Friday, September 2, 2011 - 10:24:50 AM - Kit Back To Top (14588)

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 (14167)

Beautiful overview on the subject. Thank you!


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

Very good article.