WITH CLAUSE (Common Table Expression)
You can use Common Table Expressions, also known as the
to break down complicated queries into simpler parts by naming and
referring to subqueries within queries.
A Common Table Expression (CTE) provides a way of defining a temporary result set whose definition is available only to the query in which the CTE is defined. The result of the CTE is not stored; it exists only for the duration of the query. CTEs are helpful in reducing query complexity and increasing readability. They can be used as substitutions for views in cases where either you dont have permission to create a view or the query would be the only one using the view. CTEs allow you to more easily enable grouping by a column that is derived from a scalar sub select or a function that is non deterministic.
WITH clause is also known as the subquery factoring clause.
The handling and syntax of
WITH queries are similar to the handling
and syntax of views. The
WITH clause can be processed as an inline
view and shares syntax with
CREATE VIEW. The
WITH clause can also
resolve as a temporary table, which may enhance the efficiency of a
WITH queryName AS SELECT Query
An identifier that names the subquery clause.
You cannot currently use a temporary table in a
WITH clause. This is
being addressed in a future release of Splice Machine.
If we create the following table:
CREATE TABLE BANKS ( INSTITUTION_ID INTEGER NOT NULL, INSTITUTION_NAME VARCHAR(100), CITY VARCHAR(100), STATE VARCHAR(2), TOTAL_ASSETS DECIMAL(19,2), NET_INCOME DECIMAL(19,2), OFFICES INTEGER, PRIMARY KEY(INSTITUTION_ID) );
We can then use a common table expression to improve the readability of a statement that finds the per-city total assets and income for the states with the top net income:
WITH state_sales AS ( SELECT STATE, SUM(NET_INCOME) AS total_sales FROM BANKS GROUP BY STATE ), top_states AS ( SELECT STATE FROM state_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM state_sales) ) SELECT STATE, CITY, SUM(TOTAL_ASSETS) AS assets, SUM(NET_INCOME) AS income FROM BANKS WHERE STATE IN (SELECT STATE FROM top_states) GROUP BY STATE, CITY;