How Can OUTER and CROSS APPLY Enhance SQL Query Flexibility and Performance?

Practical application of OUTER and CROSS APPLY in SQL

OUTER and CROSS APPLY are two very valuable structures in SQL that can enhance query flexibility and performance. While the classic examples may seem esoteric, these structures have practical applications in a variety of scenarios.

1. Efficiently obtain relevant data

Suppose you have two tables: the Contacts table contains contact details and the CommunicationEntries table contains communication channels (phone, fax, email). To retrieve contact information and related communication details, you can use CROSS APPLY as follows:

SELECT c.name, ce.communicationType, ce.channel
FROM Contacts c
CROSS APPLY (
    SELECT *
    FROM CommunicationEntries ce
    WHERE ce.contactId = c.contactId
) ce;

2. Use table-valued functions

Table-valued functions (TVF) provide a way to dynamically generate tabular data. CROSS APPLY allows you to call the TVF for each row in the outer query. For example, to display the query plan for a running query:

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);

3. Reuse column aliases

Sometimes you need to reuse column aliases within a complex query. CROSS APPLY allows you to set multiple aliases for the same column, making queries easier to read and manage. For example:

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

4. Efficiently unwind data

In a normalized table structure, data is stored in columns. Unwinding flattens this data, creating rows for each attribute. OUTER APPLY can perform unwinding efficiently, especially when multiple sets of columns need to be unwound. Consider the following scenario:

SELECT Id, Foo, Bar, GrpName
FROM T
CROSS APPLY (VALUES('1', Foo1, Bar1),
                    ('2', Foo2, Bar2),
                    ('3', Foo3, Bar3)) V(GrpName, Foo, Bar);

The above is the detailed content of How Can OUTER and CROSS APPLY Enhance SQL Query Flexibility and Performance?. For more information, please follow other related articles on the PHP Chinese website!