【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 条评论
登录 后参与评论

相关文章

来自专栏Java开发

Spring集成MyBatis 通用Mapper以及 pagehelper分页插件

这里送上楼主配置的Spring DataSource 和 TransactionManager 和MyBatis配置

1942
来自专栏xingoo, 一个梦想做发明家的程序员

Elasticsearch——Search的基本介绍

Elasticsearch最常用的方法莫过于查询了。Es支持以URI请求参数或者请求体的方式进行查询。 查询范例 Elasticsearch支持对多索引以...

1818
来自专栏涂小刚的专栏

Spark SQL 之 Join 实现

如今Spark SQL(Dataset/DataFrame)已经成为Spark应用程序开发的主流,作为开发者,我们有必要了解Join在Spark中是如何组织运行...

3.2K1
来自专栏跟着阿笨一起玩NET

关于使用CTE(公用表表达式)的递归查询

本文转载:http://www.cnblogs.com/shuangnet/archive/2013/03/22/2975929.html

862
来自专栏WindCoder

网易MySQL微专业学习笔记(五)-SQL语言进阶

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

541
来自专栏游戏开发那些事

mysql数据库之基础SQL语句/语法

SQL是现在进入互联网工作人们的必须技能之一,下面分享自己觉得很nice的SQL基本语句,从网上找了,觉得很不错,就分享给大家! 简要介绍基础语句:  1、说明...

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

JavaWeb05- 就这几步轻松操作MySQL数据库!

mysql-1 一.数据库 1. 数据库介绍 数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以通过sql语句对数据库中...

2615
来自专栏JavaQ

三分钟学习分布式ID方案

在分布式系统中,当数据库数据量达到一定量级的时候,需要进行数据拆分、分库分表操作,传统使用方式的数据库自有的自增特性产生的主键ID已不能满足拆分的需求,它只能保...

1142
来自专栏数据小魔方

MySQL数据库基础——本地文件交互

从这一篇开始,大概会花四五篇的内容篇幅,归纳整理一下之前学过的SQL数据库,一来可以为接下来数据分析工作提前巩固基础,二来把以前学的SQL内容系统化、结构化。 ...

33412
来自专栏文渊之博

SQL Server 2016 JSON原生支持实例说明

背景 Microsoft SQL Server 对于数据平台的开发者来说越来越友好。比如已经原生支持XML很多年了,在这个趋势下,如今也能在SQLServer2...

21010

扫码关注云+社区