Subquerying and Common Table Expressions
1. Subquerying and Common Table Expressions
Welcome back! After mastering joins, we're diving into subqueries and Common Table Expressions (CTEs) in Snowflake.2. Subquerying
Subquerying, as we know, is a query nested within another, commonly used in `FROM`, `HAVING`, `WHERE`, or `SELECT` clauses to refine results or analyze data. Here is an example subquery syntax in a `WHERE` clause. Snowflake supports both correlated and uncorrelated subqueries. Let's examine these.3. Uncorrelated subquery
An uncorrelated subquery functions independently of the main query. Here, the subquery identifies the most expensive pizza price. The main query then filters pizzas based on this price. Notice that the inner query doesn't pull data from the outer query, which is why it's uncorrelated.4. Correlated subquery
Meanwhile, a correlated subquery references columns from the outer or main query. For example, here, our goal is to find the top-priced pizzas in each category. The subquery identifies the maximum price for each pizza category by referencing `pizza_type_id` from the outer query. The main query then filters for pizzas equal to this value.5. Common Table Expressions
When subqueries get complex with many conditions, we need a cleaner and more flexible approach. This is where Common Table Expressions, or CTEs, come in handy. CTEs are like giving a nickname to a query so we can easily use it in a query. Once it's named, we can refer to it just like any other table. In the syntax here, we use cte1 as the name of CTE and then write our query inside parentheses.6. Common Table Expressions
Recall our earlier complex subquery to find the highest-priced pizzas for each type? We've simplified it using a CTE called max_price. This CTE aggregates the maximum price for each pizza_type_id from the pizzas table. In our main query, we then join the pizzas table with the pizza_type table and our CTE max_price. By doing this, we can easily compare the price of each pizza against the maximum price for its type, as found in the CTE. Using the WHERE clause, we filter out the results to only include pizzas that are cheaper than the most expensive in their category.7. Multiple CTEs
In CTEs, we're not limited to just one; we can use multiple in a single query. Each CTE, like cte1 and cte2 here, separated by a comma, serves its own purpose and can be incorporated into the main query.8. Why Use CTEs?
Building on our previous example, CTEs aren't just about simplifying complex operations. They add structure, improve readability, and enable reuse of results. But as with any tool, understanding their optimal usage is key.9. Let's practice!
Wasn't it fascinating to see the world of subqueries and CTEs? It becomes more exciting when you start crafting these queries yourself. Let's get hands-on!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.