WITH Clause - CTEs (Common Table Expressions) in Snowflake

A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE. The CTE defines the temporary view’s name, an optional list of column names, and a query expression (i.e. a SELECT statement). The result of the query expression is effectively a table. Each column of that table corresponds to a column in the (optional) list of column names.

Here is an example of a query that uses a CTE:

WITH subQ1(SchoolID) AS (SELECT SchoolID FROM Roster)
SELECT * FROM subQ1;

nVector

posted on 03 Oct 19