【SQL揭秘】有多少种数据库,就有多少类CTE

Common Table Expression

Common table expression简称CTE，由SQL:1999标准引入，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.

CTE的语法如下：

1、Non-recursive CTEs 

2、Recursive CTEs 

CTE的使用

CTE使语句更加简洁

例如以下两个语句表达的是同一语义，使用CTE比未使用CTE的嵌套查询更简洁明了。

1) 使用嵌套子查询

2) 使用CTE

CTE 可以进行树形查询

初始化这颗树

1) 层序遍历

2) 深度优先遍历

Oracle

Oracle从9.2才开始支持CTE, 但只支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的树形查询，recursive with 语句可以与connect by语句相互转化。 一些相互转化案例可以参考这里.

Oracle recursive with 语句不需要指定recursive关键字，可以自动识别是否recursive.Oracle 还支持CTE相关的hint,

“MATERIALIZE”告诉优化器产生一个全局的临时表保存结果，多次引用CTE时直接访问临时表即可。而”INLINE”则表示每次需要解析查询CTE。

PostgreSQL

PostgreSQL从8.4开始支持CTE，PostgreSQL还扩展了CTE的功能， CTE的query中支持DML语句，例如

MariaDB

MariaDB从10.2开始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2开始支持recursive CTE。 目前的GA的版本是10.1.

MySQL

MySQL从8.0开始支持完整的CTE。MySQL8.0还在development
阶段，RC都没有，GA还需时日。

AliSQL

AliSQL基于mariadb10.2， port了no-recursive CTE的实现，此功能近期会上线。

以下从源码主要相关函数简要介绍其实现，

//解析识别with table引用 
find_table_def_in_with_clauses

//检查依赖关系，比如不能重复定义with table名字 
With_clause::check_dependencies

// 为每个引用clone一份定义 
With_element::clone_parsed_spec

//替换with table指定的列名 
With_element::rename_columns_of_derived_unit

此实现对于多次引用CTE，CTE会解析多次，因此此版本CTE有简化SQL的作用，但效率上没有效提高。

select count(*) from t1 where c2 !='z';

+----------+

| count(*) |

+----------+

|    65536 |

+----------+

1 row in set (0.25 sec)

//从执行时间来看是进行了3次全表扫描

 with t as (select count(*) from t1 where c2 !='z')

     select * from t union select * from t union select * from t;

+----------+

| count(*) |

+----------+

|    65536 |

+----------+

1 row in set (0.59 sec)

 select count(*) from t1 where c2 !='z'

     union

     select count(*) from t1 where c2 !='z'

     union

    select count(*) from t1 where c2 !='z';

+----------+

| count(*) |

+----------+

|    65536 |

+----------+

1 row in set (0.57 sec)

explain  select count(*) from t1 where c2 !='z'

    union

    select count(*) from t1 where c2 !='z'

    union

    select count(*) from t1 where c2 !='z';

以下是MySQL8.0 只扫描一次的执行计划

以下是PostgreSQL9.4 只扫描一次的执行计划

AliSQL还有待改进。