SQL Advanced

SQL CROSS APPLY Explained: Complete Guide with Examples (2026)

A comprehensive guide to SQL CROSS APPLY and OUTER APPLY with practical examples covering table-valued functions, top-N-per-group queries, string splitting, and the PostgreSQL LATERAL JOIN equivalent.

Mar 23, 2026 18 min read

What Is CROSS APPLY in SQL?

CROSS APPLY is a SQL Server operator that evaluates a table expression for each row in the outer query. Think of it as a for-each loop: for every row on the left side, the right side runs once and can reference columns from that left row. Only rows where the right side produces at least one result make it into the output.

This is fundamentally different from a regular JOIN. In a standard JOIN, both sides are evaluated independently and then matched on a condition. With CROSS APPLY, the right side is dependent on the left side. It can use columns from the left table as input parameters.

Here is the simplest way to see it in action:

-- Regular JOIN: both sides are independent
SELECT d.name, e.name
FROM departments d
INNER JOIN employees e ON e.department_id = d.id;

-- CROSS APPLY: right side depends on left side
SELECT d.name, top_emp.name
FROM departments d
CROSS APPLY (
    SELECT TOP 1 e.name
    FROM employees e
    WHERE e.department_id = d.id
    ORDER BY e.salary DESC
) AS top_emp;

The JOIN returns every employee in every department. The CROSS APPLY returns only the highest-paid employee per department. The subquery on the right references d.id from the left side, which is something a regular JOIN cannot do in its ON clause with a TOP/LIMIT expression.

CROSS APPLY was introduced in SQL Server 2005 and has become one of the most powerful tools in the T-SQL arsenal. PostgreSQL offers the same functionality through the LATERAL keyword, which is part of the SQL standard. MySQL 8.0 also supports LATERAL subqueries.

CROSS APPLY vs OUTER APPLY

SQL Server provides two APPLY operators that behave like their JOIN counterparts:

CROSS APPLY = like INNER JOIN. Excludes left rows where the right side returns no results.
OUTER APPLY = like LEFT JOIN. Keeps all left rows, filling right columns with NULL when no results.

-- Sample data
CREATE TABLE customers (
    id    INT PRIMARY KEY,
    name  VARCHAR(50)
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(10,2),
    order_date  DATE
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES
    (1, 1, 250.00, '2026-01-15'),
    (2, 1, 180.00, '2026-02-20'),
    (3, 1, 320.00, '2026-03-05'),
    (4, 2, 450.00, '2026-01-10'),
    (5, 2, 120.00, '2026-03-18');
-- Charlie has no orders

-- CROSS APPLY: Charlie is excluded (no orders)
SELECT c.name, recent.amount, recent.order_date
FROM customers c
CROSS APPLY (
    SELECT TOP 2 o.amount, o.order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
) AS recent;

name	amount	order_date
Alice	320.00	2026-03-05
Alice	180.00	2026-02-20
Bob	120.00	2026-03-18
Bob	450.00	2026-01-10

-- OUTER APPLY: Charlie is included with NULLs
SELECT c.name, recent.amount, recent.order_date
FROM customers c
OUTER APPLY (
    SELECT TOP 2 o.amount, o.order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
) AS recent;

name	amount	order_date
Alice	320.00	2026-03-05
Alice	180.00	2026-02-20
Bob	120.00	2026-03-18
Bob	450.00	2026-01-10
Charlie	NULL	NULL

Rule of thumb: Use CROSS APPLY when you only want rows that have matching results. Use OUTER APPLY when you want to preserve all rows from the left side, even if the right side returns nothing.

CROSS APPLY vs JOIN: When to Use Which

CROSS APPLY and JOIN overlap in some scenarios but each has clear strengths. Here is when to pick one over the other:

Use a regular JOIN when:

You are matching rows between two tables on a simple condition
Both sides are independent and do not need to reference each other during evaluation
You want the optimizer to choose between hash, merge, or nested loop joins freely

Use CROSS APPLY when:

The right side needs to reference columns from the left side (correlated expression)
You are calling a table-valued function for each row
You need the top N rows per group
You are unpivoting columns into rows
You need to split delimited strings per row

-- This JOIN and CROSS APPLY produce identical results:

-- JOIN version
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- CROSS APPLY version (overkill for simple matching)
SELECT c.name, o.amount
FROM customers c
CROSS APPLY (
    SELECT amount FROM orders WHERE customer_id = c.id
) AS o;

-- But only CROSS APPLY can do this: top 1 per group
SELECT c.name, latest.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 1 amount
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
) AS latest;

