欺骗你的子查询
初始化场景先:
create table test_01( rn number);
create table test_02( rn number);
insert into test_01 values(1);
insert into test_01 values(2);
insert into test_01 values(3);
insert into test_01 values(4);
insert into test_02 values(1);
insert into test_02 values(2);
insert into test_02 values(3);
commit;
测试的SQL
select a.rn from test_01 a where a.rn in (select a.rn from test_02);
测试的结果:
SQL> select a.rn from test_01 a where a.rn in (select a.rn from test_02);
RN
----------
1
2
3
4
解析计划:
SELECT STATEMENT, GOAL = ALL_ROWS 5 4 52
FILTER
TABLE ACCESS FULL TAHTS TEST_01 3 4 52
FILTER
TABLE ACCESS FULL TAHTS TEST_02 2 3
产生的疑问:
子查询中 select a.rn from test_02 为啥不报错。
继续测试:
select a.rn from test_01 a where a.rn in (select a.rn from dual);
select a.rn from test_01 a where a.rn in (select rn from test_02 where rn=a.rn)
select a.rn from test_01 a where a.rn in (1,2,3);
执行上面3个SQL,SQL1居然是和疑问SQL的结果是一致的。后2个SQL结果为(1,2,3)
SQL2的解析计划:
SELECT STATEMENT, GOAL = ALL_ROWS 5 4 52
FILTER
TABLE ACCESS FULL TAHTS TEST_01 3 4 52
FILTER
FAST DUAL 2 1
也就是说和“子查询”中的表没有任何关系。
感谢“【话唠】深圳-Luke”的解释:
可以这样解析你的语句:
select a.rn from test_01 a 是外层循环,值分别是{1,2,3,4}
当值为1时,替换where子句为: where 1 in (select 1 from test_02);
当值为2时,替换where子句为: where 2 in (select 2 from test_02);
...
select a.rn from test_01 a 是外层循环,值分别是{1,2,3,4}
当值为1时,替换where子句为: where 1 in (select 1 from test_02);
当值为2时,替换where子句为: where 2 in (select 2 from test_02);
...
扩展:
但反过来就不行了,比如select a.rn from test_01 a where b.rn in (select rn from test_02 b);
可以理解为超出作用域
根据解析计划:
首先是变量table2,然后对table2的数据进行过滤。
所以扩展理解为:
当table2表有数据的时候:
select a.rn from test_01 a where a.rn in (a.rn);
结果永远是test_01表的数据。
当table2表没有有数据的时候:
select a.rn from test_01 a where a.rn in (null);
结果永远是空。