SQL Server CROSS APPLY and OUTER APPLY

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


   Free MSSQLTips whitepaper - "Understanding Windows Server Cluster Quorum Options"

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.

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

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 
 

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.

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 
 

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights








About the author
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




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

Thursday, June 17, 2021 - 8:58:43 PM - SirishBack 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 FordBack 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 BarrosBack 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 MooreBack 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 PattersonBack 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 - PawanBack 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 ShrivastavBack To Top(75296)

 Thanks sir, more n more updates u provide me.

 


Tuesday, December 12, 2017 - 2:41:49 PM - Kyle JohnsonBack 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 chavanBack To Top(70021)

 

 Great explanation sir.

 


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

Excellent explained !!!!  

 


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

Great tip!  Thanks for providing this demo and explanation!

 


Tuesday, February 28, 2017 - 3:51:24 AM - Thomas FranzBack 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 - sharadBack 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 - KiranBack To Top(43482)

 Excellent description. Thanks much.

 


Tuesday, July 26, 2016 - 3:24:04 PM - TimBack 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 WuBack To Top(41472)

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

 


Wednesday, February 24, 2016 - 10:49:38 PM - ShahimBack 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. VasquezBack 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 MontoyaBack 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 LepreBack To Top(39076)

Very clear and helpful, thank a lot!


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

how do I access to this database?


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

Thank you Arshad !

I understood the things well. 

 

Thanks again 

Satish

 


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

thanks


Friday, June 12, 2015 - 2:12:55 PM - GeorgBack 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 HolleBack 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 - kumarBack 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 - sumeshBack 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 - KeimoBack 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 VermaBack 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 RAMANATHANBack 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 - AlexBack To Top(32706)

 

HI Arshad,

 

Very nice and detailed explanation..Much appreciated!!

 

Thank you,

Alex Kuruvilla


Friday, June 27, 2014 - 1:34:45 AM - NimeshBack 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 ADILBack To Top(27756)

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



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

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


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

Nice one. Thankyou

--Fazarudeen

919841807303


Thursday, July 18, 2013 - 8:30:32 PM - MarthaEBack 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 DMELLOBack 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 ManzoorBack To Top(25196)

WOW this article is realy great


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

Congratulations !!! Very good article !


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

Well done ! ... Nice explanation !


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

VERY GOOD ARTICLE.TOTALLY UNDERSTANDABLE.


Wednesday, March 6, 2013 - 7:33:58 PM - glenBack 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 - joseBack 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 - AbhijitBack 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 RossBack 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 - AsheejBack 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 KirtonBack 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 - JanBack 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 - YorgosBack 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 - YorgosBack 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 - harshBack To Top(15991)

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

nice solution...:-)


Tuesday, February 7, 2012 - 9:05:43 AM - CrewBack 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 - NeelamBack To Top(15655)

Excellent article!


Friday, September 2, 2011 - 10:24:50 AM - KitBack 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 - AlexanderBack To Top(14167)

Beautiful overview on the subject. Thank you!


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

Very good article.