Oracle SQL多表查詢

曾經一段時間我對oracle的多表查詢搞的雲裡霧裡，究其原因：oracle自己的文法和SQL國際標準文法混用。此文章僅適合oracle 菜鳥，老鳥直接飛過…

多表連線類型（SQL 1999標準）

• Cross joins

• Natural joins

• USING clause

• Full (or two-sided) outer joins

• Arbitrary join conditions for outer joins

SQL1999文法：

SELECT table1.column, table2.column

FROM table1

[CROSS JOIN table2] |

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

ON (table1.column_name = table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)]|

[CROSS JOIN table2];

文法解釋：

table1.column --指明從中檢索資料的表和列

CROSS JOIN --返回兩個表的笛卡爾集

NATURAL JOIN --根據相同的列名串連兩個表

JOIN table

USING column_name --根據列名執行等值串連

JOIN table ON

table1.column_name --根據ON 子句中的條件執行等值串連

= table2.column_name

LEFT/RIGHT/FULL OUTER

一般來說，從資料顯示方式來講，分為內串連和外串連

內串連：只返回滿足串連條件的資料。

外串連：除了返回滿足串連條的行以外，還返回左（右）表中，不滿足條件的行，

稱為左（右）串連

示範，主要以SQL標準為主，oracle 寫法作對比。樣本使用者為scott、HR

解鎖這兩個使用者語句：

alter user scott identified by tiger account unlock;

alter user hr identified by hr account unlock;

內串連

--Oracle的寫法

select empno,ename,sal,dname,loc from emp,dept

where emp.deptno=dept.deptno;

-- SQL 99標準的寫法

select empno,ename,job,sal,dept.deptno,dname,loc

from emp join dept on emp.deptno=dept.deptno;

或把join改為inner join

外串連左外串連

Oracle 外串連文法：

SELECT table1.column, table2.column --右外串連

FROM table1, table2

WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column --左外串連

FROM table1, table2

WHERE table1.column = table2.column(+);

SQL 1999標準外串連文法見上面SQL1999文法

--Oracle的寫法

外串連的符號是（＋），（＋）要放在欄位名後。（＋）對面的那個表，會全部顯示。

左外串連時，加號在等號的右邊

SQL> select d.dname,e.ename,e.deptno

from dept d,emp e

where d.deptno = e.deptno(+)

order by d.deptno;

DNAME ENAME DEPTNO

-------------- ---------- ------

ACCOUNTING CLARK 10

ACCOUNTING KING 10

ACCOUNTING MILLER 10

RESEARCH JONES 20

RESEARCH FORD 20

RESEARCH ADAMS 20

RESEARCH SMITH 20

RESEARCH SCOTT 20

SALES WARD 30

SALES TURNER 30

SALES ALLEN 30

SALES JAMES 30

SALES BLAKE 30

SALES MARTIN 30

OPERATIONS

15 rows selected

-- SQL 99標準的寫法接

SQL> select d.dname,e.ename,e.deptno

from dept d

left join emp e

on d.deptno = e.deptno

order by d.deptno;

DNAME ENAME DEPTNO

-------------- ---------- ------

ACCOUNTING CLARK 10

ACCOUNTING KING 10

ACCOUNTING MILLER 10

RESEARCH JONES 20

RESEARCH FORD 20

RESEARCH ADAMS 20

RESEARCH SMITH 20

RESEARCH SCOTT 20

SALES WARD 30

SALES TURNER 30

SALES ALLEN 30

SALES JAMES 30

SALES BLAKE 30

SALES MARTIN 30

OPERATIONS

15 rows selected

右外串連

--oracle的寫法

SQL> select empno, ename, job, sal, dept.deptno, dname, loc

2 from emp, dept

3 where emp.deptno(+) = dept.deptno;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

40 OPERATIONS BOSTON

15 rows selected

--SQL1999標準寫法

SQL> select empno, ename, job, sal, dept.deptno, dname, loc

2 from emp

3 right join dept

4 on emp.deptno = dept.deptno;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

