Help Center/ DataArts Fabric/ Developer Guide/ Querying Data/ WITH Expression
Updated on 2025-09-18 GMT+08:00
View PDF
Share

WITH Expression

The WITH expression is used to define auxiliary statements used in large queries. These auxiliary statements are usually called common table expressions (CTE), which can be understood as a named subquery. The subquery can be referenced multiple times by its name in the quey.

The auxiliary statement in the WITH expression can be SELECT, and the WITH clause itself can be attached to a main statement. The main statement can be SELECT, INSERT, or DELETE.

SELECT in WITH

This section describes the usage of SELECT in a WITH clause.

Syntax

1
 
[WITH  with_query [,…] ] SELECT …

The syntax of with_query is as follows:

1
2
 
with_query_name [ ( column_name [, ...] ) ]
    AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )
  • If you use MATERIALIZED, the subquery runs once and its result set is saved. If you use NOT MATERIALIZED, the subquery is replaced with its reference in the main query.
  • The SQL statement specified by the AS statement of each CTE must be a statement that can return query results. Currently, only the SELECT query statement is supported. Other data modification statements such as INSERT, UPDATE, DELETE, and VALUES are not yet supported.
  • A WITH expression indicates the CTE definition in a SQL statement block. Multiple CTEs can be defined at the same time. You can specify column names for each CTE or use the aliases of the columns in the query output. Example: 
    1
    		 
    WITH s1(a, b) AS (SELECT x, y FROM t1), s2 AS (SELECT x, y FROM t2) SELECT * FROM s1 JOIN s2 ON s1.a=s2.x;

    This statement defines two CTEs: s1 and s2. s1 specifies the column names a and b, and s2 does not specify the column names. Therefore, the column names are the output column names x and y.

  • Each CTE can be referenced zero, one, or more times in the main query.
  • CTEs with the same name cannot exist in the same statement block. If CTEs with the same name exist in different statement blocks, the CTE in the nearest statement block is referenced.
  • An SQL statement may contain multiple SQL statement blocks. Each statement block can contain a WITH expression. The CTE in each WITH expression can be referenced in the current statement block, subsequent CTEs of the current statement block, and sub-layer statement blocks, however, it cannot be referenced in the parent statement block. The definition of each CTE is also a statement block. Therefore, a WITH expression can also be defined in the statement block.

The purpose of SELECT in WITH is to break down complex queries into simple parts. Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
    WITH regional_sales AS (
         SELECT region, SUM(amount) AS total_sales
         FROM orders
         GROUP BY region
     ), top_regions AS (
         SELECT region
         FROM regional_sales
         WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
     SELECT region,
            product,
            SUM(quantity) AS product_units,
            SUM(amount) AS product_sales
     FROM orders
     WHERE region IN (SELECT region FROM top_regions)
     GROUP BY region, product;

The WITH clause defines two auxiliary statements: regional_sales and top_regions. The output of regional_sales is used in top_regions, and the output of top_regions is used in the main SELECT query. This example can be written without WITH. In that case, it must be written with a two-layer nested sub-SELECT statement, making the query longer and difficult to maintain.

Parent topic: Querying Data