For the simple case, the regular JOIN is cleaner and the optimizer handles it the same way. For the top-1-per-group pattern, CROSS APPLY is the natural and most readable solution.

CROSS APPLY with Table-Valued Functions

One of the most powerful uses of CROSS APPLY is calling a table-valued function (TVF) for each row. A TVF is a function that returns a table instead of a single value. CROSS APPLY lets you pass columns from the current row as parameters to the function.

-- Create a table-valued function
CREATE FUNCTION dbo.GetTopOrders(@customerId INT, @topN INT)
RETURNS TABLE
AS
RETURN (
    SELECT TOP(@topN) id, amount, order_date
    FROM orders
    WHERE customer_id = @customerId
    ORDER BY amount DESC
);

-- Use CROSS APPLY to call it for each customer
SELECT c.name, o.amount, o.order_date
FROM customers c
CROSS APPLY dbo.GetTopOrders(c.id, 2) AS o;

name	amount	order_date
Alice	320.00	2026-03-05
Alice	250.00	2026-01-15
Bob	450.00	2026-01-10
Bob	120.00	2026-03-18

This is much cleaner than trying to accomplish the same thing with JOINs and window functions. The function encapsulates the logic, and CROSS APPLY provides the per-row execution model.

Inline table-valued functions (iTVFs) like the one above are especially performant because SQL Server can inline them into the main query plan. Multi-statement TVFs should be avoided with CROSS APPLY on large datasets because they execute row by row without inlining.

Top N Per Group Pattern

The top-N-per-group pattern is the most common real-world use of CROSS APPLY. You want the top 1, 2, or 3 rows from a related table for each row in the main table.

-- Top 3 most recent orders per customer
SELECT
    c.id AS customer_id,
    c.name,
    recent.order_id,
    recent.amount,
    recent.order_date
FROM customers c
CROSS APPLY (
    SELECT TOP 3
        o.id AS order_id,
        o.amount,
        o.order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
) AS recent
ORDER BY c.name, recent.order_date DESC;

Compare this to the window function approach:

-- Same result using ROW_NUMBER (more verbose)
WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.order_date DESC
        ) AS rn
    FROM orders o
)
SELECT c.id, c.name, r.id AS order_id, r.amount, r.order_date
FROM customers c
INNER JOIN ranked r ON r.customer_id = c.id
WHERE r.rn <= 3
ORDER BY c.name, r.order_date DESC;

Both approaches work, but CROSS APPLY is often more readable and can be faster because it stops after finding the top N rows per group, while the window function must rank all rows first.

Performance note: CROSS APPLY with TOP benefits greatly from an index on (customer_id, order_date DESC). With this index, SQL Server can seek directly to each customer's orders and stop after the first N rows.

-- Index to support the top-N-per-group pattern
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date DESC)
INCLUDE (amount);

Splitting Delimited Strings with CROSS APPLY

CROSS APPLY combined with STRING_SPLIT (SQL Server 2016+) or a custom splitting function is the standard way to normalize delimited data.

-- Products with comma-separated tags
CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    tags  VARCHAR(500)
);

INSERT INTO products VALUES
    (1, 'Laptop Pro', 'electronics,portable,business'),
    (2, 'Standing Desk', 'furniture,ergonomic,office'),
    (3, 'Wireless Mouse', 'electronics,peripheral');

-- Split tags into individual rows
SELECT p.id, p.name, tag.value AS tag
FROM products p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS tag;

id	name	tag
1	Laptop Pro	electronics
1	Laptop Pro	portable
1	Laptop Pro	business
2	Standing Desk	furniture
2	Standing Desk	ergonomic
2	Standing Desk	office
3	Wireless Mouse	electronics
3	Wireless Mouse	peripheral

This transforms denormalized comma-separated data into proper relational rows. You can then aggregate, filter, or join on individual tags:

-- Count products per tag
SELECT tag.value AS tag, COUNT(*) AS product_count
FROM products p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS tag
GROUP BY tag.value
ORDER BY product_count DESC;

-- Find products with a specific tag
SELECT p.name
FROM products p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS tag
WHERE tag.value = 'electronics';

Unpivoting Columns with CROSS APPLY

CROSS APPLY with VALUES is the most elegant way to unpivot columns into rows in SQL Server. This is useful when your data has metrics stored in separate columns that you need as rows for analysis or charting.

