人大金仓 金仓数据库KingbaseES 新特性——DELETE操作目标支持子查询

金仓数据库KingbaseES 新特性——DELETE操作目标支持子查询

关键字：

KingbaseES、DML、Delete、With As

1.产品新特性

Oracle数据库DELETE语法在FROM子句中支持子查询（包括WITH AS语句），为了进一步提升兼容ORACLE的能力以及产品水平，我们在KingbaseES产品设计中增加兼容Oracle的语法，提供DELETE FROM子句支持子查询（包括with as）语句的功能。通过调研我们发现，Oracle DELETE语法FROM子句支持子查询（包括WITH AS子句），DELETE语句将删除子查询中返回的基表的结果行，不需要将整个数据表的内容全部删除，使得DELETE操作具有更大的灵活性，应用场景更加广泛。

2.Oracle中DELETE STATEMENT

Oracle的 DELETE STATEMENT语法图：

从语法图中FROM子句中的DML_table_expression_clause可以看出其可以分解为表、视图、子查询及table_collection；而对于子查询，oracle官方文档并没有详细的介绍使用的限制。为此，我们做了很多实际应用测试。

DELETE FROM单表的简单子查询，含有聚集函数avg，count，max，min，sum的子查询，投影列含有distinct操作符的子查询，多表inner join子查询，多表inner join子查询但连接列不是主键的子查询，多表inner join子查询包含group by和having子句的子查询，多表start with, connect by子句，多表left/right join子查询，多表集合操作子查询（Union/Intersect/Except/Minus）……同样的也在DELETE FROM WITH AS中做了相同的测试实验。

总结：

1，当子查询作为DELETE目标时，DELETE的目标是子查询中的基表；

2， 子查询投影列中不包含聚集函数（avg，count，max，min，sum等）；

3， 子查询投影列中distinct操作对象必须含有唯一约束（没有意义）；

4， 不支持group by，having，start with，connect by子句；

5，如果查询表达式是带有连接的查询，那么对于基表以外的表，连接列上必须是主键或者带有unique约束；

6，子查询不支持Union/Intersect/Except/Minus等集合查询。

在我们金仓数据库KingbaseES 产品中，对其进行语法层和功能层的兼容设计，目前已经实现ORACLE的这一功能特性。

3.金仓数据库KingbaseES的实现方式

DELETE FROM < sub_query >、DELETE FROM < with as clause >将分别删除sub_query、with as clause返回的基表的结果行。同时sub_query、with as clause需要满足以下条件：

1， 能唯一确定一个基表；

2， 不能包含以下字段：聚集函数（avg，count，max，min，sum等）；Distinct字段；带有group by，having，start with，connect by子句；Union/Intersect/Except/Minus等集合查询；其他分析函数。

3，with as clause不支持with recursive递归语句

sub_query语法格式：

DELETE FROM  <sub_query> [alias_name ]

语法示例：

子查询为单表：

delete from (select * from emp1 where id =1);

该语句将删除子查询中返回的emp1表中符合条件的列。

子查询为多表join：

//emp2表id列带有唯一约束,emp1表作为基表删除其返回列
delete from (select * from emp1 e1 left join emp2 e2 on e1.id = e2.id);
//emp1表id列带有唯一约束，emp2表作为基表删除其返回列
delete from (select * from emp1 e1 right join emp2 e2 on e1.id = e2.id);
//如果emp1和emp2在id列上都有唯一约束，则emp1表作为基表删除其返回列；否则将id列上不带唯一约束的表作为基表删除其返回列
delete from (select * from emp1 e1 inner join emp2 e2 on e1.id = e2.id);

with as clause语法格式：

 DELETE FROM  <with as clause> [alias_name ]

语法示例：

子查询为单表：

delete from (with to_delete as(select * from emp1 where id =1)select * from to_delete);

该语句将删除子查询中返回的emp1表中符合条件的列。

子查询为多表join：

//emp2表id列带有唯一约束,emp1表作为基表删除其返回列
delete from (with to_delete as(select * from emp1 e1 left join emp2 e2 on e1.id = e2.id) select * from to_delete);
//emp1表id列带有唯一约束，emp2表作为基表删除其返回列
delete from (with to_delete as(select * from emp1 e1 right join emp2 e2 on e1.id = e2.id) select * from to_delete);
//如果emp1和emp2在id列上都有唯一约束，则emp1表作为基表删除其返回列；否则将id列上不带唯一约束的表作为基表删除其返回列
delete from (with to_delete as (select * from emp1 e1 inner join emp2 e2 on e1.id = e2.id) select * from to_delete);

通过对DELETE FROM的语法进行扩充，使其支持子查询（包括with as）语句的功能。完美兼容了Oracle的语法格式，同时KingbaseES也在 DELETE语法上做了创新，在DELETE关键字前支持WITH AS子句，允许指定一个或多个子查询，在DELETE查询中可以用子查询的名字来引用它们，例如：

KingbaseES:
with to_delete as 
(select id from emp1 where grade like ‘A_%’)
delete from emp1 where emp1.id in (select id from to_delete);
此语句将删除emp1表中所有grade以’A_’开头的数据，但是语句只是引用to_delete查询，无法对to_delete直接进行操作

这极大丰富了KingbaseES 的语法，使得产品功能更加完善。金仓数据库将始终坚持创新，提升产品质量，引领国产数据库越好又快向前发展，努力成为世界卓越的数据库产品与服务提供商。 更多信息，参见https://help.kingbase.com.cn/v8/index.html