When should I use CROSS APPLY over INNER JOIN?

Question

What is the main purpose of using CROSS APPLY?

I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)

I also know that CROSS APPLY doesn't require a UDF as the right-table.

In most INNER JOIN queries (one-to-many relationships), I could rewrite them to use CROSS APPLY, but they always give me equivalent execution plans.

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

---

Edit:

Here's a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where cross apply is faster/more efficient)

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId

Bridging

I know this is EVEN PICKIER of me but 'performant' is most definitely a word. It is just not related to efficiency. – Entelechy

It's very useful for sql xquery. check this. – Malayoindonesian

Seems like using "inner loop join" would be very close to cross apply. I wish your example detailed which join hint was equivalent. Just saying join might result in inner/loop/merge or even "other" because it may re-arrange with other joins. – Searchlight

When the join will create a lot of rows but you only need to evaluate one row join at a time. I had a case were I needed a self join on a table with over 100 million rows and there was simple not enough memory. So I went cursor to bring memory footprint down. From cursor I went cross apply as still managed memory footprint and was 1/3 faster than cursor. – Diatonic

In my experience Cross-Apply is faster in most cases (like for reporting), but when exporting records (tens of thousands or more), it will loose out to a hash-join on a derived query. YMMV – Kansu

CROSS APPLY has its obvious usage in allowing a set to depend on another (unlike the JOIN operator), but that doesn't comes without a cost: it behaves like a function that operates over each member of the left set, so, in SQL Server terms it always perform a Loop Join, which almost never is the best way to join sets. So, use APPLY when you need to, but don't overuse it against JOIN. – Shanks

With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with correlations to elements from the left table- Referred from 70-461 Traning Kit – Betsey

Provided link "doesn't require a UDF as the right-table." is no longer working. – Toh

@Entelechy From the dictionary: Performant uk/pəˈfɔː.mənt/ us/pɚˈfɔːr.mənt/ ADJECTIVE (of technology, etc.) working in an effective way We found it the most flexible and performant monitoring software. – Pickpocket

Answer 1

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

See the article in my blog for detailed performance comparison:

• INNER JOIN vs. CROSS APPLY

CROSS APPLY works better on things that have no simple JOIN condition.

This one selects 3 last records from t2 for each record from t1:

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

It cannot be easily formulated with an INNER JOIN condition.

You could probably do something like that using CTE's and window function:

WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3

, but this is less readable and probably less efficient.

Update:

Just checked.

master is a table of about 20,000,000 records with a PRIMARY KEY on id.

This query:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id

runs for almost 30 seconds, while this one:

WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q

is instant.

Basrelief

See the end of Ariel's link. A row_number() query is just as nice and doesn't even require a join. So I don't think I should use cross apply for this situation (select top 3, partition by t1.id). – Bridging

Good example! The raw performance increase is very apparent. How do the execution plans differ? – Bridging

@Jeff: they differ much, as you can see :) JOIN uses NESTED LOOPS with master as a leading table, CROSS APPLY uses NESTED LOOPS too but t is leading and TOP is applied to master on each loop. – Basrelief

Although this is the most popular answer I don't think it answers the actual question "What is the main purpose of using CROSS APPLY?". The main purpose is to enable table functions with parameters to be executed once per row and then joined to the results. – Particularly

@Mike: how do you call a TVF with INNER JOIN? – Basrelief

@Basrelief - the link to your blog post appears down. The post is archived here: web.archive.org/web/20101225210315/http://explainextended.com/… – Telpher

@Particularly Yes, but the OP didn't ask for the main purpose of using CROSS APPLY, he asked for when to choose it over INNER JOIN, when that would work as well. – Technician

So it is essentially a CROSS JOIN which allows the left-handed table to define the right side as well. Would that be a somewhat accurate way to describe it? – Sporogenesis

It might be worth mentioning that this is called a lateral join in standard (ANSI) SQL – Sikang

Doesn't TOP(t.id) require all ids in t to be contiguous to perform efficiently? Also, even if this is the case; if you have 1,000,000 records and you need to get record #1,000,000, won't this CROSS APPLY have to first get 999,999 records before it finds it? – Sunbeam

