JOIN、UNION (DML 四)

※創建並加一些測試資料

CREATE TABLE DEPT(
    DEPTNO  NUMBER(9),
    DNAME  VARCHAR(20),
    CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO)
);
COMMENT ON TABLE  DEPT IS '部門表';
COMMENT ON COLUMN DEPT.DEPTNO IS '部門代號';
COMMENT ON COLUMN DEPT.DNAME IS '部門名稱';

CREATE TABLE EMP(
    EMPNO  NUMBER(9),
    ENAME  VARCHAR(10),
    DNAME  VARCHAR(20),
    DEPTNO  NUMBER(9),
    CONSTRAINT EMP_PK PRIMARY KEY(EMPNO)
);
COMMENT ON TABLE  EMP IS '員工表';
COMMENT ON COLUMN EMP.EMPNO IS '員工編號';
COMMENT ON COLUMN EMP.ENAME IS '員工姓名';
COMMENT ON COLUMN EMP.DNAME IS '部門名稱';
COMMENT ON COLUMN EMP.DEPTNO IS '部門代號';

INSERT INTO DEPT VALUES(10, '財務部');
INSERT INTO DEPT VALUES(20, '研發部');
INSERT INTO DEPT VALUES(30, '業務部');
INSERT INTO DEPT VALUES(40, '生管部');

INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (31,'業務部','KING',10);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (32,'業務部','BLAKE',30);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (33,'業務部','CLARK',10);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (34,'業務部','JONES',20);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (35,'業務部','MARTIN',30);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (36,'業務部','ALLEN',30);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (37,'業務部','TURNER',30);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (38,'業務部','SMITH',30);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (39,'業務部','WARD',30);
INSERT INTO EMP (EMPNO,DNAME,ENAME,DEPTNO) VALUES (40,'業務部','MILLER',50);

結果：EMP有 10筆； DEPT有 4筆

※SQL:1999語法

也就是SQL有個標準，這個標準是各家資料庫都要遵守的，所以以下介紹的語法是萬能的

SELECT * FROM EMP CROSS JOIN DEPT;
SELECT * FROM EMP NATURAL JOIN DEPT;
SELECT * FROM EMP INNER JOIN DEPT USING(DEPTNO);
SELECT * FROM EMP E INNER JOIN DEPT D ON(E.EMPNO = D.DEPTNO);

※內聯接(INNER JOIN)：

．INNER 可以省略不打

．簡單來說，內聯接就是不顯示NULL的

．CROSS JOIN：因為EMP有10筆，DEPT有4筆，所以總共是10*4=40筆
以兩張圖的左邊序號來說，就是1-1．1-2． 1-3． 1-4 ．2-1 ．2-2 ．2-3． 2-4以此類推，推到10-4，所以共40筆，這是最沒用的東西，因為有很多資料都沒有用，所以要想辦法把不要的去掉，所以才會有一些USING啦！ON啦！的語法
這個又叫Cartesian(笛卡爾積)，非常有名，join主要就是想辦法消除它
．NATURAL JOIN：會以欄位名稱一樣的，自動幫我們JOIN
這兩張表有DNAME和DEPTNO一樣，所以會幫我們將欄位名稱一樣的JOIN起來
．JOIN…USING：第二行是全部幫我們JOIN起來，但如果只想將一個欄位JOIN起來就好，就可以用USING這個東東
．JOIN…ON：如果欄位名稱不一樣想JOIN或者像USING只能有一個欄位，想要多加一些欄位，就可以用ON
．第四行是無敵的，如下：

SELECT * FROM EMP NATURAL JOIN DEPT;
SELECT * FROM EMP E INNER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO AND E.DNAME = D.DNAME);
SELECT * FROM EMP INNER JOIN DEPT USING(DEPTNO);
SELECT * FROM EMP E INNER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

第一行和第二行是一樣的結果； 第三行和第四行也是一樣的結果，但是用JOIN…ON比較麻煩，所以才會發明JOIN…USING和NATURAL JOIN

．NATURAL JOIN 不會顯示重覆的欄位
．JOIN…USING和JOIN…ON還有兩點不同，除了顯示的順序外，就是USING會把打進去的欄位(DEPTNO)整行不顯示，如下右圖少一個欄位：

SELECT * FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON(E.DEPTNO = D.DEPTNO);
SELECT * FROM EMPLOYEE E INNER JOIN DEPARTMENT D USING(DEPTNO);

※外聯接(OUTER JOIN)：

．OUTER 可以省略不打
．簡單來說，外聯接就是顯示NULL的

※左聯接

SELECT * FROM EMP E INNER JOIN DEPT D ON(D.DEPTNO = E.DEPTNO);
SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON(D.DEPTNO = E.DEPTNO);
也可
SELECT * FROM EMP E LEFT OUTER JOIN DEPT D USING(DEPTNO);

有外聯接的會多第10行，成為有人沒部門的情況，如果只想顯示這行可以加條件，如下：

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO) WHERE D.DEPTNO IS NULL;

