Common Table Expression

A CTE (Common table expression) is a named temporary result set that exists only within the execution scope of a single SQL statement (such as SELECT, INSERT, UPDATE, or DELETE).

Like derived tables, CTEs are not stored as objects and persist only for the duration of query execution; Unlike derived tables, CTEs can be self-referenced (recursive Ctes, not currently supported) or referenced multiple times in the same query. In addition, CTEs provide better readability and performance than derived tables.

The structure of a CTE includes a name, a list of optional columns, and the query that defines the CTE.

CTE Statement:

1. WITH <query_name> AS (
2. <query_definition>
3. )
4. SELECT ... FROM <query_name>;

Before you start

Make sure you have already Deployed standalone MatrixOne.

Preparation

You can create a simple table and insert some data to help you understand the CTE statements shown later:

1. drop table if exists t1;
2. create table t1(a int, b int, c int);
3. insert into t1 values(null,null,null),(2,3,4);

CTE Example

In the following example, qn is created as a temporary result set and the corresponding query results are cached in MatrixOne. You can perform a formal qn query better than in a non-CTE scenario.

1. mysql> WITH qn AS (SELECT a FROM t1), qn2 as (select b from t1)
2. SELECT * FROM qn;

Result as below:

1. +------+
2. | a    |
3. +------+
4. | NULL |
5. |    2 |
6. +------+