Common table expressions

1. Common table expressions

In this video, we will learn about Common Table Expressions or CTEs. CTEs are named temporary result sets within a query that enhance readability and usability.

2. What is a CTE?

A Common Table Expression (CTE) is a powerful tool in BigQuery that helps us simplify complex queries. In this example, 'our-cte' acts as a temporary result, allowing us to break down our queries into manageable, reusable parts. This only persists in the scope of this query and cannot be reused in another query.

3. Subqueries vs. CTEs

Let's compare CTEs and subqueries in BigQuery. Look at this example: With a subquery, we nest queries, which can lead to complex, harder-to-maintain code. However, employing a CTE, as seen in our example on the right, streamlines readability and allows for easier modification and optimization. In this example, our CTE, 'cte-name,' simplifies the query structure, enhancing clarity and maintainability compared to the nested subquery approach.

4. Writing CTEs

Creating a CTE involves two primary steps in BigQuery. Firstly, we use the 'WITH' clause to define the CTE's name and structure. Secondly, we reference the CTE in the main query. In BigQuery, a CTE's scope is limited to the query where it's defined. Here, 'cte-name' is only accessible within this query block or the first SELECT statement within your query.

5. Using multiple CTEs

Employing multiple CTEs empowers us to break down complex queries into manageable parts. By creating modular segments of logic, like in the example here, we enhance readability and maintainability, simplifying intricate queries step by step. We only need to use the 'WITH' keyword once initially. All other CTEs are still contained in parentheses and begin with the CTE name followed by 'AS'. As illustrated here, you can also use previous CTEs within a CTE that follows it.

6. Using CTEs to filter data

CTEs in BigQuery offer an efficient way to filter and organize data before querying. Consider this scenario: Here, 'filtered_data' represents a subset meeting our specified condition. Using a CTE to filter data allows the main query to run on only the rows returned by the CTE, and fewer rows equate to faster queries. Leveraging CTEs for filtering streamlines queries by focusing on the necessary data subsets. This, in turn, optimizes performance and enhances the clarity of our query logic.

7. Using CTEs to optimize queries

CTEs play a pivotal role in query optimization in BigQuery. By pre-computing complex logic or repetitive subqueries, we improve efficiency. In this example, 'precomputed_data' stores aggregated values, reducing computation in subsequent queries. Leveraging CTEs for such optimizations improves performance by minimizing repetitive calculations and improving query speed.

8. Using CTEs to join data

One of the most common uses of CTEs is to join data from multiple tables. Instead of writing multiple JOIN clauses, we can use CTEs to define the intermediate results of each join and then reference those CTEs in the final query. This can make our queries more concise and easier to follow.

9. Let's practice!

Let's jump into some exercises to put CTEs into practice in BigQuery.

Create Your Free Account

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