StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?</p> </blockquote> <p>See the article in my blog for detailed performance comparison:</p> <ul> <li><a href="http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/" rel="noreferrer"><strong><code>INNER JOIN</code> vs. <code>CROSS APPLY</code></strong></a></li> </ul> <p><code>CROSS APPLY</code> works better on things that have no simple <code>JOIN</code> condition.</p> <p>This one selects <code>3</code> last records from <code>t2</code> for each record from <code>t1</code>:</p> <pre><code>SELECT t1.*, t2o.* FROM t1 CROSS APPLY ( SELECT TOP 3 * FROM t2 WHERE t2.t1_id = t1.id ORDER BY t2.rank DESC ) t2o </code></pre> <p>It cannot be easily formulated with an <code>INNER JOIN</code> condition.</p> <p>You could probably do something like that using <code>CTE</code>'s and window function:</p> <pre><code>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 </code></pre> <p>, but this is less readable and probably less efficient.</p> <p><strong>Update:</strong></p> <p>Just checked.</p> <p><code>master</code> is a table of about <code>20,000,000</code> records with a <code>PRIMARY KEY</code> on <code>id</code>.</p> <p>This query:</p> <pre><code>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 </code></pre> <p>runs for almost <code>30</code> seconds, while this one:</p> <pre><code>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 </code></pre> <p>is instant.</p>

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