SQL Server CROSS APPLY and OUTER APPLY: When JOINs Aren't Enough

By Sharon Ben-Moshe · October 6, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Understand CROSS APPLY and OUTER APPLY in SQL Server. Join table-valued functions, select top N rows per group, and unpivot JSON arrays.

Quick answer

In SQL Server, the APPLY operator joins a table to a table-valued function or correlated subquery on a row-by-row basis. CROSS APPLY acts like an INNER JOIN (excluding outer rows where the applied expression produces zero rows), while OUTER APPLY acts like a LEFT OUTER JOIN (preserving outer rows with NULLs when the applied expression produces zero rows).

FAQs

Why can't I use a standard INNER JOIN with a correlated subquery?

Standard SQL JOINs evaluate both relations independently; the right side of a JOIN cannot reference columns from the left table in its WHERE clause. CROSS APPLY explicitly allows the right-side subquery or function to reference columns from the left row.

Can CROSS APPLY select the Top N rows per group?

Yes! This is one of CROSS APPLY's most popular use cases: selecting the top 3 latest orders for every customer in a single query with optimal execution plans.

Does PostgreSQL support CROSS APPLY?

PostgreSQL supports this exact behavior using the `LATERAL` keyword: `CROSS JOIN LATERAL (...)` behaves identically to CROSS APPLY, and `LEFT JOIN LATERAL (...) ON true` behaves like OUTER APPLY.

Is CROSS APPLY faster than a window function?

When backed by an appropriate index on `(partition_col, sort_col)`, CROSS APPLY can leverage an index seek per outer row, outperforming a window function that scans the entire table.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

CROSS APPLY allows right-side subqueries to reference columns from the left-side table.

CROSS APPLY filters out left rows with no matches; OUTER APPLY preserves them with NULLs.

Ideal for Top N per group queries, STRING_SPLIT table-valued functions, and OPENJSON unnesting.

PostgreSQL implements the identical concept using JOIN LATERAL.

Top N per group query with CROSS APPLY

To fetch the two most recent orders for every customer, CROSS APPLY evaluates the subquery for each customer row:

Top 2 orders per customer using CROSS APPLY

SELECT
  c.customer_id,
  c.customer_name,
  recent.order_id,
  recent.order_date,
  recent.order_amount
FROM customers c
CROSS APPLY (
  SELECT TOP 2
    o.order_id,
    o.order_date,
    o.order_amount
  FROM orders o
  WHERE o.customer_id = c.customer_id -- Correlated reference!
  ORDER BY o.order_date DESC
) recent;

Preserving rows with OUTER APPLY

If a customer has placed zero orders, CROSS APPLY excludes them. Switching to OUTER APPLY preserves all customers:

Preserving unmatched rows with OUTER APPLY

SELECT
  c.customer_id,
  c.customer_name,
  recent.order_id,
  recent.order_amount
FROM customers c
OUTER APPLY (
  SELECT TOP 1
    o.order_id,
    o.order_amount
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.order_date DESC
) recent;

Joining with Table-Valued Functions (TVFs) and STRING_SPLIT

When a function returns a table, standard JOIN syntax cannot pass column parameters from the driving row. APPLY solves this seamlessly:

Applying STRING_SPLIT across rows in SQL Server

SELECT
  p.product_id,
  p.product_name,
  s.value AS tag
FROM products p
CROSS APPLY STRING_SPLIT(p.tags_csv, ',') s;

SQL Server CROSS APPLY and OUTER APPLY: When JOINs Aren't Enough

Quick answer

FAQs

Why can't I use a standard INNER JOIN with a correlated subquery?

Can CROSS APPLY select the Top N rows per group?

Does PostgreSQL support CROSS APPLY?

Is CROSS APPLY faster than a window function?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Top N per group query with CROSS APPLY

Preserving rows with OUTER APPLY

Joining with Table-Valued Functions (TVFs) and STRING_SPLIT

Official references

Quick answer

FAQs

Why can't I use a standard INNER JOIN with a correlated subquery?

Can CROSS APPLY select the Top N rows per group?

Does PostgreSQL support CROSS APPLY?

Is CROSS APPLY faster than a window function?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Top N per group query with CROSS APPLY

Preserving rows with OUTER APPLY

Joining with Table-Valued Functions (TVFs) and STRING_SPLIT

Related SQL guides

Official references