【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还有待改进。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-07-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员的SOD蜜

同样的SQL语句在查询分析器执行很快,但是网站上执行超时的诡异问题

    同样的SQL语句在查询分析器执行很快,但是网站上执行超时,这个问题以前遇到过,解决办法是重新启动服务器,但过一段时间后(时间长短不一定,一般为一天后),...

2137
来自专栏GopherCoder

专栏:012:没时间解释了,快使用sqlalchemy

1446
来自专栏智能大石头

XCode v8.11 重量级分表分库(无视海量数据)

XCode天生就有分表分库功能,设计于2005年! 历时9年,这是分表分库功能第一次针对性正式更新。 在XCode里面,分表分库非常简单,在操作数据(查询/更...

19410
来自专栏圣杰的专栏

SQL递归查询知多少

最近工作中遇到了一个问题,需要根据保存的流程数据,构建流程图。数据库中保存的流程数据是树形结构的,表结构及数据如下图: ? 仔细观察表结构,会发现其树形结构的特...

2528
来自专栏Android开发与分享

【Android】数据存储(三) 数据库(SQLite)

3357
来自专栏我的博客

ES中的索引管理

一、创建索引 如果需要手动创建索引,则需要更改配置当中 action.auto_create_index: false 二、删除索引 DELETE  /i...

33112
来自专栏Vamei实验室

被解放的姜戈02 庄园疑云

上一回说到,姜戈的江湖初体验:如何架设服务器,如何回复http请求,如何创建App。这一回,我们要走入糖果庄园。 数据库是一所大庄园,藏着各种宝贝。一个没有数据...

1845
来自专栏牛肉圆粉不加葱

Spark Sql 源码剖析(三):Analyzer

当一条 sql 语句被 SparkSqlParser 解析为一个 unresolved logicalPlan 后,接下来就会使用 Analyzer 进行 re...

612
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第二十四天 Oracle学习【悟空教程】

ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S...

952
来自专栏沃趣科技

Oracle 12c R2版本 Application Containers 特性(二)

在上一篇Oracle 12c R2版本 Application Containers 特性(一)中讲解了Application Root/PDBs的创建以及“A...

3258

扫描关注云+社区