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.

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.

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.