@MattArnold: I'm not really sure what "efficiently" means in this context. This is a made-up example to illustrate the principle, in real world you don't compare ids to row numbers. If you want to get the "record number 1000000", you first have to define what does "record number 1000000" mean. If it means "entry number 1 million in a rowset sorted by such and such fields", then yes, it would need to count them off, one by one, before it can point to the millionth one. – Basrelief

What I meant by efficiently is that if you need to get the 3rd record but the IDs are randomly generated across the entire INT range then it might be calling TOP(2,147,483,647) just to get record 3 just because it had an ID of 2,147,483,647 instead of 3. My confusion is why TOP and ORDER BY are even being used in this example - what would be wrong with using WHERE instead (e.g. WHERE id = t.id)? – Sunbeam

@MattArnold: this is not a query you would see in the real world. This is a query which illustrates the performance difference between ROW_NUMBER and CROSS APPLY / TOP. Using WHERE instead of TOP would not let me illustrate this difference. – Basrelief

Where did you get the master table that you describe using? I would like to run tests on my own machine. – Escribe

@Vérace open the linked article of mine explainextended.com/2009/07/16/inner-join-vs-cross-apply , there are scripts to create the test tables and the queries against those tables – Basrelief

@Basrelief thank you very much for your answer ! I learned a new thing today (cross apply) and saved me from having to do some very nasty inner joins with RANKS, precisely because I can have multiple matches in my inner join..... I can now simply CROSS APPLY (SELECT TOP 1...) – Localize

Answer 2

Consider you have two tables.

MASTER TABLE

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

DETAILS TABLE

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

There are many situations where we need to replace INNER JOIN with CROSS APPLY.

1. Join two tables based on TOP n results

Consider if we need to select Id and Name from Master and last two dates for each Id from Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

• SQL FIDDLE

The above query generates the following result.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

See, it generated results for last two dates with last two date's Id and then joined these records only in the outer query on Id, which is wrong. This should be returning both Ids 1 and 2 but it returned only 1 because 1 has the last two dates. To accomplish this, we need to use CROSS APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

• SQL FIDDLE

and forms the following result.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

Here's how it works. The query inside CROSS APPLY can reference the outer table, where INNER JOIN cannot do this (it throws compile error). When finding the last two dates, joining is done inside CROSS APPLY i.e., WHERE M.ID=D.ID.

2. When we need INNER JOIN functionality using functions.

CROSS APPLY can be used as a replacement with INNER JOIN when we need to get result from Master table and a function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

And here is the function

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

• SQL FIDDLE

which generated the following result

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

ADDITIONAL ADVANTAGE OF CROSS APPLY

APPLY can be used as a replacement for UNPIVOT. Either CROSS APPLY or OUTER APPLY can be used here, which are interchangeable.

Consider you have the below table(named MYTABLE).

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

The query is below.

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

• SQL FIDDLE

which brings you the result

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x

Spinous

Excellent example with the 2 vs 4 records and helped me understand the context in which this would be needed. – Arnelle

For point 1 where we have 2 rows for ID 1 instead of 4 rows for ID 1, 2. Wouldn't we just use a left join instead. – Msg

@JosephCho: an outer join in example 1 would return ID1 twice with dates and quantities, and ID2 and ID3 once each with NULLs for the date and quantity. – Canonry

Answer 3

cross apply sometimes enables you to do things that you cannot do with inner join.

Example (a syntax error):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

