Joining in Snowflake

1. Joining in Snowflake

Ready to explore some advanced concepts? This video will explore two powerful joins: NATURAL and LATERAL joins.

2. JOINS

We already know that SQL joins combine data from multiple tables. The mechanics of joins in Snowflake are similar to PostgreSQL. Here, we'll focus on NATURAL and LATERAL joins.

3. Pizza dataset

First, let's recap the pizza database we've been interacting with; here's its schema diagram. Pay attention to the different relationships, such as the one-to-many relationship between the orders and order_details tables established by the order_id.

4. NATURAL JOIN

Let's explore `NATURAL JOIN`. Unlike standard joins, `NATURAL JOIN`s eliminate duplicate columns by automatically matching columns with the same name. Observe the syntax displayed. `NATURAL JOIN` can also be combined with `OUTER JOIN`, and notice there is no `ON` clause in this join. Let's understand why.

5. NATURAL JOIN

In a standard `JOIN` using an `ON` condition, we might get duplicate columns. For instance, `pizza_type_id` could appear twice, with the second labeled as `pizza_type_id_2`. `NATURAL JOIN` avoids this by automatically matching same-named columns across tables, such as `pizza_type_id`, eliminating duplicates and the need for an `ON` clause. So, it's convenient for quickly matching columns with the same name between tables without explicitly stating the matching conditions.

6. NATURAL JOIN

Even if we try to specify `ON` condition with `NATURAL JOIN`, as shown here, we will get a syntax error.

7. NATURAL JOIN

As usual, we can use `WHERE` to filter the results, as shown in this example.

8. LATERAL JOIN

Next, let's examine `LATERAL` joins. It's a powerful SQL feature that brings more flexibility to our queries. The `LATERAL` keyword allows a subquery within the `FROM` clause to access columns from a preceding table or view. So, when we use `LATERAL`, the right-hand subquery can reference columns from the left-hand table, making our queries more dynamic. After `SELECT` statement, add the keyword `LATERAL`, followed by subquery. We'll clarify this with an example next.

9. LATERAL JOIN with a subquery

Here, we're pulling data from two tables, `pizzas`, and `pizza_type`, using the `LATERAL` keyword. This allows the subquery to reference columns from pizzas. For each row in pizzas, the `LATERAL` subquery finds a matching `pizza_type_id` in `pizza_type`. The `WHERE` clause handles the joining - notice we don't need to include the `JOIN` keyword? We need to alias after using `LATERAL`, or we will get an error. So, why not just use a `JOIN`? While a `JOIN` could do something similar, `LATERAL` is more flexible in scenarios where we want the right-hand subquery to perform more complex operations dependent on the left-hand table.

10. Why LATERAL JOIN?

In this example, we use `LATERAL` to pair orders with a subquery that calculates the total cost for each `order_id`. We could get the same result with multiple joins, but when working with large datasets, using multiple joins can significantly slow down the query, thereby increasing computational costs. We'll dive deeper into cost-saving techniques in upcoming videos. The power of `LATERAL` is broader than this case; it opens the door for more complex queries, though we won't cover every application in this course, like usage with the `FLATTEN` function.

11. Let's practice!

It's time to advance our hands-on skills with exercises. Good luck!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.