--展现oracle数据库表间各种连接
--测试表
SQL> select * from test1;
ID NAME
---------- --------------------
1 yallonking
2 yallonking
3 yallonking
4 yallonking
SQL> select * from test2;
ID NAME
---------- --------------------
4 oraking
3 oraking
5 oraking
6 oraking
--左连接
SQL> select a.*,b.* from test1 a,test2 b where a.id=b.id(+);
ID NAME ID NAME
---------- -------------------- ---------- --------------------
4 yallonking 4 oraking
3 yallonking 3 oraking
1 yallonking
2 yallonking
SQL> select a.*,b.* from test1 a left join test2 b on a.id=b.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
4 yallonking 4 oraking
3 yallonking 3 oraking
1 yallonking
2 yallonking
--右连接
SQL> select a.*,b.* from test1 a,test2 b where a.id(+)=b.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
3 yallonking 3 oraking
4 yallonking 4 oraking
6 oraking
5 oraking
SQL> select a.*,b.* from test1 a right join test2 b on a.id=b.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
3 yallonking 3 oraking
4 yallonking 4 oraking
6 oraking
5 oraking
--等值连接
SQL> select a.*,b.* from test1 a,test2 b where a.id=b.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
4 yallonking 4 oraking
3 yallonking 3 oraking
SQL> select a.*,b.* from test1 a join test2 b on a.id=b.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
4 yallonking 4 oraking
3 yallonking 3 oraking
--笛卡尔积连接
SQL> select a.*,b.* from test1 a,test2 b;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 yallonking 4 oraking
1 yallonking 3 oraking
1 yallonking 5 oraking
1 yallonking 6 oraking
2 yallonking 4 oraking
2 yallonking 3 oraking
2 yallonking 5 oraking
2 yallonking 6 oraking
3 yallonking 4 oraking
3 yallonking 3 oraking
3 yallonking 5 oraking
ID NAME ID NAME
---------- -------------------- ---------- --------------------
3 yallonking 6 oraking
4 yallonking 4 oraking
4 yallonking 3 oraking
4 yallonking 5 oraking
4 yallonking 6 oraking
16 rows selected.
SQL> select a.*,b.* from test1 a join test2 b on 1=1;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 yallonking 4 oraking
1 yallonking 3 oraking
1 yallonking 5 oraking
1 yallonking 6 oraking
2 yallonking 4 oraking
2 yallonking 3 oraking
2 yallonking 5 oraking
2 yallonking 6 oraking
3 yallonking 4 oraking
3 yallonking 3 oraking
3 yallonking 5 oraking
ID NAME ID NAME
---------- -------------------- ---------- --------------------
3 yallonking 6 oraking
4 yallonking 4 oraking
4 yallonking 3 oraking
4 yallonking 5 oraking
4 yallonking 6 oraking
16 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26143577/viewspace-750804/,如需转载,请注明出处,否则将追究法律责任。