多表的連線
多表連線：
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
7890 zxw clerk 7902 11-NOV-80 900 100
7892 test clerk 7906 22-NOV-82 860 50
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
16 rows selected.
1.笛卡爾連線：
SQL> select count(1) from dept ,emp;
COUNT(1)
----------
64
這個資料沒任何意議，沒有連線條件；
2.內連線；
等值關係：
做多表連線，一定要找關聯關係，主外來鍵找關聯關係；
SQL> select e.ename,e.deptno,d.dname from emp e,dept d
2 where e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
FORD 20 RESEARCH
SCOTT 20 RESEARCH
ADAMS 20 RESEARCH
JONES 20 RESEARCH
SMITH 20 RESEARCH
TURNER 30 SALES
MARTIN 30 SALES
WARD 30 SALES
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
ALLEN 30 SALES
BLAKE 30 SALES
JAMES 30 SALES
14 rows selected.
----內連線，標準的SQL,支援所有的SQL
SQL> select e.ename,e.deptno,d.dname from emp e,dept d
2 where e.deptno=d.deptno;
也可寫成：
SQL> select e.ename,e.deptno,d.dname
2 from emp e inner join dept d on e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
FORD 20 RESEARCH
SCOTT 20 RESEARCH
ADAMS 20 RESEARCH
JONES 20 RESEARCH
SMITH 20 RESEARCH
TURNER 30 SALES
MARTIN 30 SALES
WARD 30 SALES
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
ALLEN 30 SALES
BLAKE 30 SALES
JAMES 30 SALES
14 rows selected.
3.外連線；
在外連線中，某些不滿足條件的列也會顯示出來。
也就是說，要限制其中一個表的行，而不是限制另一個表的行。
這種連線形式在許多情況下是非常有用的。
在外連線中又分左連線和右連線
A.左連線：
ORACLE的寫法：+號右邊，以左邊為準，右邊補齊。
左連線+在右邊，左邊的是主表，左邊的表記錄 全部顯示，
如果左邊的表有記錄，右邊的表沒有找到記錄，則補NULL.
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
2 from emp e, dept d
3 where e.deptno=d.deptno(+);
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
from emp e left outer join dept d
on e.deptno=d.deptno;
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
JAMES CLERK 950 30 SALES CHICAGO
TURNER SALESMAN 1500 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MARTIN SALESMAN 1250 30 SALES CHICAGO
WARD SALESMAN 1250 30 SALES CHICAGO
ALLEN SALESMAN 1600 30 SALES CHICAGO
test clerk 860 ----------多的
zxw clerk 900 ---------多的
16 rows selected.
B.右連線：
ORACLE的寫法：+號左邊，以右邊為準，左邊補齊。
右連線+在左邊，右邊的是主表，右邊的表記錄 全部顯示，
如果右邊的表有記錄，左邊的表沒有找到記錄，則補NULL.
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
from emp e , dept d
where e.deptno(+)=d.deptno;
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
from emp e right outer join dept d
on e.deptno=d.deptno;
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
TURNER SALESMAN 1500 30 SALES CHICAGO
MARTIN SALESMAN 1250 30 SALES CHICAGO
WARD SALESMAN 1250 30 SALES CHICAGO
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
ALLEN SALESMAN 1600 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
JAMES CLERK 950 30 SALES CHICAGO
多的 ---------- 40 OPERATIONS BOSTON
15 rows selected.
C.自連線，左右都顯示：
select e.ename, e.job,e.sal，d.deptno,d.dname,d.loc
from emp e full outer join dept d
on e.deptno=d.deptno;
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
2 from emp e full outer join dept d
3 on e.deptno=d.deptno;
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
JAMES CLERK 950 30 SALES CHICAGO
TURNER SALESMAN 1500 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MARTIN SALESMAN 1250 30 SALES CHICAGO
WARD SALESMAN 1250 30 SALES CHICAGO
ALLEN SALESMAN 1600 30 SALES CHICAGO
test clerk 860
zxw clerk 900
40 OPERATIONS BOSTON
17 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
7890 zxw clerk 7902 11-NOV-80 900 100
7892 test clerk 7906 22-NOV-82 860 50
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
16 rows selected.
1.笛卡爾連線：
SQL> select count(1) from dept ,emp;
COUNT(1)
----------
64
這個資料沒任何意議，沒有連線條件；
2.內連線；
等值關係：
做多表連線，一定要找關聯關係，主外來鍵找關聯關係；
SQL> select e.ename,e.deptno,d.dname from emp e,dept d
2 where e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
FORD 20 RESEARCH
SCOTT 20 RESEARCH
ADAMS 20 RESEARCH
JONES 20 RESEARCH
SMITH 20 RESEARCH
TURNER 30 SALES
MARTIN 30 SALES
WARD 30 SALES
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
ALLEN 30 SALES
BLAKE 30 SALES
JAMES 30 SALES
14 rows selected.
----內連線，標準的SQL,支援所有的SQL
SQL> select e.ename,e.deptno,d.dname from emp e,dept d
2 where e.deptno=d.deptno;
也可寫成：
SQL> select e.ename,e.deptno,d.dname
2 from emp e inner join dept d on e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
FORD 20 RESEARCH
SCOTT 20 RESEARCH
ADAMS 20 RESEARCH
JONES 20 RESEARCH
SMITH 20 RESEARCH
TURNER 30 SALES
MARTIN 30 SALES
WARD 30 SALES
ENAME DEPTNO DNAME
-------------------- ---------- ------------------------------------------
ALLEN 30 SALES
BLAKE 30 SALES
JAMES 30 SALES
14 rows selected.
3.外連線；
在外連線中，某些不滿足條件的列也會顯示出來。
也就是說，要限制其中一個表的行，而不是限制另一個表的行。
這種連線形式在許多情況下是非常有用的。
在外連線中又分左連線和右連線
A.左連線：
ORACLE的寫法：+號右邊，以左邊為準，右邊補齊。
左連線+在右邊，左邊的是主表，左邊的表記錄 全部顯示，
如果左邊的表有記錄，右邊的表沒有找到記錄，則補NULL.
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
2 from emp e, dept d
3 where e.deptno=d.deptno(+);
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
from emp e left outer join dept d
on e.deptno=d.deptno;
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
JAMES CLERK 950 30 SALES CHICAGO
TURNER SALESMAN 1500 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MARTIN SALESMAN 1250 30 SALES CHICAGO
WARD SALESMAN 1250 30 SALES CHICAGO
ALLEN SALESMAN 1600 30 SALES CHICAGO
test clerk 860 ----------多的
zxw clerk 900 ---------多的
16 rows selected.
B.右連線：
ORACLE的寫法：+號左邊，以右邊為準，左邊補齊。
右連線+在左邊，右邊的是主表，右邊的表記錄 全部顯示，
如果右邊的表有記錄，左邊的表沒有找到記錄，則補NULL.
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
from emp e , dept d
where e.deptno(+)=d.deptno;
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
from emp e right outer join dept d
on e.deptno=d.deptno;
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
TURNER SALESMAN 1500 30 SALES CHICAGO
MARTIN SALESMAN 1250 30 SALES CHICAGO
WARD SALESMAN 1250 30 SALES CHICAGO
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
ALLEN SALESMAN 1600 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
JAMES CLERK 950 30 SALES CHICAGO
多的 ---------- 40 OPERATIONS BOSTON
15 rows selected.
C.自連線，左右都顯示：
select e.ename, e.job,e.sal，d.deptno,d.dname,d.loc
from emp e full outer join dept d
on e.deptno=d.deptno;
SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
2 from emp e full outer join dept d
3 on e.deptno=d.deptno;
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
JAMES CLERK 950 30 SALES CHICAGO
TURNER SALESMAN 1500 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
ENAME JOB SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MARTIN SALESMAN 1250 30 SALES CHICAGO
WARD SALESMAN 1250 30 SALES CHICAGO
ALLEN SALESMAN 1600 30 SALES CHICAGO
test clerk 860
zxw clerk 900
40 OPERATIONS BOSTON
17 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27036311/viewspace-755748/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 多表外連線的使用
- MySQL筆記3——內連線/外連線、多表連線MySql筆記
- LEFT JOIN 和JOIN 多表連線
- 【janes】多表查詢 外連線
- SQL語言基礎(多表連線)SQL
- Sql Server系列：多表連線查詢SQLServer
- SQL複雜查詢多表連線SQL
- MySQL學習筆記之多表連線MySql筆記
- 多表連線SQL優化如何處理SQL優化
- MYSQL學習筆記23： 多表查詢（自連線內連線+左右外連線）MySql筆記
- SQL優化--多表連線和走索引的關係SQL優化索引
- SpringBoot + JPA的自學之路（三）多表連線查詢Spring Boot
- MySQL和PostgreSQL在多表連線演算法上的差異MySql演算法
- oracle心得4--集合查詢@oracle中的多表連線@案例分析Oracle
- 【學習】SQL基礎-006-多表連線查詢SQL
- MySQL筆記-左連線的使用（left join有關聯的多表查詢）MySql筆記
- 多表連線的三種方式詳解 hash join、merge join、 nested loopOOP
- Mybatis連線池_動態sql語句_多表查詢實現MyBatisSQL
- 14-多表連線、索引選擇、顯式遊標和其他索引
- 通過swap_join_inputs來控制多表連線hash join中的build tableUI
- SQL的四種連線:內連線 左外連線 右外連線 全連線SQL
- 深入理解SQL的四種連線-左外連線、右外連線、內連線、全連線SQL
- oracle 多表連合修改----BYPASS_UJVC(轉)Oracle
- 【SQL】Oracle的內連線、左外連線、右外連線及全外連線SQLOracle
- 長連線和短連線的使用
- http的長連線和短連線HTTP
- Oracle的左連線和右連線Oracle
- 例項解析外連線 內連線 自連線 全連線
- Hadoop--Map/Reduce實現多表連結Hadoop
- linux 的軟連線與硬連線Linux
- 等值連線和自然連線的區別
- SQL中的左連線和右連線SQL
- MYSQL語法：左連線、右連線、內連線、全外連線MySql
- 學習連連看 連線線之謎+道具的使用
- 內連線、外連線
- 連線和半連線
- 左連線，右連線
- Oracle的表連線方法（三）雜湊連線Oracle