两张测试表的内容如下:
SQL> select * from yu_test1;
CL1_A CL2_A CL3_A
---------- ------------ -----
a fgg hhh
b jjj kkk
c eee rrrr
d aaa 444
SQL> select * from yu_test2;
CL1_B CL2_B CL3_B
---------- ------------ -----
b sdf tyu
d drt fds
e dcv hgf
f qwe erf
做左连接,可以看出以左边的数据为基准连接右表
SQL> select * from yu_test1 a left join yu_test2 b on (a.cl1_a=b.cl1_b);
CL1_A CL2_A CL3_A CL1_B CL2_B CL3_B
---------- ----------- ------- ---------- ------- -----
b jjj kkk b sdf tyu
d aaa 444 d drt fds
c eee rrrr
a fgg hhh
做右连接,可以看出以右边的数据为基准连接左表
SQL> select * from yu_test1 a right join yu_test2 b on (a.cl1_a=b.cl1_b);
CL1_A CL2_A CL3_A CL1_B CL2_B CL3_B
------- ------ ------- ------- ------ -----
b jjj kkk b sdf tyu
d aaa 444 d drt fds
f qwe erf
e dcv hgf
以下语句是反过来的,得到的数据也是反的
SQL> select * from yu_test2 a left join yu_test1 b on (a.cl1_b=b.cl1_a);
CL1_B CL2_B CL3_B CL1_A CL2_A CL3_A
----- ------- ------- ------ ------ -----
b sdf tyu b jjj kkk
d drt fds d aaa 444
f qwe erf
e dcv hgf
SQL> select * from yu_test2 a right join yu_test1 b on (a.cl1_b=b.cl1_a);
CL1_B CL2_B CL3_B CL1_A CL2_A CL3_A
------ ------- ------ ------ ------ -----
b sdf tyu b jjj kkk
d drt fds d aaa 444
c eee rrrr
a fgg hhh
用(+)代替left/right join,示例如下
看出在左边是属于右连接
SQL> select * from yu_test1 a , yu_test2 b where a.cl1_a(+)=b.cl1_b;
CL1_A CL2_A CL3_A CL1_B CL2_B CL3_B
------- ------ ------- ------- ------ -----
b jjj kkk b sdf tyu
d aaa 444 d drt fds
f qwe erf
e dcv hgf
在右边属于左连接
SQL> select * from yu_test1 a , yu_test2 b where a.cl1_a=b.cl1_b(+);
CL1_A CL2_A CL3_A CL1_B CL2_B CL3_B
---------- ----------- ------- ---------- ------- -----
b jjj kkk b sdf tyu
d aaa 444 d drt fds
c eee rrrr
a fgg hhh
内连接,其实我觉得和普通的=没啥区别了
SQL> select * from yu_test1 a inner join yu_test2 b on (a.cl1_a=b.cl1_b);
CL1_A CL2_A CL3_A CL1_B CL2_B CL3_B
------ ------ ------ ------ ------ -----
b jjj kkk b sdf tyu
d aaa 444 d drt fds