-- Quarterly sales stored as columns
CREATE TABLE quarterly_sales (
    product   VARCHAR(50),
    q1_sales  DECIMAL(10,2),
    q2_sales  DECIMAL(10,2),
    q3_sales  DECIMAL(10,2),
    q4_sales  DECIMAL(10,2)
);

INSERT INTO quarterly_sales VALUES
    ('Widget A', 15000, 18000, 22000, 19000),
    ('Widget B', 8000, 12000, 9000, 14000);

-- Unpivot: columns to rows
SELECT s.product, quarters.quarter, quarters.sales
FROM quarterly_sales s
CROSS APPLY (
    VALUES
        ('Q1', s.q1_sales),
        ('Q2', s.q2_sales),
        ('Q3', s.q3_sales),
        ('Q4', s.q4_sales)
) AS quarters(quarter, sales)
ORDER BY s.product, quarters.quarter;

product	quarter	sales
Widget A	Q1	15000.00
Widget A	Q2	18000.00
Widget A	Q3	22000.00
Widget A	Q4	19000.00
Widget B	Q1	8000.00
Widget B	Q2	12000.00
Widget B	Q3	9000.00
Widget B	Q4	14000.00

This is cleaner than UNPIVOT and more flexible because you can transform column names and data types in the VALUES clause. It also works when columns have different data types, which UNPIVOT does not support.

PostgreSQL Equivalent: LATERAL JOIN

PostgreSQL does not have the CROSS APPLY keyword, but it provides the same functionality through LATERAL subqueries, which are part of the SQL standard.

-- SQL Server: CROSS APPLY
SELECT c.name, recent.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 1 o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
) AS recent;

-- PostgreSQL: LATERAL JOIN (equivalent)
SELECT c.name, recent.amount
FROM customers c
INNER JOIN LATERAL (
    SELECT o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
    LIMIT 1
) AS recent ON true;

-- PostgreSQL: LEFT JOIN LATERAL = OUTER APPLY equivalent
SELECT c.name, recent.amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
    LIMIT 1
) AS recent ON true;

Key differences in the PostgreSQL syntax:

Use INNER JOIN LATERAL instead of CROSS APPLY
Use LEFT JOIN LATERAL instead of OUTER APPLY
Add ON true because PostgreSQL JOINs always require an ON clause
Use LIMIT instead of TOP

MySQL 8.0+ also supports LATERAL subqueries with the same syntax as PostgreSQL. For more database-specific syntax differences, see our MySQL vs PostgreSQL comparison.

Real-World CROSS APPLY Patterns

1. Most recent activity per user

-- Get each user's last login and last purchase
SELECT
    u.id,
    u.email,
    login.last_login,
    purchase.last_purchase,
    purchase.last_amount
FROM users u
OUTER APPLY (
    SELECT TOP 1 login_date AS last_login
    FROM user_logins
    WHERE user_id = u.id
    ORDER BY login_date DESC
) AS login
OUTER APPLY (
    SELECT TOP 1
        order_date AS last_purchase,
        amount AS last_amount
    FROM orders
    WHERE customer_id = u.id
    ORDER BY order_date DESC
) AS purchase;

This pattern is common in dashboards and CRM systems. You can chain multiple OUTER APPLY clauses to pull the latest record from several related tables, all in a single query.

2. JSON array expansion

-- SQL Server: expand JSON arrays
SELECT
    p.id,
    p.name,
    tag.value AS tag
FROM products p
CROSS APPLY OPENJSON(p.tags_json) AS tag
WHERE ISJSON(p.tags_json) = 1;

-- PostgreSQL equivalent
SELECT
    p.id,
    p.name,
    tag.value AS tag
FROM products p,
LATERAL jsonb_array_elements_text(p.tags_json) AS tag(value);

3. Running calculations per row

-- Calculate shipping cost based on weight tiers per order
SELECT
    o.id AS order_id,
    o.total_weight,
    shipping.cost AS shipping_cost
FROM orders o
CROSS APPLY (
    SELECT
        CASE
            WHEN o.total_weight <= 1 THEN 5.99
            WHEN o.total_weight <= 5 THEN 9.99
            WHEN o.total_weight <= 20 THEN 14.99
            ELSE 14.99 + (o.total_weight - 20) * 0.50
        END AS cost
) AS shipping;