This is a syntax error, because, when used with inner join, table functions can only take variables or constants as parameters. (I.e., the table function parameter cannot depend on another table's column.)

However:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

This is legal.

Edit: Or alternatively, shorter syntax: (by ErikE)

select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id

Edit:

Note: Informix 12.10 xC2+ has Lateral Derived Tables and Postgresql (9.3+) has Lateral Subqueries which can be used to a similar effect.

Bibcock

I think this is the reasoning behind why we have cross apply. If you check out the link below this is the first thing MS says about cross apply. It might have other uses but I think this is the reason it was introduced. Without it table functions would not be usable in a lot of situations. technet.microsoft.com/en-us/library/ms175156.aspx – Particularly

cross apply also produces a nice execution plan when coupled with inline table functions while maintaining much needed modularity. – Bibcock

No SELECT needed inside the CROSS APPLY. Please try CROSS APPLY dbo.myTableFun(O.name) F. – Technician

@Technician sure, you can always use the less flexible syntax to cross apply. I was showing the more generalized version which you can can sometimes use in order to avoid bringing hard to compute columns into the query. – Bibcock

I think the point is: inner join ( select * from dbo.myTableFun(O.name) ) F is legal too. So if you want to emphasis the differences, you'd better use the shorter syntax. – Fizgig

@Fizgig inner join won't work if table function parameter depends on another table's column (aka external reference) in the external select. It will work if table function parameter is a literal or a variable. Cross apply will work in both cases. – Bibcock

Answer 4

It seems to me that CROSS APPLY can fill a certain gap when working with calculated fields in complex/nested queries, and make them simpler and more readable.

Simple example: you have a DoB and you want to present multiple age-related fields that will also rely on other data sources (such as employment), like Age, AgeGroup, AgeAtHiring, MinimumRetirementDate, etc. for use in your end-user application (Excel PivotTables, for example).

Options are limited and rarely elegant:

• JOIN subqueries cannot introduce new values in the dataset based on data in the parent query (it must stand on its own).

• UDFs are neat, but slow as they tend to prevent parallel operations. And being a separate entity can be a good (less code) or a bad (where is the code) thing.

• Junction tables. Sometimes they can work, but soon enough you're joining subqueries with tons of UNIONs. Big mess.

• Create yet another single-purpose view, assuming your calculations don't require data obtained mid-way through your main query.

• Intermediary tables. Yes... that usually works, and often a good option as they can be indexed and fast, but performance can also drop due to to UPDATE statements not being parallel and not allowing to cascade formulas (reuse results) to update several fields within the same statement. And sometimes you'd just prefer to do things in one pass.

• Nesting queries. Yes at any point you can put parenthesis on your entire query and use it as a subquery upon which you can manipulate source data and calculated fields alike. But you can only do this so much before it gets ugly. Very ugly.

• Repeating code. What is the greatest value of 3 long (CASE...ELSE...END) statements? That's gonna be readable!

  • Tell your clients to calculate the damn things themselves.

Did I miss something? Probably, so feel free to comment. But hey, CROSS APPLY is like a godsend in such situations: you just add a simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl and voilà! Your new field is now ready for use practically like it had always been there in your source data.

Values introduced through CROSS APPLY can...

• be used to create one or multiple calculated fields without adding performance, complexity or readability issues to the mix
• like with JOINs, several subsequent CROSS APPLY statements can refer to themselves: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• you can use values introduced by a CROSS APPLY in subsequent JOIN conditions
• As a bonus, there's the Table-valued function aspect

Dang, there's nothing they can't do!

Ataraxia

This is a big +1 from me, as I'm surprised it's not mentioned more often. Perhaps you could extend this example to show how you can perform "procedural" calculations on the chain of derived values? Eg: CROSS APPLY (select crossTbl.value * tbl.multiplier as Multiplied) multiTbl - CROSS APPLY (select multiTbl.Multiplied / tbl.DerivativeRatio as Derived) derivedTbl - etc ... – Lingulate

Any more info/examples on how to use Cross Apply as replacement for CASE..ELSE..END ? – Badalona

@Badalona APPLY can be used to store the result of a case statement (among other things) in order to refer to it. A structure could be something like: SELECT CASE when subquery.intermediateResult > 0 THEN "yes" ELSE "no" END FROM someTable OUTER APPLY (select CASE...END...ELSE as intermediateResult) as subquery. – Ataraxia

Example of calculations with cross apply col.sql.drylib.com – Bloodandthunder

Answer 5

This has already been answered very well technically, but let me give a concrete example of how it's extremely useful:

Lets say you have two tables, Customer and Order. Customers have many Orders.

I want to create a view that gives me details about customers, and the most recent order they've made. With just JOINS, this would require some self-joins and aggregation which isn't pretty. But with Cross Apply, its super easy:

SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T

Lacteous

Best answer I read so far – Massasauga

Using TOP 2 would make this a better example, because it's not that hard to replicate TOP 1 using GROUP BY and MAX, but quite difficult to replicate TOP N for N > 1 – Catalan

Answer 6

Cross apply works well with an XML field as well. If you wish to select node values in combination with other fields.

For example, if you have a table containing some xml

<root>
    <subnode1>
       <some_node value="1" />
       <some_node value="2" />
       <some_node value="3" />
       <some_node value="4" />
    </subnode1>
</root>

Using the query

SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id

Will return a result

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

Stipend

Answer 7

Cross apply can be used to replace subquery's where you need a column of the subquery

subquery

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

here i won't be able to select the columns of company table so, using cross apply

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

Mathian

Answer 8

Here's a brief tutorial that can be saved in a .sql file and executed in SSMS that I wrote for myself to quickly refresh my memory on how CROSS APPLY works and when to use it:

-- Here's the key to understanding CROSS APPLY: despite the totally different name, think of it as being like an advanced 'basic join'.
-- A 'basic join' gives the Cartesian product of the rows in the tables on both sides of the join: all rows on the left joined with all rows on the right.
-- The formal name of this join in SQL is a CROSS JOIN.  You now start to understand why they named the operator CROSS APPLY.

-- Given the following (very) simple tables and data:
CREATE TABLE #TempStrings ([SomeString] [nvarchar](10) NOT NULL);
CREATE TABLE #TempNumbers ([SomeNumber] [int] NOT NULL);
CREATE TABLE #TempNumbers2 ([SomeNumber] [int] NOT NULL);
INSERT INTO #TempStrings VALUES ('111'); INSERT INTO #TempStrings VALUES ('222');
INSERT INTO #TempNumbers VALUES (111); INSERT INTO #TempNumbers VALUES (222);
INSERT INTO #TempNumbers2 VALUES (111); INSERT INTO #TempNumbers2 VALUES (222); INSERT INTO #TempNumbers2 VALUES (222);

-- Basic join is like CROSS APPLY; 2 rows on each side gives us an output of 4 rows, but 2 rows on the left and 0 on the right gives us an output of 0 rows:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
    -- Note: this also works:
    --#TempStrings st CROSS JOIN #TempNumbers nbr

-- Basic join can be used to achieve the functionality of INNER JOIN by first generating all row combinations and then whittling them down with a WHERE clause:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
WHERE
    st.SomeString = nbr.SomeNumber

-- However, for increased readability, the SQL standard introduced the INNER JOIN ... ON syntax for increased clarity; it brings the columns that two tables are
-- being joined on next to the JOIN clause, rather than having them later on in the WHERE clause.  When multiple tables are being joined together, this makes it
-- much easier to read which columns are being joined on which tables; but make no mistake, the following syntax is *semantically identical* to the above syntax:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Inner join
    #TempStrings st INNER JOIN #TempNumbers nbr ON st.SomeString = nbr.SomeNumber

-- Because CROSS APPLY is generally used with a subquery, the subquery's WHERE clause will appear next to the join clause (CROSS APPLY), much like the aforementioned
-- 'ON' keyword appears next to the INNER JOIN clause.  In this sense, then, CROSS APPLY combined with a subquery that has a WHERE clause is like an INNER JOIN with
-- an ON keyword, but more powerful because it can be used with subqueries (or table-valued functions, where said WHERE clause can be hidden inside the function).
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr

-- CROSS APPLY joins in the same way as a CROSS JOIN, but what is joined can be a subquery or table-valued function.  You'll still get 0 rows of output if
-- there are 0 rows on either side, and in this sense it's like an INNER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- OUTER APPLY is like CROSS APPLY, except that if one side of the join has 0 rows, you'll get the values of the side that has rows, with NULL values for
-- the other side's columns.  In this sense it's like a FULL OUTER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st OUTER APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- One thing CROSS APPLY makes it easy to do is to use a subquery where you would usually have to use GROUP BY with aggregate functions in the SELECT list.
-- In the following example, we can get an aggregate of string values from a second table based on matching one of its columns with a value from the first
-- table - something that would have had to be done in the ON clause of the LEFT JOIN - but because we're now using a subquery thanks to CROSS APPLY, we
-- don't need to worry about GROUP BY in the main query and so we don't have to put all the SELECT values inside an aggregate function like MIN().
SELECT
    st.SomeString, nbr.SomeNumbers
FROM
    #TempStrings st CROSS APPLY (SELECT SomeNumbers = STRING_AGG(tempNbr.SomeNumber, ', ') FROM #TempNumbers2 tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr
-- ^ First the subquery is whittled down with the WHERE clause, then the aggregate function is applied with no GROUP BY clause; this means all rows are
--   grouped into one, and the aggregate function aggregates them all, in this case building a comma-delimited string containing their values.

DROP TABLE #TempStrings;
DROP TABLE #TempNumbers;
DROP TABLE #TempNumbers2;

Samadhi

Answer 9

Here is an article that explains it all, with their performance difference and usage over JOINS.

SQL Server CROSS APPLY and OUTER APPLY over JOINS

As suggested in this article, there is no performance difference between them for normal join operations (INNER AND CROSS).

The usage difference arrives when you have to do a query like this:

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)

