oracle 各種表間連線
--展現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.
--測試表
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle各種表空間Oracle
- 各種表連線方式對比分析
- Oracle的三種表連線方式Oracle
- Oracle 的各種表Oracle
- 各種連線資料庫的連線字串資料庫字串
- 各種資料庫連線資料庫
- Oracle 表連線Oracle
- oracle 當前資料庫各種版本時間釋出表Oracle資料庫
- ORACLE 表連線方式Oracle
- oracle表連線方式Oracle
- 【SQL】表連線七種方式SQL
- JDBC連線各種資料庫的字串JDBC資料庫字串
- jdbc獲取各種資料庫連線JDBC資料庫
- .NET中各種資料庫連線大全資料庫
- Java中各種線性表的效能分析Java
- Oracle的表連線方法(三)雜湊連線Oracle
- JDBC 連線oracle三種方法JDBCOracle
- Oracle 表連線方式詳解(外連結、內連線、自連線)Oracle
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解
- 各種資料庫連線程式碼(JSP)資料庫線程JS
- .NET中各種資料庫連線大全 (轉)資料庫
- 幾種表的連線方式(SQL)SQL
- Oracle的表連線方法(七)Oracle
- Oracle的表連線方法(五)Oracle
- Oracle的表連線方法(六)Oracle
- Oracle的表連線方法(一)排序合併連線Oracle排序
- Oracle表連線操作——Hash Join(雜湊連線)下Oracle
- Oracle表連線操作——Hash Join(雜湊連線)上Oracle
- 表與表之間連線式on,using以及自然連線的區別
- oracle sql內連線_左(右)連線_全外連線_幾種寫法OracleSQL
- JDBC連線各種資料庫的方法(經典)JDBC資料庫
- 【轉載】JDBC連線各種資料庫的字串JDBC資料庫字串
- jdbc獲取對各種資料庫的連線JDBC資料庫
- Java連線各種資料庫的例項 (轉)Java資料庫
- 3種主要表連線方式對比
- 淺談mysql中各種表空間(tablespaces)的概念MySql
- Oracle的表連線方法(二)巢狀迴圈連線Oracle巢狀
- Oracle 表連線方式分析(精讀)Oracle