When to Use CROSS APPLY vs. OUTER APPLY in SQL?

Practical application scenarios of CROSS APPLY and OUTER APPLY in SQL

The OUTER APPLY and CROSS APPLY operators in SQL provide flexible data operations and complex query execution. Here are some real-life use cases demonstrating when to use each operator:

CROSS APPLY

• Group query Top N records: CROSS APPLY can efficiently retrieve the top N results in each group. For example:

<code class="language-sql">SELECT pr.name,
       pa.name
FROM sys.procedures pr
OUTER APPLY (SELECT TOP 2 *
                    FROM sys.parameters pa
                    WHERE pa.object_id = pr.object_id
                    ORDER BY pr.name) pa
ORDER BY pr.name,
          pa.name </code>

• Call a table-valued function for each row: CROSS APPLY can call a table-valued function multiple times, with each row of the outer query as input. For example:

<code class="language-sql">SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)</code>

OUTER APPLY

• Reuse column aliases: OUTER APPLY allows reuse of column aliases without creating temporary tables. For example:

<code class="language-sql">SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)  
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)  </code>

• Unpack multiple column groups: OUTER APPLY can unpack multiple groups of columns from a flat table. For example:

<code class="language-sql">CREATE TABLE T
  (
     Id INT PRIMARY KEY,
     Foo1 INT, Bar1 INT,
     Foo2 INT, Bar2 INT,
     Foo3 INT, Bar3 INT
  );</code>

Use VALUES syntax (SQL Server 2008):

<code class="language-sql">SELECT Id,
       Foo,
       Bar,
       GrpName
FROM   T
       CROSS APPLY (VALUES('1', Foo1, Bar1),
                          ('2', Foo2, Bar2),
                          ('3', Foo3, Bar3)) V(GrpName, Foo, Bar); </code>

The above is the detailed content of When to Use CROSS APPLY vs. OUTER APPLY in SQL?. For more information, please follow other related articles on the PHP Chinese website!