然后我执行下面的查询,Oracle不会报”ORA-00918: 未明确定义列“,而是直接将where子句中的USERFLAG认为是TEST3的字段。这是不是Oracle的Bug啊?
SQL> SELECT A.ID AID, B.ID, A.USERFLAG AU, B.USERFLAG BU, C.USERFLAG CU
2 FROM TEST1 A
3 inner JOIN TEST2 B ON A.ID=B.ID
4 inner JOIN TEST3 C ON B.ID=C.ID
5 WHERE USERFLAG = 1
6 /
AID ID AU BU CU
---------------- ---------------- -- -- --
3 3 0 0 1
由 hutuboy
我想WHERE USERFLAG = 1 没加表名,就会默认最后一个表.只是这样认为的,做了如下个实验, 高手指点.
SQL> select * from test1;
ID
ID2
USERING
--------------------------------------- --------------------------------------- ---------------------------------------
1
5
1
3
2
1
SQL> select * from test2;
ID
ID2
USERING
--------------------------------------- --------------------------------------- ---------------------------------------
1
6
1
3
3
0
SQL> select * from test3;
ID
ID2
USERING
--------------------------------------- --------------------------------------- ---------------------------------------
1
2
0
3
3
1
SQL>
SQL> SELECT A.id, A.id2, B.id,b.id2,c.id,c.id2, A.usering AU, B.usering BU, C.usering CU
2FROM TEST1 A
3inner JOIN TEST3 C ON a.ID=C.ID
4inner JOIN TEST2 B ON A.ID=B.ID
5WHERE usering = 1;
ID
ID2
ID
ID2
ID
ID2
AU
BU
CU
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1
5
1
6
1
2
1
1
0
SQL>
SQL> SELECT A.id, A.id2, B.id,b.id2,c.id,c.id2, A.usering AU, B.usering BU, C.usering CU
2FROM TEST1 A
3inner JOIN TEST2 b ON a.ID=b.ID
4inner JOIN TEST3 c ON A.ID=c.ID
5WHERE usering = 1;
ID
ID2
ID
ID2
ID
ID2
AU
BU
CU
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
3
2
3
3
3
3
1
0
1
SQL>
SQL> SELECT A.id, A.id2, B.id,b.id2,c.id,c.id2, A.usering AU, B.usering BU, C.usering CU
2FROM TEST3 c
3inner JOIN TEST2 b ON c.ID=b.ID
4inner JOIN TEST1 a ON A.ID=c.ID
5WHERE usering = 1;
ID
ID2
ID
ID2
ID
ID2
AU
BU
CU
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1
5
1
6
1
2
1
1
0
3
2
3
3
3
3
1
0
1
|