That is, when you have to relate with function. This cannot be done using INNER JOIN, which would give you the error "The multi-part identifier "D.DepartmentID" could not be bound." Here the value is passed to the function as each row is read. Sounds cool to me. :)

Otic

Answer 10

I guess it should be readability ;)

CROSS APPLY will be somewhat unique for people reading to tell them that a UDF is being used which will be applied to each row from the table on the left.

Ofcourse, there are other limitations where a CROSS APPLY is better used than JOIN which other friends have posted above.

Costrel

Answer 11

The essence of the APPLY operator is to allow correlation between left and right side of the operator in the FROM clause.

In contrast to JOIN, the correlation between inputs is not allowed.

Speaking about correlation in APPLY operator, I mean on the right hand side we can put:

• a derived table - as a correlated subquery with an alias
• a table valued function - a conceptual view with parameters, where the parameter can refer to the left side

Both can return multiple columns and rows.

Materials

Answer 12

While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs.

Stolen from Here

Pinelli

One year later, here I am back reading the same question, and finding my own answer which I frankly don't remember writing! – Pinelli

Answer 13

Well I am not sure if this qualifies as a reason to use Cross Apply versus Inner Join, but this query was answered for me in a Forum Post using Cross Apply, so I am not sure if there is an equalivent method using Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

AS BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp

END

Reprography

Answer 14

This is perhaps an old question, but I still love the power of CROSS APPLY to simplify the re-use of logic and to provide a "chaining" mechanism for results.

I've provided a SQL Fiddle below which shows a simple example of how you can use CROSS APPLY to perform complex logical operations on your data set without things getting at all messy. It's not hard to extrapolate from here more complex calculations.

http://sqlfiddle.com/#!3/23862/2

Lingulate

Answer 15

We use CROSS APPLY to update a table with JSON from another (update request) table -- joins won't work for this as we use OPENJSON, to read the content of the JSON, and OPENJSON is a "table-valued function".

I was going to put a simplified version of one of our UPDATE commands here as a example but, even simplified, it is rather large and overly complex for an example. So this much simplied "sketch" of just part of the command will have to suffice:

SELECT  
       r.UserRequestId,
       j.xxxx AS xxxx,
FROM  RequestTable as r WITH (NOLOCK)
   CROSS APPLY
      OPENJSON(r.JSON, '$.requesttype.recordtype')
      WITH(
             r.userrequestid nvarchar(50) '$.userrequestid',
             j.xxx nvarchar(20) '$.xxx
           )j
       WHERE r.Id > @MaxRequestId
          and ... etc. ....

Rossman