Snowflake Cross Apply

Introduction

When you’re writing complex join logic in SQL Server, the CROSS APPLY operator gives you more flexibility than standard joins. If you’re using Snowflake, you may notice it lacks native support for CROSS APPLY. However, Snowflake does offer a compatible alternative.

As more teams migrate workloads to Snowflake, understanding how to translate legacy SQL Server constructs like CROSS APPLY becomes essential. These patterns usually power row-level transformations, dynamic filtering, and custom aggregations—critical for analytics and reporting pipelines. Knowing the Snowflake equivalent ensures smooth migrations and performant query design in modern cloud environments.

This article shows how Snowflake Cross Apply behavior is achieved using LATERAL JOIN. You’ll also learn how it compares to SQL Server syntax and how to write performant, row-aware queries in Snowflake.

What Is CROSS APPLY?

In SQL Server, CROSS APPLY lets you join a table to a table-valued function. SQL Server evaluates the function for each row and merges the result with the outer row dynamically.

-- Using CROSS APPLY
SELECT *
FROM Person p
CROSS APPLY (
  SELECT *
  FROM Company c
  WHERE p.companyid = c.companyId
) Czip;

In many cases, you can replicate the logic with a regular INNER JOIN:

-- Equivalent INNER JOIN
SELECT *
FROM Person p
INNER JOIN Company c ON p.companyid = c.companyId;

Snowflake Cross Apply Equivalent

Although Snowflake doesn’t support CROSS APPLY directly, you can use LATERAL JOIN to achieve the same effect. This ANSI-standard syntax allows the subquery to reference outer table columns row by row.

-- Using LATERAL JOIN in Snowflake
SELECT *
FROM Person p
LEFT JOIN LATERAL (
  SELECT *
  FROM Company c
  WHERE p.companyid = c.companyId
) Czip ON TRUE;

If you want behavior closer to CROSS APPLY, switch to INNER JOIN LATERAL.

When to Use CROSS APPLY or LATERAL JOIN

Not every join requires CROSS APPLY or LATERAL JOIN. These constructs shine when the joined subquery depends on values from the outer row. In other words, when the right-hand side needs to be evaluated per row of the left-hand table.

Typical use cases include:

Invoking table-valued functions that rely on outer row values
Applying dynamic filtering or row-level transformations
Embedding correlated aggregations or conditional logic

For example, if you want to join each user to their latest activity or calculate a metric based on row-specific conditions, a lateral join avoids messy workarounds. It’s more expressive than traditional joins and cleaner than subqueries in the SELECT clause.

Execution Plan Behavior

Both SQL Server and Snowflake process these queries row-by-row:

The engine scans the main table sequentially.
It executes the subquery for each row.
The system joins the matching results immediately.

One key difference: SQL Server defaults to inner semantics with CROSS APPLY. Meanwhile, Snowflake uses LEFT JOIN LATERAL in most examples to preserve unmatched outer rows.

Example: Orders and OrderItems

Let’s say you want to calculate order totals by combining two tables:

-- Orders
CREATE TABLE Orders (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE
);

-- OrderItems
CREATE TABLE OrderItems (
  OrderID INT,
  ItemID INT,
  Quantity INT,
  Price DECIMAL(10, 2)
);

-- Sample data
INSERT INTO Orders VALUES
(1, 101, '2023-05-01'),
(2, 102, '2023-05-02'),
(3, 101, '2023-05-03');

INSERT INTO OrderItems VALUES
(1, 1, 2, 10.00),
(1, 2, 1, 15.00),
(2, 1, 3, 10.00),
(2, 3, 2, 20.00),
(3, 2, 1, 15.00);

Now use LATERAL JOIN in Snowflake to compute totals:

SELECT
  o.OrderID,
  o.CustomerID,
  o.OrderDate,
  oi.TotalAmount
FROM Orders o
LEFT JOIN LATERAL (
  SELECT
    OrderID,
    SUM(Quantity * Price) AS TotalAmount
  FROM OrderItems
  WHERE OrderID = o.OrderID
  GROUP BY OrderID
) oi ON TRUE;

Expected output:

OrderID | CustomerID | OrderDate   | TotalAmount
--------+------------+-------------+-------------
1       | 101        | 2023-05-01  | 35.00
2       | 102        | 2023-05-02  | 70.00
3       | 101        | 2023-05-03  | 15.00

Performance Tips for LATERAL in Snowflake

Filter rows as early as possible in your subquery.
Select only necessary columns to reduce memory usage.
Convert reused logic into materialized views if needed.
Track execution cost using Snowflake’s query profiler.
Scale your virtual warehouse appropriately for concurrency and size.

Summary and Conclusion

Even though Snowflake lacks a CROSS APPLY operator, LATERAL JOIN fully replicates its functionality. You can migrate SQL Server logic by rewriting joins with INNER JOIN LATERAL or LEFT JOIN LATERAL depending on your goal.

Once you understand how these joins operate, you’ll unlock powerful row-context query options in Snowflake—perfect for aggregation, subqueries, and multi-stage logic.

About DataSunrise

DataSunrise helps organizations secure Snowflake databases with powerful tools for auditing, access control, data masking, and compliance automation. Our unified platform works across cloud and on-prem systems to protect sensitive data in real time.

If you’re ready to explore how DataSunrise can help with Snowflake security and compliance, request a demo. Our experts will show you how to integrate our solution with your data stack—without slowing down performance.