40 OPERATIONS BOSTON

15 rows selected

全串連

--SQL1999標準寫法

SQL> select empno, ename, job, sal, d.deptno, dname, loc

2 from emp e

3 full join dept d

4 on e.deptno = d.deptno;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

40 OPERATIONS BOSTON

15 rows selected

自串連

把表自身的鏡像當成另外一個表

--oracle 的寫法

SQL> select e.ename || ' works for ' || m.ename

2 from emp e, emp m

3 where e.empno = m.mgr;

E.ENAME||'WORKSFOR'||M.ENAME

-------------------------------

JONES works for FORD

JONES works for SCOTT

BLAKE works for TURNER

BLAKE works for ALLEN

BLAKE works for WARD

BLAKE works for JAMES

BLAKE works for MARTIN

CLARK works for MILLER

SCOTT works for ADAMS

KING works for BLAKE

KING works for JONES

KING works for CLARK

FORD works for SMITH

13 rows selected

--SQL1999標準的寫法

SQL> select e.ename || ' works for ' || m.ename

2 from emp e

3 join emp m

4 on e.empno = m.mgr;

E.ENAME||'WORKSFOR'||M.ENAME

-------------------------------

JONES works for FORD

JONES works for SCOTT

BLAKE works for TURNER

BLAKE works for ALLEN

BLAKE works for WARD

BLAKE works for JAMES

BLAKE works for MARTIN

CLARK works for MILLER

SCOTT works for ADAMS

KING works for BLAKE

KING works for JONES

KING works for CLARK

FORD works for SMITH

13 rows selected

自然串連

以兩個表具有相同的欄位的所有列為基礎，可採用自然串連（natural join)

它將選擇兩個表中那些在所有匹配的列中值相等的行。

如果列具有相同的名稱，但資料類型能夠不同，則會報錯。

--SQL1999標準寫法

SQL> select empno, ename, job, sal, deptno, dname, loc

2 from emp natural

3 join dept;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

14 rows selected

使用using子句建立串連

如果幾個列具有相同的名稱，但是資料類型不匹配，那麼可以使用using子句來修改natural join子句以指定要用於等值串連的列，在多個列匹配時，使用using 子句只匹配一個列，在引用列中不要使用表名或別名。natural join 和using 子句是互不相容的。

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE location_id = 1400;

--下面的語句無效，因為where 子句中限定了location_id為d. location_id

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE d.location_id = 1400;

ORA-25154: column part of USING clause cannot have qualifier

注意：兩個表中名稱相同的列在使用時不能有任何限定符，這一限制同樣適用natural join

cross join 實現交叉串連

交叉串連產生的結果集也就是笛卡爾乘積

--oracle 的寫法

select empno, ename, dname

from emp,dept;

在oracle文法中直接from兩張表就是cross join

--SQL1999標準寫法

select empno, ename, dname

from emp

cross join dept;

等值串連時增加條件

上述的多表串連都是等值串連（最常用），等值串連後面還可以添加限定條件。

--oracle 的寫法

select e.empno, e.ename, d.dname

from emp e, dept d

where d.deptno = e.deptno

and e.ename = 'SCOTT';

--SQL1999標準的寫法

select e.empno, e.ename, d.dname

from emp e

inner join dept d

on d.deptno = e.deptno

and e.ename = 'SCOTT';

非等值串連

--oracle的寫法

select ename, sal, grade

from emp, salgrade

where sal between losal and hisal

and emp.deptno = 20;

--SQL1999標準寫法

select e.ename, e.sal, s.grade

from emp e

join salgrade s

on e.sal between losal and hisal

and e.deptno = 20;

總結

Oracle的文法還是比較人性化的，除了左外串連和右外串連。如果想使代碼簡單明了，使用oracle的文法，如果想使寫出的代碼相容性比較好（可能會移植到DB2等），盡量使用SQL標準文法。

詳細資料請參考oracle 官方文檔

Oracle Database

SQL Language Reference

11g Release 2 (11.2)

E17118-04

9 SQL Queries and Subqueries 部分