sql - 如何意外删除表中的所有行

Question

有人想尝试解释一下其机制...查询解析器的这个小怪癖今天几乎给我造成了重大损害。

创建一个100行的测试表，范围为1-100。

create table test( JobID int primary key);

;with numbers as (
    select 1 as n
    union all
    select n + 1 as n 
    from numbers
    where n < 100
)
insert into test
select n from numbers

创建一个临时表，其中包含整数 1-50:

select jobid as number into #deletions
from test 
where jobid <= 50

现在使用 IN 子句执行删除，但内部查询中的列名称错误:

delete from test where JobID in (select JobID from #deletions)

最后一个删除语句，从表面上看，看起来是删除 50 行......但是，#deletions 中没有 JobID，因此它会从外部查询中提取该信息，并最终以某种方式删除测试中的所有行。

我的问题是，它到底是如何解释内部查询的...#deletions 只有 50 行，那么它是如何从外表中提取所有 100 个 id 的呢？今天这种类型的打字错误几乎给我造成了重大伤害。

在我看来，这应该会引发某种解析/语法错误或某种歧义错误。

这是一个 SQL Fiddle Demo

Answer 1

如果使用表别名，逻辑就会清晰。您认为您正在写:

delete from test
    where test.JobID in (select d.JobID from #deletions d);

这是有道理的，但它会生成语法错误，因为 #deletions 中不存在 JobId。因此，SQL 的范围规则进入下一个级别来查找 JobId 并将查询解释为:

delete from test
    where test.JobID in (select test.JobID from #deletions d);

这将删除 JobId 的所有非 NULL 值。

道德:始终使用合格的列名称。