Common Table Expressions

The CTE ("Common Table Expression") can be thought of as a "temporary view" or "named subquery" that can be used in the query in which CTE is defined.

The CTE defines:

• The name of the "temporary view" (or "query")
• A list of column names and a query expression (optional).

The result of the CTE can be used like a table, where each column corresponds to a column in the (optional) list of column names.

The goal of using CTEs is to increase flexibility and to simplify the code maintenance.

WITH command and CONNECT BY command are used in conjunction with CTEs.

Example of a simple CTE named cte_2cols and its use in a SELECT statement.

with
  cte_2cols as (select 'a' as ord1, '10' as col1, 50 as col2
                union all 
                select 'a' as ord1,  '40' as col1, 20 as col2 
                union all 
                select 'b' as ord1,  '50' as col1, NULL as col2)
select ord1, col2 from cte_2cols order by col2 desc;

+------+------+
| ord1 | col2 |
+------+------+
| b    | NULL |
| a    | 50   |
| a    | 20   |
+------+------+

When naming a CTE, the following rules should be followed:

• CTEs should not be named like SQL functions.
• CTEs should not be named like views or tables.

In case of name overlapping, the use of CTE in the subsequent query is preferred over other objects.

See WITH for more examples.