Snowflake WITH Clause Syntax, Usage and Examples

Snowflake WITH Clause is an optional clause that always precedes SELECT clause in the query statements or construct. The WITH clause usually contains a sub query that is defined as a temporary table similar to View definition. Each sub query in the WITH clause is associated with the name, an optional list of a column names, and a query that evaluates to a table. The query expression is usually a select statement.

In this article, we will check Snowflake WITH clause syntax, usage, types of WITH clause with some examples. The WITH clause are commonly referred to as a common table expression(s).

What is Snowflake WITH Clause?

WITH clause is an optional query construct precedes the SELECT statement within your query. A CTE or WITH clause is a syntactical sugar for a subquery.

In many data warehouse applications, there will always be a requirement to reuse the results of some query construct across multiple location. For example, you may use piece of the code in many locations of your query. You may create the common table expression (CTE) for that query construct and use CTE across all other locations.

Read:

Snowflake LIMIT and OFFSET – Uses and Examples

You can also use the WITH clauses to improve the complex sub-queries and improve overall Snowflake performance.

Type of WITH clause in Snowflake?

Snowflake cloud data warehouse supports two type of WITH clauses.

Non-Recursive WITH Clause
- These are simple WITH clauses
Recursive WITH Clause
- It can refer to itself and usually used to resolve hierarchy

Where you can use Snowflake WITH Clause?

The main advantage of WITH clause is, you can use it wherever SELECT clause is acceptable in the SQL script or query.

For instance, you can use it in:

INSERT INTO … SELECT
UPDATE – Within a WHERE clause of subquery
CREATE VIEW
DELETE
CTAS
SELECT

Snowflake WITH Clause Syntax

Below is the syntax for WITH clause:

[ <WITH clause> ] < <with list element> [ { <comma> <with list element> }... ]> 
<Select Statement>;

Snowflake WITH Clause Examples

Following example demonstrates the WITH clause usage.

WITH Clause usage along with SELECT statement

WITH s_patient_cte
     AS (SELECT *
         FROM   s_patient
         WHERE  id = 100001)
SELECT *
FROM   s_patient_cte
ORDER  BY 1;

Above query is 100% equivalent to following query.

SELECT *
FROM   (SELECT *
        FROM   s_patient
        WHERE  id = 100001) AS s_patient_cte
ORDER  BY 1;

Now, let us check common table expression usage with few examples.

WITH Clause in an INSERT Statement

Following Snowflake example demonstrate CTE in an INSERT statement.

INSERT INTO sample_table1 
WITH CTE AS 
(SELECT 1, 2 FROM dual) 
SELECT * from CTE;

WITH Clause in an UPDATE Statement Example

You can use Snowflake CTE in an UPDATE statement WHERE sub query.

For example:

UPDATE sample_table1 
SET    col1 = 3 
WHERE  col1 = (WITH sample_cte 
                    AS (SELECT 1 
                        FROM   dual) 
               SELECT * 
                FROM   sample_cte);

WITH clause in DELETE statement

You can use the Snowflake CTE in DELETE statement WHERE sub query.

For example:

DELETE FROM sample_table1 
WHERE  col1 IN (WITH sample_cte 
                     AS (SELECT 3 
                         FROM   dual) 
                SELECT * 
                 FROM   sample_cte);

WITH clause in CREATE TABLE AS Statement

You can use WITH clause in CREATE TABLE AS statement.

For example,

CREATE TABLE sample_table2 
AS
WITH CTE AS
( 
 SELECT current_date as col1
)
SELECT col1 
FROM CTE;

Recursive WITH Clause Example

The recursive WITH clause in Snowflake is something that refers to itself. These types of recursive queries are used to resolve hierarchical solutions.

WITH RECURSIVE rec_cte (X, Y) AS
(
  SELECT X, Y FROM table1
  UNION ALL
  SELECT X, Y
    FROM table1 JOIN rec_cte_name ON <join_condition>
)
SELECT ... FROM ...

Snowflake WITH Clause Restrictions

Below are some of WITH clause restrictions:

You cannot specify another WITH clause inside a WITH clause sub query.
You cannot make forward references to tables defined by WITH clause sub queries
CTEs are not currently fully supported in DDL operations. You can use with CREATE TABLE AS …
For recursive CTE, column list is mandatory.
Keyword recursive is used only once.