本来对oracle中的左右连接已经非常熟悉，但是看到项目中写的各式各样的连接自己也迷惑了(+﹏+)~。摘出来，希望火眼金睛o(≧v≦)o~~的朋友指点一二。先谢谢了O(∩_∩)O。为了容易理解，简化了实际复杂的sql,但结构类似问题1： A文(项目使用的）： select AA.x,BB.y,CC.z from AA,BB,CC where AA.id1 = BB.id1(+) and AA.id2 = BB.id2(+) and BB.id1 = CC.id1(+) and BB.id2 = CC.id2(+) and AA.m = 'test' B文： select AA.x,BB.y,CC.z from AA,BB,CC where AA.id1 = BB.id1(+) and AA.id2 = BB.id2(+) and AA.id1 = CC.id1(+) and AA.id2 = CC.id2(+) and AA.m = 'test'sql本身想以AA表作为主表查询,但在A文中偷换了主表，B文似乎没什么悬念。想请教的是A文中移花接木的做法会产生问题么？问题2： C文（项目使用的）： select AA.x,BB.y,CC.z from AA left join BB on AA.id1 = BB.id1 and AA.id2 = BB.id2 left join CC on BB.id1 = CC.id1 and BB.id2 = CC.id2 where AA.m = ‘test’ D文： select AA.x,BB.y,CC.z from AA left join BB on AA.id1 = BB.id1 and AA.id2 = BB.id2 left join CC on AA.id1 = CC.id1 and AA.id2 = CC.id2 where AA.m = ‘test’ 也是AA表为主表，问题同上面的，C文的写法与D文相比会差生查询结果上的差异么？
如果觉得A文与C文是一样sql的不用写法，费眼回答一个就行。
如果觉得A文与C文是一样sql的不用写法，费眼回答一个就行。
AA.id1 = BB.id1 andAA.id2 = BB.id2BB.id1 = CC.id1 andBB.id2 = CC.id2D:
AA.id1 = BB.id1 andAA.id2 = BB.id2AA.id1 = CC.id1 andAA.id2 = CC.id2
C和D的连接条件不一样。
除非A，B，C三表对于id1,id2是一一关联，那么上述语句的结果才能一样。
BB表和CC表是一一关联。AA表是主表。
c 跟d的左连接跟cc表的主表不一样怎么可能结果一样.
c:
left join CC on BB.id1 = CC.id1 and BB.id2 = CC.id2d:left join CC on AA.id1 = CC.id1 and AA.id2 = CC.id2
SQL> select * from aa,bb,cc
2 where aa.id1=bb.id1(+) and bb.id1=cc.id1(+) and aa.m='test';
ID1 X M ID1 Y ID1 Z
----------- ---------- ---------- ----------- ---------- ----------- ----------
2 b test 2 b 2 b
1 a test
5 e test
4 d test 4 d
3 c test 3 c
SQL>
SQL> select * from aa left join bb on aa.id1=bb.id1 and aa.m='test'
2 left join cc on bb.id1=cc.id1
3 ;
ID1 X M ID1 Y ID1 Z
----------- ---------- ---------- ----------- ---------- ----------- ----------
2 b test 2 b 2 b
5 e test
1 a test
4 d test 4 d
3 c test 3 c
SQL> insert into aa select * from aa;
5 rows inserted
SQL> insert into bb select * from bb
2 ;
5 rows inserted
SQL> insert into cc select * from cc;
2 rows inserted
SQL>
SQL> select * from aa,bb,cc
2 where aa.id1=bb.id1(+) and bb.id1=cc.id1(+) and aa.m='test';
ID1 X M ID1 Y ID1 Z
----------- ---------- ---------- ----------- ---------- ----------- ----------
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
1 a test
1 a test
5 e test
5 e test
3 c test 3 c
3 c test 3 c
3 c test 3 c
3 c test 3 c
4 d test 4 d
4 d test 4 d
4 d test 4 d
4 d test 4 d
20 rows selected
SQL>
SQL> select * from aa left join bb on aa.id1=bb.id1 and aa.m='test'
2 left join cc on bb.id1=cc.id1
3 ;
ID1 X M ID1 Y ID1 Z
----------- ---------- ---------- ----------- ---------- ----------- ----------
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
5 e test
1 a test
5 e test
1 a test
3 c test 3 c
3 c test 3 c
3 c test 3 c
3 c test 3 c
4 d test 4 d
4 d test 4 d
4 d test 4 d
4 d test 4 d
20 rows selected
SQL>
SQL> select * from aa left join bb on aa.id1=bb.id1 and aa.m='test'
2 left join cc on aa.id1=cc.id1;
ID1 X M ID1 Y ID1 Z
----------- ---------- ---------- ----------- ---------- ----------- ----------
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
2 b test 2 b 2 b
5 e test
5 e test
3 c test 3 c
3 c test 3 c
3 c test 3 c
3 c test 3 c
4 d test 4 d
4 d test 4 d
4 d test 4 d
4 d test 4 d
1 a test
1 a test
20 rows selected
SQL>
--我也测试了下，A文和C文一样，B文和D文一样
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> select * from aa; ID1 ID2 X
---------- ---------- --------------------
1 1 a
2 2 aa
3 3 aaaSQL> select * from bb; ID1 ID2 Y
---------- ---------- --------------------
1 1 b
2 2 bbSQL> select * from cc; ID1 ID2 Z
---------- ---------- --------------------
1 1 c--A文
SQL> edit
Wrote file afiedt.buf 1 select AA.x,BB.y,CC.z
2 from AA,BB,CC
3 where AA.id1 = BB.id1(+) and
4 AA.id2 = BB.id2(+) and
5 BB.id1 = CC.id1(+) and
6* BB.id2 = CC.id2(+)
SQL> /X Y Z
-------------------- -------------------- --------------------
a b c
aaa
aa bb--B文
SQL> edit
Wrote file afiedt.buf 1 select AA.x,BB.y,CC.z
2 from AA,BB,CC
3 where AA.id1 = BB.id1(+) and
4 AA.id2 = BB.id2(+) and
5 AA.id1 = CC.id1(+) and
6* AA.id2 = CC.id2(+)
SQL> /X Y Z
-------------------- -------------------- --------------------
a b c
aa bb
aaa--C文
SQL> edit
Wrote file afiedt.buf 1 select AA.x,BB.y,CC.z
2 from AA
3 left join BB on
4 AA.id1 = BB.id1 and
5 AA.id2 = BB.id2
6 left join CC on
7 BB.id1 = CC.id1 and
8* BB.id2 = CC.id2
SQL> /X Y Z
-------------------- -------------------- --------------------
a b c
aaa
aa bb--D文
SQL> edit
Wrote file afiedt.buf 1 select AA.x,BB.y,CC.z
2 from AA
3 left join BB on
4 AA.id1 = BB.id1 and
5 AA.id2 = BB.id2
6 left join CC on
7 AA.id1 = CC.id1 and
8* AA.id2 = CC.id2
SQL> /X Y Z
-------------------- -------------------- --------------------
a b c
aa bb
aaaSQL>
看来A和C的写法都没有问题，这下放心了o(≧v≦)o~~
谢谢关注帖子的朋友