Common Table Expressions

Common Table Expressions, or CTEs, allow a complex subquery to be represented in a short way later on for improved readability, and reuse multiple times in a query.

Note that CTEs do not affect query performance.

Overview

The Common Table Expressions page describes the following:

Syntax

The following is the correct syntax when using CTEs:

cte ::=
    [ WITH table_alias AS (query)
         [, ...]
    ]


query ::=
   query_term
   [ UNION ALL query_term [ ...] ]
   [ ORDER BY order [, ... ] ]
   [ LIMIT num_rows ]
   ;

query_term ::=
      SELECT
        [ TOP num_rows ]
        [ DISTINCT ]
        select_list
        [ FROM table_ref [, ... ]
            [ WHERE value_expr
            [ GROUP BY value_expr [, ... ]
               [ HAVING value_expr ]
            ]
      | ( VALUES ( value_expr [, ... ] ) [, ... ] )

Examples

This section includes the following examples:

Using a Simple CTE

The following is an example of using a simple CTE:

nba=> WITH s AS (SELECT "Name" FROM nba WHERE "Salary" > 20000000)
.        SELECT * FROM nba AS n, s WHERE n."Name" = s."Name";
Name            | Team                  | Number | Position | Age | Height | Weight | College      | Salary   | name0
----------------+-----------------------+--------+----------+-----+--------+--------+--------------+----------+----------------
Carmelo Anthony | New York Knicks       |      7 | SF       |  32 | 6-8    |    240 | Syracuse     | 22875000 | Carmelo Anthony
Chris Bosh      | Miami Heat            |      1 | PF       |  32 | 6-11   |    235 | Georgia Tech | 22192730 | Chris Bosh
Chris Paul      | Los Angeles Clippers  |      3 | PG       |  31 | 6-0    |    175 | Wake Forest  | 21468695 | Chris Paul
Derrick Rose    | Chicago Bulls         |      1 | PG       |  27 | 6-3    |    190 | Memphis      | 20093064 | Derrick Rose
Dwight Howard   | Houston Rockets       |     12 | C        |  30 | 6-11   |    265 |              | 22359364 | Dwight Howard
Kevin Durant    | Oklahoma City Thunder |     35 | SF       |  27 | 6-9    |    240 | Texas        | 20158622 | Kevin Durant
Kobe Bryant     | Los Angeles Lakers    |     24 | SF       |  37 | 6-6    |    212 |              | 25000000 | Kobe Bryant
LeBron James    | Cleveland Cavaliers   |     23 | SF       |  31 | 6-8    |    250 |              | 22970500 | LeBron James

In this example, the WITH clause defines the temporary name r for the subquery which finds salaries over $20 million. The result set becomes a valid table reference in any table expression of the subsequent SELECT clause.

Using Nested CTEs

The following is an example of using a simple CTE:

WITH w AS
    (SELECT * FROM
        (WITH x AS (SELECT * FROM nba) SELECT * FROM x ORDER BY "Salary" DESC))
  SELECT * FROM w ORDER BY "Weight" DESC;

Note that SQream supports any amount of nested CTEs.

Reusing CTEs

The following is an example of reusing CTEs, separated by commas:

nba=> WITH
.        nba_ct AS (SELECT "Name", "Team" FROM nba WHERE "College"='Connecticut'),
.        nba_az AS (SELECT "Name", "Team" FROM nba WHERE "College"='Arizona')
.        SELECT * FROM nba_az JOIN nba_ct ON nba_ct."Team" = nba_az."Team";
Name            | Team            | name0          | team0
----------------+-----------------+----------------+----------------
Stanley Johnson | Detroit Pistons | Andre Drummond | Detroit Pistons
Aaron Gordon    | Orlando Magic   | Shabazz Napier | Orlando Magic

SQream supports reusing CTEs multiple times per query.

Using CTEs with the CREATE TABLE AS Statement

The following is an example of using CTEs with the CREATE TABLE AS statement:

When used with CREATE_TABLE_AS statement, the CREATE TABLE statement should appear before the WITH argument:

CREATE TABLE weights AS

WITH w AS
    (SELECT * FROM
        (WITH x AS (SELECT * FROM nba) SELECT * FROM x ORDER BY "Salary" DESC))
  SELECT * FROM w ORDER BY "Weight" DESC;

For more information about the CREATE_TABLE_AS statement, see CREATE TABLE AS.