這樣下就只會有第10筆了，可參考這個網站
所以LEFT OUTER JOIN就是左表的欄位(ON裡的欄位)對應到NULL的把它顯示出來，以上面的網站的圖來講，中間就是相同的部分，也就是1~9，左邊就是第10筆了

※右聯接

右聯接就是相反而已，成為有部門沒人的情況

SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D USING(DEPTNO);
SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D USING(DEPTNO) WHERE E.DEPTNO IS NULL;

※全聯接

也就是有部門沒人和有人沒部門的綜合體，所以會有11筆(9筆共同的「中間的」)和2筆(左和右聯接各一筆)NULL的

SELECT * FROM EMP E FULL OUTER JOIN DEPT D USING(DEPTNO);
SELECT * FROM EMP E FULL OUTER JOIN DEPT D USING(DEPTNO) WHERE D.DEPTNO IS NULL OR E.DEPTNO IS NULL;

※Oracle的JOIN語法

※內聯接

SELECT * FROM EMP, DEPT;
SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

．表和表之間用逗點隔開就可以了，等同CROSS JOIN，消除迪卡爾積就用一般的WHERE就可以了
．用逗點隔開就不能再用USING和ON了

※外聯接

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D USING(DEPTNO);
SELECT * FROM EMP E, DEPT D WHERE D.DEPTNO(+) = E.DEPTNO;

．這兩行是一樣的，都是左聯接
．第一行是標準語法，所以EMP有對應到NULL的要顯示出來
．Oracle的(+)表示要顯示NULL的那一方
．不要想去判斷左或右聯接，要以邏輯的角度去想，譬如有人沒部門，那麼沒部門(D)那裡一定有(+)，所以不要管左右了

※全聯接

Oracle沒有提供全聯接，不可以兩邊都有(+)，會報「ORA-01468: 一個述詞只可以參用一個外部結合的表格」的錯，所以只能用標準語法

※多表連接

以inner join 為例

-- 標準的多表連接
SELECT * FROM 
T1 t1 [INNER] JOIN T2 t2 ON(t1.xxx = t2.ooo)
        [INNER] JOIN T3 t3 ON(t1.xxx = t3.zzz)
      -- ...
-- Oracle 的多表連接
SELECT * FROM 
T1 t1, T2 t2, T3 t3 --, T4 [t4], T5 [t5], ...
WHERE t1.xxx = t2.ooo
AND t1.xxx = t3.zzz;

※Union

聯集，Union容易和Join混淆
其中JOB和DNAME的類型和順序必須相同，否則會出「ORA-01790: 表示式的資料類型必須與相對應的表示式相同 01790. 00000 -  "expression must have same datatype as corresponding expression"」的錯

SELECT DNAME FROM DEPT WHERE DEPTNO = 30

結果：(有1筆)
SALES

SELECT JOB FROM EMP WHERE SAL > 2000

結果：(有7筆)
CLERK
MANAGER
MANAGER
MANAGER
ANALYST
PRESIDENT
ANALYST

SELECT DNAME FROM DEPT WHERE DEPTNO = 30
UNION ALL
SELECT JOB FROM EMP WHERE SAL > 2000

結果：(UNION ALL 不會去掉重覆的值)(7+1=8筆)
SALES
CLERK
MANAGER
MANAGER
MANAGER
ANALYST
PRESIDENT
ANALYST

SELECT DNAME FROM DEPT WHERE DEPTNO = 30
UNION
SELECT JOB FROM EMP WHERE SAL > 2000

結果：(UNION 去掉重覆的值)
ANALYST
CLERK
MANAGER
PRESIDENT
SALES
所以使用UNION語法就是把兩張表全部SHOW出來，包括NULL

※使用UNION或UNION ALL的效能比OR還要好

※MINUS

既然講了UNION，那就順便講MINUS和INTERSECT了，因為官網是一起講的
minus就是減的意思，第一筆資料減第二筆資料相同的值，最後在distinct並依小到大排序(ASC)，假設有兩筆資料
A：5、7、8、8
B：7、9、2
A minus B==>5、8
B minus A==>2、9

※範例1

SELECT deptno FROM emp
WHERE empno IN(7902,7499,7876,7900)-- 30 20 30 20
MINUS
SELECT deptno FROM emp
WHERE empno IN(7782,7654,7698,7934);-- 30 30 10 10

※依照上面的說法，答按是20

※範例2

SELECT deptno FROM emp
WHERE empno IN(7782,7654,7698,7934)-- 30 30 10 10
MINUS
SELECT deptno FROM emp
WHERE empno IN(7902,7876);-- 20 20

※什麼都減不到，但仍要distinct和排序，答案是10 30

※範例3

SELECT * FROM EMP --14筆
MINUS
SELECT * FROM EMP WHERE DEPTNO = 10 --3筆

※答案是14-3，所以是11筆資料量

※INTERSECT

交集，只抓重覆的資料

SELECT * FROM EMP
INTERSECT
SELECT * FROM EMP WHERE DEPTNO = 10;

※這要看資料，我的情況是重覆的資料有3筆