Using CROSS APPLY for computed values keeps the main SELECT clean and lets you reference the computed value multiple times without repeating the calculation.

4. Parsing structured strings

-- Extract parts from formatted codes like 'US-CA-90210'
SELECT
    a.address_code,
    parts.country,
    parts.state,
    parts.zip
FROM addresses a
CROSS APPLY (
    SELECT
        PARSENAME(REPLACE(a.address_code, '-', '.'), 3) AS country,
        PARSENAME(REPLACE(a.address_code, '-', '.'), 2) AS state,
        PARSENAME(REPLACE(a.address_code, '-', '.'), 1) AS zip
) AS parts;

Performance Considerations

CROSS APPLY can be fast or slow depending on how you use it. Here are the key factors:

Index support is critical

-- Without index: full table scan per customer (slow)
-- With index: index seek per customer (fast)
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, order_date DESC)
INCLUDE (amount);

-- Now this CROSS APPLY uses an index seek
SELECT c.name, top_order.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 1 amount
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
) AS top_order;

Avoid multi-statement TVFs

-- SLOW: multi-statement TVF (executes as black box)
CREATE FUNCTION dbo.GetOrderStats(@custId INT)
RETURNS @result TABLE (total_orders INT, total_spent DECIMAL)
AS BEGIN
    INSERT @result
    SELECT COUNT(*), SUM(amount)
    FROM orders WHERE customer_id = @custId;
    RETURN;
END;

-- FAST: inline TVF (optimizer can see inside)
CREATE FUNCTION dbo.GetOrderStatsInline(@custId INT)
RETURNS TABLE
AS RETURN (
    SELECT COUNT(*) AS total_orders, SUM(amount) AS total_spent
    FROM orders WHERE customer_id = @custId
);

Inline TVFs are expanded into the main query plan. Multi-statement TVFs are treated as opaque operations that prevent the optimizer from choosing the best plan. Always prefer inline TVFs with CROSS APPLY.

Check the execution plan

-- Always verify with the actual execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT c.name, latest.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 1 amount
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
) AS latest;

-- Look for: Index Seek (good) vs Table Scan (bad)
-- Look for: Nested Loops (expected for APPLY)

CROSS APPLY typically produces a Nested Loops join in the execution plan, which is efficient when the inner side has index support and the outer side has a reasonable number of rows. For very large outer tables with millions of rows, a regular JOIN with window functions may perform better.

Let AI write your CROSS APPLY queries. Describe what you need in plain English, and AI2SQL generates the correct APPLY or LATERAL syntax for your database.

Frequently Asked Questions

What is CROSS APPLY in SQL?

CROSS APPLY is a SQL Server operator that lets you invoke a table-valued function or correlated subquery for each row in the outer table. It works like an INNER JOIN but allows the right side to reference columns from the left side. Only rows that produce results from the right side are included in the output. In PostgreSQL and standard SQL, the equivalent is LATERAL JOIN.

What is the difference between CROSS APPLY and OUTER APPLY?

CROSS APPLY works like INNER JOIN: it only returns rows from the left table where the right side produces at least one result. OUTER APPLY works like LEFT JOIN: it returns all rows from the left table, and if the right side produces no results, the right columns are filled with NULLs. Use CROSS APPLY when you only want matching rows, and OUTER APPLY when you want to keep all rows from the left table.

When should I use CROSS APPLY instead of JOIN?

Use CROSS APPLY when you need the right side to reference columns from the left side (correlated expressions), when calling table-valued functions per row, when selecting the top N rows per group, or when unpivoting columns into rows. For simple equi-joins between two tables, a regular JOIN is cleaner and usually performs the same.

Does PostgreSQL support CROSS APPLY?

PostgreSQL does not use the CROSS APPLY keyword, but it supports the same functionality through LATERAL JOIN, which is part of the SQL standard. You write INNER JOIN LATERAL or LEFT JOIN LATERAL to get the same behavior as SQL Server's CROSS APPLY or OUTER APPLY respectively. The LATERAL keyword allows the subquery to reference columns from preceding tables in the FROM clause.

Can AI generate CROSS APPLY queries from plain English?

Yes. Tools like AI2SQL let you describe what you need in plain English, such as "get the top 3 orders for each customer" or "split comma-separated tags into rows," and the AI generates the correct CROSS APPLY or LATERAL JOIN syntax for your specific database dialect. This is especially useful since APPLY syntax varies between SQL Server and PostgreSQL.