oracle進階查詢之連接配接查詢、集合運算

一、集合運算

--minus補集，傳回第一個查詢結果的記錄減去第二個查詢結果的記錄後剩下的記錄

SELECT deptno FROM dept MINUS SELECT deptno FROM emp;

--intersect交集，傳回兩個查詢共有的記錄

SELECT deptno FROM dept INTERSECT SELECT deptno FROM emp;

--union并集，傳回各個查詢的所有記錄，不包括重複記錄

SELECT deptno FROM dept UNION SELECT deptno FROM emp;

--union all并集，傳回各個查詢的所有記錄，包括重複記錄

SELECT deptno FROM dept UNION ALL SELECT deptno FROM emp;

當使用集合操作的時候，要注意：查詢所傳回的列數以及列的類型必須比對，列名可以

不同。

二、連接配接查詢

在 SQL Server 中已經學習過内聯接(inner join)、外聯接(outer join)，外聯接又分為左外聯

接(left outer join)和右外聯接(right outer join)。 Oracle 中對兩個表或者若幹表之間的外聯接用

（+）表示。

--1、内連接配接,預設情況下，在執行連接配接查詢時如果沒有指定任何連接配接操作符，都屬于内連接配接

例：請查詢出工資大于 2000 元的，員工姓名，部門，工作，工資。

由于部門名稱在 dept 中，其他的資訊在 emp 表中，需要内聯接才能完成。

代碼示範：内聯接

SELECT e.ename,e.job,e.sal,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno

AND e.sal>2000;

-----通過在from子句中指定inner join也可以指定内連接配接，其中inner可以省略不寫

SELECT e.ename,e.job,e.sal,dd.dname

FROM emp e INNER JOIN dept dd ON e.deptno=dd.deptno

WHERE e.sal>2000;

-----如果主表的主鍵列和從表的外部鍵列名相同，那麼還可以使用natural join關鍵字自動執行内連接配接操作

SELECT dname,ename FROM dept NATURAL JOIN emp;

--2、左外連接配接

-----左外連接配接是通過left[OUTER] join選項來實作的，不僅會傳回滿足連接配接條件的所有記錄，還會傳回不滿足連接配接條件的連接配接操作符左别表的其它行

例：請查詢出每個部門下的員工姓名，工資。

分析：Emp 表用外鍵 deptno 引用 Dept 表中的 deptno，在 Dept 表中如果有某些部門沒有員工，

那麼用内聯接，沒有員工的部門将無法顯示，是以必須以 Dept 表為基準的外聯接。

下面的（+）操作符以及右外連接配接也是一樣

SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b

ON a.deptno=b.deptno AND a.deptno=10;

--3、右外連接配接    

-----跟左外連接配接一樣

--4、完全外連接配接

-----通過FULL[OUTER] JOIN選擇選項來實作的，不僅會傳回滿足連接配接條件的所有記錄，還會傳回不滿足連接配接條件的所有其它行

SELECT a.dname,b.ename FROM dept a FULL JOIN emp b

ON a.deptno=b.deptno AND a.deptno=10;

--(+):oracle專用的聯接符，在條件中出現在左邊指右外聯接，出現在右邊指左外聯接

--雖然oracl自身的聯接查詢文法比較好寫，同時容易了解，但是為了程式便于移植，推薦使用OUTER JOIN執行外連接配接。

--5、使用（+）操作符

----5、1使用（+）操作符執行左外連接配接

SELECT a.dname,b.ename FROM dept a,emp b

WHERE a.deptno=b.deptno(+)

AND b.deptno(+)=10;

----5、2使用（+）操作符執行右外連接配接

SELECT a.dname,b.ename FROM dept a,emp b

WHERE a.deptno(+)=b.deptno AND a.deptno(+)=10

ORDER BY a.dname;