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 SELECT statement.
WITH common_table_expression [, ...] common_table_expression view_identifier [ ( column_identifier [, ...] ) ] [ AS ] ( query )
Examples
-- 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
WITH column_date AS (
SELECT date_format(TIMESTAMPADD(HOUR, +7, current_timestamp()),'yyyyMMdd') AS T0
, date_format(TIMESTAMPADD(HOUR, +7-24, current_timestamp()),'yyyyMMdd') AS T1
, date_format(TIMESTAMPADD(HOUR, +7-48, current_timestamp()),'yyyyMMdd') AS T2
, date_format(TIMESTAMPADD(HOUR, +7-72, current_timestamp()),'yyyyMMdd') AS T3
)
SELECT *
FROM column_date;
---------------------------------------------
| T0 | T1 | T2 | T3 |
---------------------------------------------
| 20230310 | 20230309 | 20230308 | 20230307 |