SQL 查询中列名的选择

今天遇到一个小问题，我发出这样一个查询，居然产生执行结果：

 

select * from test_yct where id_new in( select id_new from test_yct_new );

 

很普通的 SQL，问题是在表 test_yct_new 中并没有列 id_new。

 

详细讨论见 ITPUB，这里再重新测试一下：

 

 

SQL> desc test_yct
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------
 ID                                                 VARCHAR2(13)
 NAME                                               VARCHAR2(20)
 ID_NEW                                             VARCHAR2(10)

 

SQL> desc test_yct_new
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------
 ID_NEW                                             VARCHAR2(10)

 NAME                                               VARCHAR2(20)

 

SQL> select * from test_yct;

 

ID            NAME                 ID_NEW
------------- -------------------- ------
1             yuechaotian1         01
2             yuechaotian2         02
3             yuechaotian3         03
4             yuechaotian4         04
5             yuechaotian5         05
6             yuechaotian6         06
7             yuechaotian7         07
8             yuechaotian8         08
9             yuechaotian9         09
10            yuechaotian10        10

 

已选择10行。

 

SQL> select * from test_yct_new;

 

ID_NEW     NAME
---------- --------------------
04         yuechaotian1
05         yuechaotian2
06         yuechaotian3
07         yuechaotian4

 

我先做一个普通的查询，查询结果显而易见：

 

SQL> select * from test_yct where id_new in( select id_new from test_yct_new );

 

ID            NAME                 ID_NEW
------------- -------------------- ----------
4             yuechaotian4         04
5             yuechaotian5         05
6             yuechaotian6         06
7             yuechaotian7         07

 

那么如果我把表 test_yct_new 中的列 id_new 去掉，再次执行这个查询语句呢：

 

SQL> alter table test_yct_new drop column id_new;

 

表已更改。

 

SQL> desc test_yct_new
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------
 NAME                                               VARCHAR2(20)

 

SQL> select * from test_yct where id_new in( select id_new from test_yct_new );

 

ID            NAME                 ID_NEW
------------- -------------------- ----------
1             yuechaotian1         01
2             yuechaotian2         02
3             yuechaotian3         03
4             yuechaotian4         04
5             yuechaotian5         05
6             yuechaotian6         06
7             yuechaotian7         07
8             yuechaotian8         08
9             yuechaotian9         09
10            yuechaotian10        10

 

已选择10行。

 

此时已经没有列 test_yct_new.id_new 了，但这个查询仍可以执行。因为 Oracle 把它转换成了下面的SQL：

 

SQL> select * from test_yct where id_new in( select test_yct.id_new from test_yct_new );

 

ID            NAME                 ID_NEW
------------- -------------------- ----------
1             yuechaotian1         01
2             yuechaotian2         02
3             yuechaotian3         03
4             yuechaotian4         04
5             yuechaotian5         05
6             yuechaotian6         06
7             yuechaotian7         07
8             yuechaotian8         08
9             yuechaotian9         09
10            yuechaotian10        10

 

已选择10行。

 

所以，对于括号内的列名（id_new），Oracle 会先从括号内的表（test_yct_new）中查找，如果找到了，就以此为准；如果查找不到该列名，则从括号外面的表（test_yct）中查找。如果都查找不到，才会提示错误：

 

ORA-00904: "ID_NEW": 无效的标识符

 

再用 test_yct.id 来重复一次这个实验：

 

SQL> select * from test_yct where id_new in( select id from test_yct_new );

 

ID            NAME                 ID_NEW
------------- -------------------- ----------
10            yuechaotian10        10

 

SQL> select * from test_yct where id_new in( select test_yct.id from test_yct_new );

 

ID            NAME                 ID_NEW
------------- -------------------- ----------
10            yuechaotian10        10