By: Arshad Ali |Updated: 2023-12-07 |Comments (64) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > JOIN Tables
Problem
Microsoft introduced the APPLY operator in SQL Server 2005. It's like aJOIN clause, which allows joining between two table expressions. The key differencebetween the JOIN and APPLY operators is when you have a table-valued expressionon the right side and want to evaluate it for each row from the left table expression.Since they produce similar results, when do you choose an APPLY versus a JOIN?
Solution
In this article, I'll explore how the APPLY operator joins two table expressions.Also, I'll show how it differs fromregular JOINs. Additionally, we'll explore a few use cases. By the end,you'll know when to choose a JOIN or an APPLY operator when crafting scripts.
I've included a summary table and code snippets below for your convenience.
| Operator | Similar | When to Use | When NOT to Use |
|---|---|---|---|
| CROSS APPLY | INNER JOIN | Use a CROSS APPLY when no easy join exists and when the right tableis an expression or table-valued function. It's like an INNER JOINsince rows must exist in both tables/expressions for SQL to return results. | If you can get the same results with an inner join, don't usea CROSS APPLY. It is not clear and leaves people scratching their heads. |
| OUTER APPLY | LEFT JOIN | Use OUTER APPLY when no easy join exists and when the right table isan expression or table-valued function. It's like a LEFT JOIN sincerows do not need to exist on the right side to return results from the lefttable. | If you can get the same results with a LEFT OUTER JOIN, don'tuse an OUTER APPLY for the reason mentioned above. |
-- https://www.mssqltips.comDROP TABLE IF EXISTS #LeftTable;CREATE TABLE #LeftTable( Id INT, Name NVARCHAR(10))INSERT INTO #LeftTable( Id, Name)VALUES(1, 'Red'), (2, 'Green'), (3, 'Blue'), (4, 'Yellow'), (5, 'Purple');DROP TABLE IF EXISTS #RightTable;CREATE TABLE #RightTable( Id INT, ReferenceId INT, Name NVARCHAR(10))INSERT INTO #RightTable( Id, ReferenceId, Name)VALUES(1, 1, 'Dog'), (2, 1, 'Cat'), (3, 2, 'Bird'), (4, 4, 'Horse'), (5, 3, 'Bear'), (6, 1, 'Deer');GO-- CROSS APPLYSELECT L.Name, R.NameFROM #LeftTable L CROSS APPLY(SELECT Name FROM #RightTable R WHERE R.ReferenceId = L.Id) R;-- INNER JOINSELECT L.Name, R.NameFROM #LeftTable L INNER JOIN #RightTable R ON R.ReferenceId = L.Id;-- OUTER APPLYSELECT L.Name, R.NameFROM #LeftTable L OUTER APPLY(SELECT Name FROM #RightTable R WHERE R.ReferenceId = L.Id) R;-- LEFT OUTER JOINSELECT L.Name, R.NameFROM #LeftTable L LEFT OUTER JOIN #RightTable R ON R.ReferenceId = L.Id;-- CROSS APPLY with a Table ExpressionSELECT *FROM #LeftTable L CROSS APPLY( SELECT TOP 2 R.Name FROM #RightTable R WHERE R.ReferenceId = L.Id ORDER BY R.Id DESC) R;
SQL Server APPLY Operator Variants
The APPLY operator allows you to join two table expressions. SQL processes theright table expression for each row from the left table expression. Similarly, SQLevaluates the left table expression first, and then the right table expression isevaluated against each row of the left table expression for the final result set.Finally, our result set contains all the selected columns from the left table expressionfollowed by all the columns of the right table expression.
Let's take a minute and review the two types of APPLY operators:
- CROSS APPLY
- OUTER APPLY
And similarities they have with a JOIN.
CROSS APPLY
The CROSS APPLY operator returns rows from the left table expression (in itsfinal output) if it matches the right table expression. In other words, when matched,the right table expression returns rows for the left expression. It's helpfulto think of a CROSS APPLY as equivalent to an INNER JOIN (or, more precisely, it'slike a CROSSJOIN with a correlatedsub-query) with an implicit join condition of 1=1.
OUTER APPLY
The OUTER APPLY operator returns all the rows from the left table expressionirrespective of whether it matches the expression from the right table. For rowswith no corresponding matches in the right table expression, it contains NULL valuesin columns of the right table expression. The OUTER APPLY is equivalent to a LEFTOUTER JOIN.
If you can achieve the same results with a regular JOIN clause, why and whendo you use the APPLY operator? Although you can achieve the same with a regularJOIN, the need for APPLY arises if you have a table-valued expression on the rightpart. Let me explain with some examples.
Video Example of CROSS APPLY and OUTER APPLY
Build Sample Data
The following script creates two tables. First, it builds a Department tableand then an Employee table. Please note that each employee belongs to a department,giving us referential integrity.
-- https://www.mssqltips.comUSE [tempdb];GODROP TABLE IF EXISTS [Employee];GODROP TABLE IF EXISTS [Department];GOCREATE TABLE [Department]( [DepartmentID] [INT] NOT NULL PRIMARY KEY, [Name] VARCHAR(250) NOT NULL,);GOCREATE TABLE [Employee]( [EmployeeID] [INT] NOT NULL PRIMARY KEY, [FirstName] VARCHAR(250) NOT NULL, [LastName] VARCHAR(250) NOT NULL, [HireDate] DATE NOT NULL, [YearlySalary] DECIMAL(16, 2) NOT NULL, [DepartmentID] [INT] NOT NULL REFERENCES [Department] (DepartmentID),);GOINSERT INTO [Department]( [DepartmentID], [Name])VALUES(1, N'Engineering'),(2, N'Administration'),(3, N'Sales'),(4, N'Marketing'),(5, N'Finance');GOINSERT INTO [Employee]( [EmployeeID], [FirstName], [LastName], [HireDate], [YearlySalary], [DepartmentID])VALUES(1, N'Orlando', N'Gee', '01-01-2023', 60000.00, 1),(2, N'Keith', N'Harris', '01-21-2023', 60000.00, 2),(3, N'Donna', N'Carreras', '03-01-2021', 82000.00, 3),(4, N'Janet', N'Gates', '04-01-2022', 90000.00, 3),(5, N'Bill', N'North', '07-01-2000', 85000.00, 1),(6, N'Sally', N'Smith', '07-01-2000', 88000.00, 1);GO
SQL Server CROSS APPLY vs INNER JOIN
The first query below selects data from the Department table and uses a CROSSAPPLY to evaluate the Employee table for each record of the Department table. Thesecond query joins the Department table with the Employee table and returns allmatching records. Both queries have the same results.
-- https://www.mssqltips.com-- Query #1SELECT *FROM Department D CROSS APPLY (SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;GO-- Query #2SELECT *FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID;GO
Also, the optimizer picks the same execution plans for both queries, which havean equal query cost, as shown in the screenshot below.
I'll stick with an INNER JOIN for a simple query that joins tables. UsingCROSS APPLY for this job is like using email for file storage. It works, but it'snot what anyone expects when reviewing the query.
SQL Server OUTER APPLY vs LEFT OUTER JOIN
Let's take a minute and look at another example. The first query in thecode block below, selects data from the Department table. It uses an OUTER APPLYto evaluate the Employee table for each record of the Department table. For thoserows for which there is no match in the Employee table, SQL returns NULL, as youcan see in the screenshots below.
The second query uses a LEFT OUTER JOIN between the Department and Employee tables.As expected, the query returns all rows from the Department table, even for thoserows for which there is no match in the Employee table.
-- https://www.mssqltips.com-- Query #1SELECT *FROM Department D OUTER APPLY (SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;GO-- Query #2SELECT *FROM Department D LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID;GO
Even though the above two queries return the same data, the execution plan isdifferent. Cost-wise, there is little difference. The query with the OUTER APPLYuses a Compute Scalar operator before the Nested Loops operator to evaluate andproduce the columns of the Employee table.
The cost difference would not stop me from using OUTER APPLY. But, like the CROSSAPPLY, I'll stick to a LEFT OUTER JOIN if all I'm doing is joining twotables.
Joining Table-valued Functions and Tables Using APPLY
We've looked at examples where we prefer a JOIN to an APPLY. Now, let'slook at an example where the APPLY operator shines.
I'm creating a table-valued function in the script below that accepts DepartmentIDas its parameter. Then, it returns the top two employees, based on salary, who belongto that department. The first query selects data from the Department table and usesa CROSS APPLY to join with the function. It passes the DepartmentID for each rowfrom the outer table expression (in our case, the Department table) and evaluatesthe function for each row, like a correlatedsubquery.
The next query uses the OUTER APPLY instead of the CROSS APPLY. Unlike the CROSSAPPLY, which returns only correlated data, the OUTER APPLY returns non-correlateddata, placing NULLs into the missing columns.
-- https://www.mssqltips.comCREATE OR ALTER FUNCTION dbo.fn_GetTopEmployeeSalary( @DeptID AS INT)RETURNS TABLEASRETURN (SELECT TOP 2 CONCAT(LastName, ', ', FirstName) AS EmployeeName, YearlySalary FROM Employee E WHERE E.DepartmentID = @DeptID ORDER BY E.YearlySalary DESC);GOSELECT D.Name AS DepartmentName, E.EmployeeName, E.YearlySalaryFROM Department D CROSS APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;GOSELECT D.Name AS DepartmentName, E.EmployeeName, E.YearlySalaryFROM Department D OUTER APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;GO
Can you use a simplejoin in place of the above queries? The answer is no. If you replace theCROSS/OUTER APPLY in the above queries with an INNER JOIN/LEFT OUTER JOIN, specifyingthe ON clause with 1=1, and run the query, you will get the error message below.
This error happens because, with JOINs, the execution context of the outer querydiffers from the execution context of the function (or a derivedtable). You can't bind a value/variable from the outer query to the functionas a parameter. You need the APPLY operator for such queries.
In short, queries need the APPLY operator when using a table-valued function,but you can only use it with inline SELECT statements.
Joining Table-valued System Functions and Tables Using APPLY
Let me show you another query using a DynamicManagement Function (DMF).
The script below returns all executing user queries except ones for the currentsession. As you can see, the sys.dm_exec_requests dynamicmanagement view (DMV) is CROSS APPLY'ed with the sys.dm_exec_sql_text (DMF),which accepts a "plan handle" for the query. Finally, the "plan handle"is passed from the left/outer expression to the function to return results.
USE master;GOSELECT 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) stWHERE 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.
Notice for the above query, the [text] column returns all queries submitted ina batch. If you want to see only the active (currently executing) queries, you canuse the statement_start_offset and statement_end_offset columns to trim the activepart of the query. Refer to this tip:How to isolate the current running commands in SQL Server for a good example.
Other Notes
Remember, the APPLY operator is not an ANSI operator but an extension of T-SQL.Consider this if you plan to port your database to some other DBMS.
Also, a query with an APPLY operator performs better in specific scenarios thana query with regular joins. Here are two articles that discuss this effect in detail.
Finally, I invite you to review the slew of comments below. Thank you all forsharing your experiences with the APPLY operator.
Next Steps
- Jared Westover compares the performance of using an APPLY operator or theROW_NUMBER() function in the article, Return TOP (N) Rows using APPLY or ROW_NUMBER() in SQL Server.
- Aaron Bertrand wrote a short article, Advanced JSON Techniques in SQL Server - Part 3, on reading JSON using CROSSAPPLY and the OPENJSON function.
- In the article, Convert OUTER APPLY and CROSS APPLY Joins for Snowflake using LEFT JOINS,Sergey Gigoyan explores alternatives to APPLY in Snowflake.
Last updated by Jared Westover on 2023-12-07
About the author
Comments For This Article
| Wednesday, May 1, 2024 - 2:06:38 PM - X | Back To Top (92209) |
| I've only used CROSS APPLY when extracting individual fields from a JSON string stored in one of the database columns in the table. SELECT extractedJsonAddress.StreetName, extractedJsonAddress.CityName FROM CstomersTable c CROSS APPLY OPENJSON(c.CustomerAddressJsonText) ( StreetName Nvarchar(250) '$.Address.StreetName', CityName Nvarchar(250) '$.Address.CityName' ) extractedJsonAddress | |
| Monday, December 18, 2023 - 3:16:07 AM - Sree | Back To Top (91804) |
| Nice explaination | |
| 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 SELECT * FROM Department D | |
| 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.* 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 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, | |
| Friday, October 28, 2016 - 12:24:00 AM - sharad | Back To Top (43647) |
Marvelous! Really anice 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 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 thisline: select *from dstools.dbo.zSplitlines('UN1016, CARBON MONOXIDE, COMPRESSED, 2.3 (2.1) POISON GAS, FLAMMABLE GAS, INHALATION HAZARD, ZONE D',32,' ') I get: LineNumberLine Which is correct. However, if I use a delim ',' I get this: LineNumberLine 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 thatthe 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 | |
| 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 showa 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 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. | |
