SQL Server CROSS APPLY and OUTER APPLY

By:   |   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 a JOIN clause, which allows joining between two table expressions. The key difference between the JOIN and APPLY operators is when you have a table-valued expression on 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 from regular 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 table is an expression or table-valued function. It's like an INNER JOIN since 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 use a 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 is an expression or table-valued function. It's like a LEFT JOIN since rows do not need to exist on the right side to return results from the left table. If you can get the same results with a LEFT OUTER JOIN, don't use an OUTER APPLY for the reason mentioned above.
-- https://www.mssqltips.com

DROP 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 APPLY
SELECT L.Name,
       R.Name
FROM #LeftTable L
    CROSS APPLY
(SELECT Name FROM #RightTable R WHERE R.ReferenceId = L.Id) R;

-- INNER JOIN
SELECT L.Name,
       R.Name
FROM #LeftTable L
    INNER JOIN #RightTable R
        ON R.ReferenceId = L.Id;

-- OUTER APPLY
SELECT L.Name,
       R.Name
FROM #LeftTable L
    OUTER APPLY
(SELECT Name FROM #RightTable R WHERE R.ReferenceId = L.Id) R;

-- LEFT OUTER JOIN
SELECT L.Name,
       R.Name
FROM #LeftTable L
    LEFT OUTER JOIN #RightTable R
        ON R.ReferenceId = L.Id;

-- CROSS APPLY with a Table Expression
SELECT *
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 the right table expression for each row from the left table expression. Similarly, SQL evaluates the left table expression first, and then the right table expression is evaluated 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 expression followed 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 its final 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 helpful to think of a CROSS APPLY as equivalent to an INNER JOIN (or, more precisely, it's like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1.

OUTER APPLY

The OUTER APPLY operator returns all the rows from the left table expression irrespective of whether it matches the expression from the right table. For rows with no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression. The OUTER APPLY is equivalent to a LEFT OUTER JOIN.

If you can achieve the same results with a regular JOIN clause, why and when do you use the APPLY operator? Although you can achieve the same with a regular JOIN, the need for APPLY arises if you have a table-valued expression on the right part. 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 table and then an Employee table. Please note that each employee belongs to a department, giving us referential integrity.

-- https://www.mssqltips.com

USE [tempdb];
GO

DROP TABLE IF EXISTS [Employee];
GO

DROP TABLE IF EXISTS [Department];
GO

CREATE TABLE [Department]
(
    [DepartmentID] [INT] NOT NULL PRIMARY KEY,
    [Name] VARCHAR(250) NOT NULL,
);
GO

CREATE 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),
);
GO

INSERT INTO [Department]
(
    [DepartmentID],
    [Name]
)
VALUES
(1, N'Engineering'),
(2, N'Administration'),
(3, N'Sales'),
(4, N'Marketing'),
(5, N'Finance');
GO

INSERT 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 CROSS APPLY to evaluate the Employee table for each record of the Department table. The second query joins the Department table with the Employee table and returns all matching records. Both queries have the same results.

-- https://www.mssqltips.com

-- Query #1
SELECT *
FROM Department D
    CROSS APPLY
   (SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;
GO

-- Query #2
SELECT *
FROM Department D
    INNER JOIN Employee E
        ON D.DepartmentID = E.DepartmentID;
GO
APPLY and an INNER JOIN

Also, the optimizer picks the same execution plans for both queries, which have an equal query cost, as shown in the screenshot below.

Execution Plan

I'll stick with an INNER JOIN for a simple query that joins tables. Using CROSS APPLY for this job is like using email for file storage. It works, but it's not 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 the code block below, selects data from the Department table. It uses an OUTER APPLY to evaluate the Employee table for each record of the Department table. For those rows for which there is no match in the Employee table, SQL returns NULL, as you can 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 those rows for which there is no match in the Employee table.

-- https://www.mssqltips.com

-- Query #1
SELECT *
FROM Department D
    OUTER APPLY
   (SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;
GO

-- Query #2
SELECT *
FROM Department D
    LEFT OUTER JOIN Employee E
        ON D.DepartmentID = E.DepartmentID;
GO
Results for OUTER APPLY and LEFT JOIN

Even though the above two queries return the same data, the execution plan is different. Cost-wise, there is little difference. The query with the OUTER APPLY uses a Compute Scalar operator before the Nested Loops operator to evaluate and produce the columns of the Employee table.

Execution Plan

The cost difference would not stop me from using OUTER APPLY. But, like the CROSS APPLY, I'll stick to a LEFT OUTER JOIN if all I'm doing is joining two tables.

Joining Table-valued Functions and Tables Using APPLY

We've looked at examples where we prefer a JOIN to an APPLY. Now, let's look at an example where the APPLY operator shines.

I'm creating a table-valued function in the script below that accepts DepartmentID as its parameter. Then, it returns the top two employees, based on salary, who belong to that department. The first query selects data from the Department table and uses a CROSS APPLY to join with the function. It passes the DepartmentID for each row from the outer table expression (in our case, the Department table) and evaluates the function for each row, like a correlated subquery.

The next query uses the OUTER APPLY instead of the CROSS APPLY. Unlike the CROSS APPLY, which returns only correlated data, the OUTER APPLY returns non-correlated data, placing NULLs into the missing columns.

-- https://www.mssqltips.com

CREATE OR ALTER FUNCTION dbo.fn_GetTopEmployeeSalary
(
    @DeptID AS INT
)
RETURNS TABLE
AS
RETURN
     (SELECT TOP 2
             CONCAT(LastName, ', ', FirstName) AS EmployeeName,
             YearlySalary
      FROM Employee E
      WHERE E.DepartmentID = @DeptID
      ORDER BY E.YearlySalary DESC);
GO
SELECT D.Name AS DepartmentName,
       E.EmployeeName,
       E.YearlySalary
FROM Department D
    CROSS APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
GO
SELECT D.Name AS DepartmentName,
       E.EmployeeName,
       E.YearlySalary
FROM Department D
    OUTER APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
GO
Table-valued Function Results

Can you 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 message below.

INNER JOIN error

This error happens because, with JOINs, the execution context of the outer query differs from the execution context of the function (or a derived table). You can't bind a value/variable from the outer query to the function as 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 Dynamic Management Function (DMF).

The script below returns all executing user queries except ones for the current session. As you can see, the sys.dm_exec_requests dynamic management 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;
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.
sys.dm_exec_requests results

Notice for the above query, the [text] column returns all queries submitted in a batch. If you want to see only the active (currently executing) queries, 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

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 than a 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 for sharing your experiences with the APPLY operator.

Next Steps

Last updated by Jared Westover on 2023-12-07



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-12-07

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















get free sql tips
agree to terms