Snowflake Cross Apply

Introduction

When using SQL for complex queries, you may need to combine data from different tables in more advanced ways. This goes beyond just using inner and outer joins. The CROSS APPLY operator in SQL Server lets you join a table with a table-valued function, creating versatile query options. However, if you’re using the Snowflake cloud data platform, you may have noticed that there is no Snowflake CROSS APPLY analog.

This article will explain how CROSS APPLY works and how to use it in Snowflake with LATERAL JOIN. We will also compare the differences between these two methods. We will also compare the differences between these two approaches.

By the end, you’ll have a solid understanding of how to perform complex joins in Snowflake that are analogous to SQL Server’s CROSS APPLY. Let’s dive in!

What is CROSS APPLY?

In SQL Server, CROSS APPLY is an operator that allows you to join a table with a table-valued function. It applies the function to each row of the left table and produces a result set by combining the rows from the left table with the corresponding rows returned by the function.

Here’s a simple example to illustrate how it works:

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

In this example, the subquery is executed for each row in the Person table using the operator. It returns the matching rows from the Company table where companyId matches. The result is a join between Person and Company based on the ‘companyId’ relationship.

The equivalent query using a standard INNER JOIN syntax would be:

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

Both queries will return the same result set, but the CROSS APPLY version allows for more complex join conditions and can be particularly useful when working with table-valued functions.

Snowflake Cross Apply

Snowflake does not have CROSS APPLY, but it has similar functionality with the LATERAL keyword when used with a join. In ANSI SQL standard, a lateral join allows you to use columns from previous tables in the join condition. This results in the same outcome as using CROSS APPLY.

Here’s an example of how you can use a lateral join in Snowflake to achieve the same result as the example above:

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

In this example, the LATERAL keyword is used to indicate that the subquery following it can reference columns from the preceding Person table. The ON TRUE condition unconditionally joins the lateral subquery with the Person table.

The lateral join behaves similarly to CROSS APPLY, executing the subquery for each row of the left table and combining the results. The main difference is that CROSS APPLY performs an inner join, while the lateral join example above uses a left join. You can achieve an inner join behavior by simply changing LEFT JOIN LATERAL to INNER JOIN LATERAL.

Execution Plans

Let’s take a closer look at the execution plans for the CROSS APPLY and lateral join examples to understand how they differ.

For the CROSS APPLY example in SQL Server:

1. The system scans the Person table to retrieve all rows.
2. For each row in Person, SQL Server runs subquery following the CROSS APPLY. It filters the Company table based on the companyId condition.
3. The query joins the resulting rows from the subquery with the corresponding row from Person.
4. The function returns the final result set.

For the LATERAL JOIN example in Snowflake:

1. The system scans the Person table to retrieve all rows.
2. The lateral subquery executes for each row in Person, filtering the Company table based on the companyId condition.
3. The resulting rows from the lateral subquery are left joined with the corresponding row from Person using the ON TRUE condition.
4. The function returns the final result set.

The execution plans for both approaches are similar, with the main difference being the type of join used (inner join for CROSS APPLY and left join for the lateral join example).

Example with Preliminary Setup

Let’s look at a more comprehensive example that includes some preliminary setup steps. Suppose we have two tables: Orders and OrderItems. Each order can have multiple order items, and we want to retrieve the total amount for each order along with the order details.

First, let’s create the necessary tables:

-- Create Orders table
CREATE TABLE Orders (
OrderID INT,
CustomerID INT,
OrderDate DATE
);
-- Create OrderItems table
CREATE TABLE OrderItems (
OrderID INT,
ItemID INT,
Quantity INT,
Price DECIMAL(10, 2)
);

-- Insert sample data into Orders table
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
(1, 101, '2023-05-01'),
(2, 102, '2023-05-02'),
(3, 101, '2023-05-03');
-- Insert sample data into OrderItems table
INSERT INTO OrderItems (OrderID, ItemID, Quantity, Price)
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, let’s use a lateral join to retrieve the order details along with the total amount for each order:

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;

In this example, for each row in the Orders table, the lateral subquery is executed to calculate the total amount of the order by summing the product of Quantity and Price for the corresponding order items. The result then joins with the Orders table using the ON TRUE condition.

The output of this query will be:

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

Summary and Conclusion

In this article, we looked at how CROSS APPLY works in SQL Server and its equivalent in Snowflake using lateral joins. We found out that Snowflake doesn’t have CROSS APPLY, but lateral joins work similarly. They let subqueries use columns from previous tables in the join condition.

We looked at examples of how to use CROSS APPLY in SQL Server and how to achieve the same result using a lateral join in Snowflake. We talked about the plans for both methods and gave a detailed example with initial setup steps.

Knowing how to use lateral joins in Snowflake is important for writing efficient queries with multiple tables and subqueries.

About DataSunrise

DataSunrise provides user-friendly and flexible tools for Snowflake database security, audit, and compliance. Our solutions help organizations protect sensitive data, monitor database activity, and ensure compliance with regulations such as GDPR, HIPAA, and PCI DSS.

If you’re interested in learning more about what DataSunrise has to offer, we invite you to request an online demo. Our team of experts will be happy to showcase our products and discuss how they can assist you in securing and managing your databases effectively.