in子查询里居然能引用外表字段

Question

in子查询里居然能引用外表字段
select count(1) from emp 
where job in(select job from dept  );  COUNT(1)
----------
        14今天在写sql时遇到以上类似的情况，子查询里的dept表本来没有jop字段
但这样写居然没报错，原来是如果in子查询里字段在内表找不到就会去引用外表的想问in子查询引用外表字段有什么用？
在什么情况下能发挥他的作用
要不然就感觉只会被迷糊误导

Answer 1

select deptno,ename,job,sal
from emp
where deptno in (10,30);

Answer 2

子查询引用外层查询的列是正常的，只不过一般不在in子查询中引用外层查询的列。
但是在exists,not exists子查询中用得比较多，
如：查询不存在雇员的部门
--使用not exists（引用外层查询列d.deptno进行关联）
SELECT *
  FROM dept d
 WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno;--使用in（不使用外层查询列）
SELECT * FROM dept d WHERE d.deptno NOT IN (SELECT e.deptno FROM emp e);

Answer 3

如果大家都没使用这种情况
应该可以说是一个bug

Answer 4

觉得使用表前缀才是硬道理以下是在itpub发帖收到的答复，大家可以参考
http://www.itpub.net/viewthread.php?tid=1429387&pid=17701846&page=1&extra=page%3D1#pid17701846--1.子表引用父表列，而自己没有，在子表有数据的情况下，返回所有非空键的父表记录，子表为空，则结果无
--2.子表引用父表属性，只有最外层子查询才能引用
--3.有前缀标识，按前缀，如果子表父表前缀一样，按4的规则
--4.如果无前缀标识唯一性，子查询表也有此字段，那么以局部子查询为准
    如果前缀一样，子查询存在此字段，则以子查询表为准，否则以父表的为准
dingjun123@ORADB> drop table a;表已删除。已用时间:  00: 00: 01.07
dingjun123@ORADB> create table a as select level id from dual connect by level<3;表已创建。已用时间:  00: 00: 00.28
dingjun123@ORADB> drop table b;表已删除。已用时间:  00: 00: 00.06
dingjun123@ORADB> create table b(x number);表已创建。已用时间:  00: 00: 00.10
dingjun123@ORADB> select * from a where id in (select id from b);未选定行已用时间:  00: 00: 00.03
dingjun123@ORADB> insert into b values(5);已创建 1 行。已用时间:  00: 00: 00.01
dingjun123@ORADB> select * from a where id in (select id from b);        ID
----------
         1
         2已选择2行。已用时间:  00: 00: 00.01dingjun123@ORADB> set autot on exp
dingjun123@ORADB> select * from a where id in (select id from b);        ID
----------
         1
         2已选择2行。已用时间:  00: 00: 00.00执行计划
----------------------------------------------------------
Plan hash value: 1043214102----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |    26 |     6   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | A    |     2 |    26 |     3   (0)| 00:00:01 |
|*  3 |   FILTER            |      |       |       |            |          |
|   4 |    TABLE ACCESS FULL| B    |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE :B1=:B2))
   3 - filter(:B1=:B2)Note
-----
   - dynamic sampling used for this statement (level=2)
--a.id为空的不返回
dingjun123@ORADB> insert into a values(null);
已创建 1 行。
已用时间:  00: 00: 00.00
dingjun123@ORADB> select * from a where id in (select id from b);
        ID
----------
         1
         2
已选择2行。
已用时间:  00: 00: 00.00--只有最外层才能引用
dingjun123@ORAD> select * from a where id in (select 1 from (select id from b));
select * from a where id in (select 1 from (select id from b))
                                                   *
第 1 行出现错误:
ORA-00904: "ID": 标识符无效已用时间:  00: 00: 00.01--有前缀按照前缀规则
dingjun123@ORADB> select * from a where id in (select b.id from b);
select * from a where id in (select b.id from b)
                                    *
第 1 行出现错误:
ORA-00904: "B"."ID": 标识符无效
已用时间:  00: 00: 00.01--子表无，前缀一样同，同开始，找非空的a.id所有记录
dingjun123@ORADB> select * from a b where id in (select b.id from b);        ID
----------
         1
         2已选择2行。已用时间:  00: 00: 00.01--无前缀标识唯一性，以局部子查询的为准
dingjun123@ORADB> alter table b add id number;表已更改。已用时间:  00: 00: 00.06
dingjun123@ORADB> select * from a where id in (select id from b);未选定行已用时间:  00: 00: 00.01--列一样，前缀一样，局部为准
dingjun123@ORADB> select * from a b where id in (select b.id from b);未选定行已用时间:  00: 00: 00.01

Answer 5

只有字段有重复的情况,表前缀才是必需的.
也不能叫bug,如果子查询内部不支持外表的引用,那么关联子查询如何实现?

Answer 6

关联子查询平时都是用exists
in子查询里使用还真么用过
现在就是希望有经验的提供实用的例子

Answer 7

外层表的列在子查询中使用是很正常的
不过我一般在where条件中使用，从不在select后使用
可以定义为使用问题吧
提示报错也不合适吧
一般的程序处理时 都是外层变量在内层是有生命的 只能被重载 但不会消失 oracle估计也想遵守这个规则吧

Answer 8

呵呵，那说明你用的sql还比较少。去看看oracle原厂的sql培训教程，只看关联子查询部分就成

Answer 9

多数情况下子查询引用外表都是用作条件过滤或 case 之类的吧，像那样直接  select job from dept 这样的好像没什么意义啊。