StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhen should I use Cross Apply over Inner Join?
text
Body
copied!<p>What is the main purpose of using <a href="http://technet.microsoft.com/en-us/library/ms175156.aspx" rel="noreferrer">CROSS APPLY</a>?</p> <p>I have read (vaguely, through posts on the Internet) that <code>cross apply</code> can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)</p> <p>I also know that <code>CROSS APPLY</code> <a href="http://www.eggheadcafe.com/conversation.aspx?messageid=34684493&threadid=34673936" rel="noreferrer">doesn't require a UDF as the right-table.</a></p> <p>In most <code>INNER JOIN</code> queries (one-to-many relationships), I could rewrite them to use <code>CROSS APPLY</code>, but they always give me equivalent execution plans. </p> <p>Can anyone give me a good example of when <code>CROSS APPLY</code> makes a difference in those cases where <code>INNER JOIN</code> will work as well?</p> <hr> <p><strong>Edit:</strong></p> <p>Here's a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where <code>cross apply</code> is faster/more efficient)</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload