When Does CROSS APPLY Outperform INNER JOIN in SQL Queries?

When is CROSS APPLY better than INNER JOIN in SQL queries?

Question:

In a SQL query, when is it more efficient to use CROSS APPLY than INNER JOIN? Although they appear equivalent in many-to-one relationships, examples of the performance benefits of CROSS APPLY are difficult to find.

Answer:

Advantages of CROSS APPLY in complex connections:

CROSS APPLY shows its advantages when the joined table has complex conditions that cannot be easily expressed with INNER JOIN. Consider the following example:

<code class="language-sql">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>

This query retrieves the three most recent records in t2 for each record in t1. This cannot be easily achieved with INNER JOIN.

Performance comparison between CTE and CROSS APPLY:

An alternative to CROSS APPLY is to use a common table expression (CTE) with a window function:

<code class="language-sql">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>

However, this method is less readable and may be less efficient in some cases.

Example:

To illustrate the performance difference, consider the following query:

<code class="language-sql">-- 使用 CTE
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

-- 使用 CROSS APPLY
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>

On a table with approximately 20,000,000 records, the CTE query takes nearly 30 seconds to execute, while the CROSS APPLY query completes immediately.

The above is the detailed content of When Does CROSS APPLY Outperform INNER JOIN in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!