How to Use CTE in Snowflake: Syntax and Examples
In Snowflake, use a
WITH clause to define a Common Table Expression (CTE) that creates a temporary named result set for use in a query. The CTE improves readability and can be referenced multiple times within the main query.Syntax
A CTE in Snowflake starts with the WITH keyword, followed by one or more named subqueries. Each subquery has a name and a query in parentheses. After defining CTEs, you write the main query that uses them.
- WITH: starts the CTE block
- cte_name: the temporary table name
- AS (subquery): defines the CTE's query
- Main query: uses the CTE name as a table
sql
WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name;
Example
This example shows a CTE that selects employees with salary over 50000, then the main query counts how many such employees exist.
sql
WITH high_salary_employees AS ( SELECT employee_id, salary FROM employees WHERE salary > 50000 ) SELECT COUNT(*) AS high_salary_count FROM high_salary_employees;
Output
HIGH_SALARY_COUNT
-----------------
42
Common Pitfalls
Common mistakes when using CTEs in Snowflake include:
- Forgetting the
WITHkeyword before the CTE name. - Not ending the CTE subquery with a closing parenthesis.
- Trying to use the CTE outside the query it is defined in.
- Using multiple CTEs without separating them by commas.
Always remember CTEs exist only for the duration of the query.
sql
/* Wrong: Missing WITH keyword */ -- high_salary_employees AS ( -- SELECT employee_id FROM employees WHERE salary > 50000 -- ) -- SELECT * FROM high_salary_employees; /* Right: Include WITH and commas if multiple CTEs */ WITH high_salary_employees AS ( SELECT employee_id FROM employees WHERE salary > 50000 ), recent_hires AS ( SELECT employee_id FROM employees WHERE hire_date > '2023-01-01' ) SELECT * FROM high_salary_employees;
Quick Reference
| CTE Part | Description |
|---|---|
| WITH | Starts the CTE block |
| cte_name | Temporary name for the CTE result set |
| AS (subquery) | Defines the query for the CTE |
| Main Query | Uses the CTE name as a table |
Key Takeaways
Use the WITH clause to define a CTE before your main query in Snowflake.
CTEs improve query readability and can be referenced multiple times within the same query.
Always separate multiple CTEs with commas and close each subquery with parentheses.
CTEs exist only during the execution of the query and cannot be reused outside it.
Common errors include missing WITH keyword and incorrect parentheses placement.
