子查询语法检查问题请教

sys

问题：不理解子查询是怎样执行的？
其中x表含id/name字段，y表含name字段，z表含id/name字段

server信息：

1. [oracle@dbalinux ~]$ uname -a
   
2. Linux dbalinux.net 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:37:40 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

复制代码

1. SQL> select * from v$version;
   
2. BANNER
   
3. ----------------------------------------------------------------
   
4. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
   
5. PL/SQL Release 10.2.0.1.0 - Production
   
6. CORE        10.2.0.1.0        Production
   
7. TNS for Linux: Version 10.2.0.1.0 - Production
   
8. NLSRTL Version 10.2.0.1.0 - Production

复制代码

坏境模拟：

1. SQL> desc x;
   
2. Name                                           Null?    Type
   
3. ----------------------------------------- -------- ----------------------------
   
4. ID                                                    NUMBER
   
5. NAME                                                    VARCHAR2(20)
   
6. 
   
7. SQL> desc y;
   
8. Name                                           Null?    Type
   
9. ----------------------------------------- -------- ----------------------------
   
10. NAME                                                    VARCHAR2(20)
    
11. 
    
12. SQL> desc z;
    
13. Name                                           Null?    Type
    
14. ----------------------------------------- -------- ----------------------------
    
15. ID                                                    NUMBER
    
16. NAME                                                    VARCHAR2(20)

复制代码

1. SQL> select * from x;
   
2. 
   
3.         ID NAME
   
4. ---------- --------------------
   
5.          1 clark
   
6.          2 dave
   
7.          3 ben
   
8. 
   
9. SQL> select * from y;
   
10. 
    
11. NAME
    
12. --------------------
    
13. scott
    
14. clark
    
15. 
    
16. SQL> select * from z;
    
17. 
    
18.         ID NAME
    
19. ---------- --------------------
    
20.          1 clark
    
21.          2 dave
    
22.          4 mark

复制代码

1. SQL> select * from x where x.id in(select id from y);
   
2. 
   
3.         ID NAME
   
4. ---------- --------------------
   
5.          1 clark
   
6.          2 dave
   
7.          3 ben
   
8. 
   
9. SQL> select * from x where x.id in(select id from z);
   
10. 
    
11.         ID NAME
    
12. ---------- --------------------
    
13.          1 clark
    
14.          2 dave
    
15. 
    
16. SQL> select * from x where x.id in(select y.id from y);
    
17. select * from x where x.id in(select y.id from y)
    
18.                                      *
    
19. ERROR at line 1:
    
20. ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y'
    
21. 
    
22. 
    
23. SQL> select * from x where x.id in(select z.id from z);
    
24. 
    
25.         ID NAME
    
26. ---------- --------------------
    
27.          1 clark
    
28.          2 dave

复制代码

1. SQL> select * from x where x.id in(select idd from y);
   
2. select * from x where x.id in(select idd from y)
   
3.                                      *
   
4. ERROR at line 1:
   
5. ORA-00904: "IDD": invalid identifier
   
6. 
   
7. 
   
8. SQL> select * from x where x.id in(select idd from z);
   
9. select * from x where x.id in(select idd from z)
   
10.                                      *
    
11. ERROR at line 1:
    
12. ORA-00904: "IDD": invalid identifier

复制代码

劳烦各位大牛给解答一下为什么select * from x where x.id in(select id from y);的语法检查可以过去？

Liu Maclean(刘相兵

1. 
   
2. SQL> create table x(id int,name varchar2(200));
   
3. 
   
4. Table created.
   
5. 
   
6. SQL> create table y(name varchar2(200));
   
7. 
   
8. Table created.
   
9. 
   
10. 
    
11. 
    
12. SQL> select * from x where x.id  in (select id from y);
    
13. 
    
14. no rows selected
    
15. 
    
16. SQL> alter system flush shared_pool;      
    
17. 
    
18. System altered.
    
19. 
    
20. SQL> analyze table x compute statistics;
    
21. 
    
22. Table analyzed.
    
23. 
    
24. SQL> analyze table y compute statistics;
    
25. 
    
26. Table analyzed.
    
27. 
    
28. SQL> alter session set events '10053 trace name context forever,level 1';
    
29. 
    
30. Session altered.
    
31. 
    
32. SQL> select * from x where x.id  in (select id from y);
    
33. 
    
34. no rows selected
    
35. 
    
36. SQL> oradebug setmypid
    
37. Statement processed.
    
38. SQL> oradebug tracefile_name
    
39. /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_21249.trc
    
40. 
    
41. 
    
42. 
    
43. 
    
44. 
    
45. 
    
46. 
    
47. 
    
48. 
    
49. 
    
50. 
    
51. 
    
52. 
    
53. 
    
54. JPPD: Applying transformation directives
    
55. query block SEL$1 (#1) unchanged
    
56. FPD: Considering simple filter push in query block SEL$1 (#1)
    
57. EXISTS (SELECT 0 FROM "Y" "Y")
    
58. FPD: Considering simple filter push in query block SEL$2 (#2)
    
59. :B1=:B2
    
60. try to generate transitive predicate from check constraints for query block SEL$2 (#2)
    
61. finally: :B1=:B2
    
62. 
    
63. try to generate transitive predicate from check constraints for query block SEL$1 (#1)
    
64. finally:  EXISTS (SELECT 0 FROM "Y" "Y")
    
65. 
    
66. Final query after transformations:******* UNPARSED QUERY IS *******
    
67. 
    
68. 
    
69. SELECT "X"."ID" "ID", "X"."NAME" "NAME"
    
70.   FROM "SYS"."X" "X"
    
71. WHERE EXISTS (SELECT 0 FROM "SYS"."Y" "Y" WHERE "X"."ID" = "X"."ID")
    
72. 
    
73. 
    
74. 
    
75. 
    
76. 
    
77. 
    
78. 
    
79. 
    
80. 
    
81. 
    
82. 
    
83. 10053 后可以看到
    
84. 
    
85. select * from x where x.id  in (select id from y);  实际转换为
    
86. 
    
87. 
    
88. SELECT "X"."ID" "ID", "X"."NAME" "NAME"
    
89.   FROM "SYS"."X" "X"
    
90. WHERE EXISTS (SELECT 0 FROM "SYS"."Y" "Y" WHERE "X"."ID" = "X"."ID")
    

复制代码

即Oracle实际把select id from y这里的ID 理解为 X.ID，因为这个字段标示 仅在X表上有， 当指定Y.ID时因为实际Y表上不存在该字段所以报        ORA-00904错误

sys

自己查的一些资料，Oracle® Database SQL Reference  10 g Release 2 (10.2)  B14200-02
If columns in a subquery have the same name as columns in the containing statement,
then you must prefix any reference to the  column of the table from the containing
statement with the table name or alias. To make your statements easier to read, always
qualify the columns in a subquery with the name or alias of the table, view, or
materialized view.