When and How to Use OUTER APPLY in SQL: Real-World Examples?

Practical application example of OUTER APPLY in SQL

OUTER APPLY is a powerful operator in SQL that allows you to associate rows in one table with rows in another table. Although it looks similar to INNER JOIN, it provides unique functionality in specific situations.

Use cases for OUTER APPLY

Here are some real-life examples of OUTER APPLY proving its worth:

Query the first N rows by group:

Example: Retrieve the first two parameters of each stored procedure.

<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 table-valued function:

Example: Execute a table-valued function for each row in the outer query.

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

Reuse column alias:

Example: Reuse column aliases to perform multiple calculations.

<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>

Reverse multiple sets of columns:

Example: Reverse data from a table with denormalized structure.

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

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 and How to Use OUTER APPLY in SQL: Real-World Examples?. For more information, please follow other related articles on the PHP Chinese website!