Oracle的DELETE [FROM] (subquery)语法

数据库中的删除操作通常是直接指定表名进行操作，但Oracle数据库支持一种特别的DELETE [FROM] (subquery)语法，使用户能够通过子查询指定删除操作，其它任何数据库均不支持，详细了解之后不由感叹Oracle的强大。

1，支持情况

Oracle对 DELETE [FROM] (subquery) 语法的支持包括以下几种情形：

单表子查询：单表子查询的返回结果可以直接用于DELETE操作。
多表JOIN子查询：支持基于包含多表JOIN的子查询进行DELETE。
可修改视图：可修改视图也相当于一种子查询，同样支持。

但无论是哪种情况，子查询或可修改视图中必须存在一个“键值保存表”（Key-Preserved Table），键值保存表也是实现该语法的关键。

什么是键值保存表

在一个多表JOIN的查询中，如果某个表的主键或唯一键被投影，且在JOIN结果中依旧保持唯一性，也就是说，JOIN操作不会破坏该键的唯一性，这个表在这个查询中便被称为键值保存表。

2，Oracle行为分析

Oracle在执行 DELETE [FROM] (subquery) 时会根据子查询的结构确定删除目标，这其中存在一些有趣的现象。

单表子查询行为

如果子查询中仅包含一个表，Oracle允许删除操作，即使该表没有唯一键。例如：

CREATE TABLE A (id int);

INSERT INTO A VALUES (1);

DELETE (SELECT * FROM A WHERE id = 1);

多表JOIN子查询的复杂行为

当 DELETE [FROM] (subquery) 包含多表JOIN时，行为变得复杂。例如：

CREATE TABLE A (id int PRIMARY KEY);
CREATE TABLE B (id int, price int);

INSERT INTO A VALUES (1);
INSERT INTO B VALUES (1,1);

DELETE (SELECT * FROM A, B WHERE A.id = B.id);

在该例中，如果 A.id是主键，而 B 表没有，则 DELETE 语句会删除 B 表中的数据。如果 B.id是主键，而 A 表没有，则 DELETE 语句会删除 A 表中的数据。若 A.id 和 B.id 都是主键，Oracle会选择语句中顺序居前的键值保存表进行删除。

这里非常反直觉，不是说会删除“键值保存表”上的数据吗？为什么反而是另外一个没有主键的表数据会被删除？其实也合理：因为在JOIN的结果中，唯一键的值可能出现多次，所以那个表并不是“键值保存表”，而匹配到的多个相同值却保留了一个隐藏的唯一键，即Oracle表的ROWID，其所在的表可以视为键值保存表。

比如在如下例子中，A表有主键，且主键在JOIN之后保留，则A为键值保存表，数据就是从A中删除：

CREATE TABLE A (id int PRIMARY KEY);
INSERT INTO A VALUES (1);
INSERT INTO A VALUES (2);

CREATE TABLE B (id int, price int);
INSERT INTO B VALUES (1,1);
INSERT INTO B VALUES (1,1);

DELETE (SELECT A.id FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.id = B.id));

3，Oracle的可能实现原理

Oracle对 DELETE [FROM] (subquery) 的实现依赖于键值保存表来避免歧义，其可能的执行步骤如下：

首先语法分析确定子查询或视图中是否存在键值保存表。如果有多个键值保存表，则选择第一个；没有时，引入 ROWID 作为唯一键，此时只能存在一个键值保存表（无唯一键的复杂单表子查询可能也是如此）。
执行子查询，并从选中的键值保存表中删除子查询选中的行。

一点感慨

说起来有点复杂，做起来其实也并不简单，多表中对“键值保存表”的判断其实是非常复杂的逻辑，一些视图比如USER_UPDATABLE_COLUMNS也与此相关，只能感慨Oracle还是太全面了。