WITH

A common table expression (CTE) defines a named query that can referenced multiple times within the scope of a SQL statement. CTEs are visible within the query block in which they are defined, and they can nested.

Only non-recursive CTEs are supported at the moment.

Syntax

Parameter

• cteName

  The name of the common table expression.

• columnAlias

  The alias of the corresponding column of the CTE query.

• cteQuery

  The common table expression query.

• query

  The query the can optionally reference the CTE query via its given cteName.

Example

Simple CTE

Suppose we have the following tables representing a simple employee / department model:

create table employees(id bigint, name string, dept bigint, primary key(id))
insert into employees values (1, 'Dylan', 42),
                             (2, 'Mark', 42),
                             (3, 'Helly', 42),
                             (4, 'Irving', 42),
                             (5, 'Seth', 1),
                             (50, 'Burt', 44)

Here is a simple CTE query:

with macro_data_refinement_employees(name) as (select name from employees where dept = 42)
select * from macro_data_refinement_employees

we get the following result:

name
"Dylan"
"Mark"
"Irving"
"Helly"

Another CTE example

Suppose we want to find out which restaurant has an average rating higher than 3, we can leverage CTE to break the query into two logical parts, one part for calculating the restaurant names and their averages:

select name, ar
from restaurant as a, (select avg(rating) as ar from a.reviews) as x

The second part will operate on the result set of the first query, and filter out all the columns less or equal to 3, to do this, we wrap the first part in a CTE and use it in the filtering query:

with ratings(name, avgRating)
as (select name, ar from restaurant as a, (select avg(rating) as ar from a.reviews) as x)
select name, avgRating from ratings where avgRating > 3.0f

We get the desired result:

name	avgRating
"Restaurant44"	4.0