The WITH clause can be used in queries to create a Common Table Expression (CTE). A CTE generates a temporary result set that exists during the execution of a single SQL statement and can be referenced by the query. CTEs can simplify query logic and improve query performance by reusing repeated calculations.

When using, please replace the placeholders in the example with actual fields from your data table.

WITH...SELECT... #

• The following queries are equivalent:

  WITH cte AS ( 
      SELECT a, SUM(b) AS c 
      FROM t 
      GROUP BY a 
      ORDER BY c DESC 
  ) 
  SELECT * 
  FROM cte 
  LIMIT 10;
  

  SELECT * 
  FROM ( 
      SELECT a, SUM(b) AS c 
      FROM t 
      GROUP BY a 
      ORDER BY c DESC 
  ) cte 
  LIMIT 10;
  

• Referencing a WITH statement multiple times

  WITH cte AS ( 
      SELECT a, SUM(b) AS c 
      FROM t 
      GROUP BY a 
  ) 
  SELECT 
      t1.d, 
      t1.a, 
      t1.b, 
      cte1.c AS total, 
      CASE WHEN cte2.c > 10000 THEN 'X' 
           ELSE 'Y' 
      END AS type 
  FROM t1 
  JOIN cte cte1 ON t1.a = cte1.a 
  JOIN cte cte2 ON t1.a = cte2.a 
  WHERE t1.b > 100;
  

• Defining multiple subqueries with a WITH statement

  WITH 
      cte1 AS ( 
          SELECT COUNT(*) AS cnt 
          FROM t 
          WHERE d = 'val' 
      ), 
      cte2 AS ( 
          SELECT COUNT(*) AS cnt 
          FROM t 
      ) 
  SELECT 
      c1.cnt AS cnt1, 
      c2.cnt AS cnt2, 
      (c1.cnt * 100.0 / c2.cnt) AS ratio 
  FROM cte1 c1, cte2 c2;
  

• Mutual references between multiple subqueries in a WITH statement

  WITH 
      cte1 AS ( 
          SELECT a, SUM(b) AS c 
          FROM t 
          GROUP BY a 
      ), 
      cte2 AS ( 
          SELECT SUM(c) AS total 
          FROM cte1 
      ), 
      cte3 AS ( 
          SELECT 
              c1.a, 
              c1.c, 
              (c1.c * 100.0 / c2.total) AS ratio 
          FROM cte1 c1 
          CROSS JOIN cte2 c2 
      ) 
  SELECT * 
  FROM cte3 
  ORDER BY ratio DESC;
  

INSERT...WITH #

• Using a WITH statement in INSERT

  INSERT INTO target (a, c) 
  WITH cte AS ( 
      SELECT a, SUM(b) AS c 
      FROM t 
      GROUP BY a 
  ) 
  SELECT a, c 
  FROM cte 
  WHERE c > 10000;
  

• Using a WITH statement in INSERT OVERWRITE

  INSERT OVERWRITE summary 
  WITH 
      cte1 AS ( 
          SELECT 
              a, 
              SUM(b)      AS total, 
              COUNT(*)    AS cnt 
          FROM t 
          WHERE e >= DATE '2025-01-01' 
          GROUP BY a 
      ), 
      cte2 AS ( 
          SELECT 
              a, 
              total * 1.0 / cnt AS avg 
          FROM cte1 
      ) 
  SELECT 
      s.a, 
      s.total, 
      s.cnt, 
      a.avg 
  FROM cte1 s 
  JOIN cte2 a 
      ON s.a = a.a;
  

Settings parameters #

You can use the following settings parameters to optimize CTEs. For detailed functionality, see Auto CTE optimizationandImplicitCTEoptimization

| | | | | | \

CTE supports two execution modes: Inline all and Shared.
Inline all execution method directly expands the CTE in the main query, executing it separately each time it is referenced. This execution mode is simpler and allows for more optimization, but repeated scanning and calculation can cause significant waste.
Shared execution method reuses results to avoid repeated calculations. However, this method prevents the optimizer from applying certain optimizations: for example, it cannot push down correct predicates from the parent query, causing the CTE to scan many unnecessary rows; it also prevents join reordering, making it impossible to achieve globally optimal join order. Additionally, if the CTE is too large, the query may require a significant amount of extra memory or temporary storage, resulting in inefficient execution.
ByteHouse implements CBO Auto CTE optimization, enumerating Inline/Shared plans for different CTEs in a query, and finally selecting based on cost, choosing different execution methods for different CTEs (Partial Inline) to find the globally optimal solution.
ByteHouse can set different execution modes via settings cte_mode:

• cte_mode='SHARED': Shared execution method.
• cte_mode='INLINED': Inline all execution method.
• cte_mode='AUTO': Partial Inline execution method.

ByteHouse's Auto CTE can optimize WITH statements, defined VIEWs, and CTEs generated by implicit CTEs in queries.

Example usage:

WITH v as (SELECT a, b FROM t 
WHERE c < 1000) 
SELECT v1.* FROM v v1, v v2, v v3 
WHERE v1.a = v2.a AND v2.a = v3.a 
AND v3.b = 'val' 
SETTINGS cte_mode = 'INLINED';

ByteHouse can enable enable_shared_common_plan_node, automatically discover identical parts in queries, and construct CTEs.

SELECT * FROM 
(SELECT a, b FROM t WHERE c < 1000) v1 
WHERE b = 'val1'; 
UNION ALL 
SELECT * FROM 
(SELECT a, b FROM t WHERE c < 1000) v2 
WHERE b = 'val2' 
SETTINGS enable_shared_common_plan_node = true;