Common Table Expressions (CTEs)
Here is an example of a CTE. We are going to query the view we just created, but we are going to filter based on the total_price being above 20000. We are calling that sales_below_20000 and then immediately querying the DISTINCT customer_name from that CTE.
USE demo; WITH sales_below_20000 AS (SELECT * FROM high_sales WHERE total_price < 20000) SELECT DISTINCT customer_name FROM sales_below_20000;
Common table expression (CTE) (Databricks SQL)
Defines a temporary result set that you can reference possibly multiple times within the scope of a SQL statement. A CTE is used mainly in a
WITH common_table_expression [, ...] common_table_expression view_identifier [ ( column_identifier [, ...] ) ] [ AS ] ( query )
-- CTE with multiple column aliases > WITH t(x, y) AS (SELECT 1, 2) SELECT * FROM t WHERE x = 1 AND y = 2; 1 2 -- CTE in CTE definition > WITH t AS ( WITH t2 AS (SELECT 1) SELECT * FROM t2) SELECT * FROM t; 1 -- CTE in subquery > SELECT max(c) FROM ( WITH t(c) AS (SELECT 1) SELECT * FROM t); 1 -- CTE in subquery expression > SELECT (WITH t AS (SELECT 1) SELECT * FROM t); 1 -- CTE in CREATE VIEW statement > CREATE VIEW v AS WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4) SELECT * FROM t; > SELECT * FROM v; 1 2 3 4 -- CTE names are scoped > WITH t AS (SELECT 1), t2 AS ( WITH t AS (SELECT 2) SELECT * FROM t) SELECT * FROM t2; 2