具有歧义列的SQL

Question

检查以下示例(Oracle语法):

create table t1 (id number, a varchar2(255));
create table t2 (id number, a varchar2(255));
create table t3 (id number);
select * from t1 where id in (select id from t2 where a = 'aa');
select * from t1 where id in (select id from t3 where a = 'aa');

两种选择都很好，但它们使用不同的属性进行过滤。在我看来，首先SQL应该给出一个错误，因为列a定义不明确。这不是在官方SQL标准中定义的吗?

Answer 1

这不是最清晰的语句，但文档确实提到了这种行为:

如果子查询中的列与包含语句中的列具有相同的名称，则必须在包含语句中使用表名或别名作为对表列的任何引用的前缀。为了使语句更易于阅读，请始终使用表、视图或物化视图的名称或别名来限定子查询中的列。

也就是说，当你的第一个语句引用t1.a时，你必须明确地使用它的名称(或别名):

select * from t1 where id in (select id from t2 where t1.a = 'aa');

如果你不这样做，那么它将默认使用子查询的表。它对解析器来说不是模棱两可的，因为它将首先查看(子)查询级别中的表，如果在当前级别中找不到该列，则只查看外部级别。这可能不是你所期望的，我同意抱怨它模棱两可会更好地避免导致错误结果的细微错误;但这就是它的工作方式。

但正如它所说，无论如何，总是显式的更安全:

select * from t1 where t1.id in (select t2.id from t2 where t2.a = 'aa');

或

select * from t1 where t1.id in (select t2.id from t2 where t1.a = 'aa');

第二个语句:

select * from t1 where t1.id in (select t3.id from t3 where t1.a = 'aa');

Answer 2

根据长期存在的编程原则，这种行为正是它应该有的样子。我没有见过SQL标准(显然它不能免费检查)，但在编程中，名称之间的"冲突"是最常见的问题之一，至少在某些情况下有非常简单的规则。这是其中之一:本地名称将始终掩盖可能存在于调用环境中的相同名称。

可悲的是，Oracle本身并不总是遵循这个简单的规则。在因子子查询中有一些与此相关的bug(在OTN上对此进行了一些讨论，导致向Oracle提交bug报告)。

Answer 3

第一个SQL。

select * from t1 where id in (select id from t2 where a = 'aa');

第一个查询将在执行查询时获取t2表的"a"列。正如我们所知，表t1和t2具有相同的列名，但首选项给了本地列。

第二SQL。

select * from t1 where id in (select id from t3 where a = 'aa');

在执行第二个查询时，它将从表t1中选择"a"列。

因为没有任何名为"a"的列，因此SQL将采用不同的属性来执行查询