Categories:
Query Syntax

WITH

A WITH clause is an optional clause that precedes the SELECT clause in a statement. The WITH clause defines one or more subqueries or expressions. Each subquery has a name and is equivalent to a view definition valid for the duration of the statement only.

WITH clause aliases can be referenced in the FROM clause. Each subquery in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (a SELECT statement).

Syntax

WITH
  <subquery_name1> [ ( <col_list> ) ] AS ( SELECT ...  )
  [ , <subquery_name2> [ ( <col_list> ) ] AS ( SELECT ...  ) [ , ... ] ]
SELECT ...
FROM ...
[ ... ]

subquery_name1 and subquery_name2 must be valid SQL identifiers.

col_list is optional. If supplied, it defines names of the columns in the CTE (common table expression).

Usage Notes

  • A WITH clause subquery cannot refer recursively to the WITH element that it defines, but can refer to other WITH elements that appear earlier in the WITH clause. For instance, the subquery defining subquery_name2 can refer to subquery_name1, but neither of the subqueries can refer to subquery_name2.
  • Currently, the ACCOUNT_USAGE views cannot be called from inside a CTE (common table expression).
  • Currently, Snowflake does not fully support CTEs in DDL operations.

Examples

This section provides sample queries and sample output. To keep the examples shorter, the code omits the statements to create and load the tables.

In this example, we use a simple WITH clause as a view to extract a subset of data, in this case the music albums that were released in 1976. For this small database, the query output is the albums “Amigos” and “Look Into The Future”, both from the year 1976:

with
  albums_1976 as (select * from music_albums where album_year = 1976)
select album_name from albums_1976 order by album_name;
+----------------------+
| ALBUM_NAME           |
|----------------------|
| Amigos               |
| Look Into The Future |
+----------------------+

This is an example of one WITH clause using an earlier WITH clause; the subquery named journey_album_info_1976 uses the subquery named album_info_1976. The output is the album “Look Into The Future”, with the name of the band:

with
   album_info_1976 as (select m.album_ID, m.album_name, b.band_name
      from music_albums as m inner join music_bands as b
      where m.band_id = b.band_id and album_year = 1976),
   Journey_album_info_1976 as (select *
      from album_info_1976 
      where band_name = 'Journey')
select album_name, band_name 
   from Journey_album_info_1976;
+----------------------+-----------+
| ALBUM_NAME           | BAND_NAME |
|----------------------+-----------|
| Look Into The Future | Journey   |
+----------------------+-----------+

This example lists musicians who played on Santana albums and Journey albums. This example does not use the WITH clause. For this query (and the next few queries, all of which are equivalent ways of running the same query), the output is the IDs and names of musicians Gregg Rolie and Neal Schon, both of whom played on Santana albums and Journey albums.

select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Santana'
intersect
select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

As you can see, the previous query has a lot of duplicate code. The next few examples show how to simplify this query by using one or more explicit views, and then how to simplify it by using a WITH clause that creates implicit views rather than by using explicit views.

This query shows how to use views to reduce the duplication and complexity of the previous example (as in the previous example, this does not use a WITH clause):

create or replace view view_musicians_in_bands AS
  select distinct musicians.musician_id, musician_name, band_name
    from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
    where musicians.musician_ID = musicians_and_albums.musician_ID
      and musicians_and_albums.album_ID = music_albums.album_ID
      and music_albums.band_ID = music_bands.band_ID;

With this view, we can re-write our original query as:

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

This example uses a WITH clause to do the equivalent of what the preceding query did:

with
  musicians_in_bands as (
     select distinct musicians.musician_id, musician_name, band_name
      from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
      where musicians.musician_ID = musicians_and_albums.musician_ID
        and musicians_and_albums.album_ID = music_albums.album_ID
        and music_albums.band_ID = music_bands.band_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

These statements create more granular views (this example does not use a WITH clause):

List the albums by a particular band:

create or replace view view_album_IDs_by_bands AS
 select album_ID, music_bands.band_id, band_name
  from music_albums inner join music_bands
  where music_albums.band_id = music_bands.band_ID;

List the musicians who played on albums:

create or replace view view_musicians_in_bands AS
 select distinct musicians.musician_id, musician_name, band_name
  from musicians inner join musicians_and_albums inner join view_album_IDs_by_bands
  where musicians.musician_ID = musicians_and_albums.musician_ID
    and musicians_and_albums.album_ID = view_album_IDS_by_bands.album_ID;

Now use those views to query musicians who played on both Santana and Journey albums:

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

These statements create more granular implicit views (this example does use a WITH clause):

with
  album_IDs_by_bands as (select album_ID, music_bands.band_id, band_name
                          from music_albums inner join music_bands
                          where music_albums.band_id = music_bands.band_ID),
  musicians_in_bands as (select distinct musicians.musician_id, musician_name, band_name
                          from musicians inner join musicians_and_albums inner join album_IDs_by_bands
                          where musicians.musician_ID = musicians_and_albums.musician_ID
                            and musicians_and_albums.album_ID = album_IDS_by_bands.album_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+