思考 SQL |

一樣的結果，可以有不同的 SQL 解法，所以除了科學，SQL 也是一種藝術。現在就來思考幾段 SQL 的藝術。

就用我常用來展示的資料表 DEPT 與 EMP 來展示。當你實際碰到問題時可以回到這裡參閱，看看能不能激發你的思維。這也是我日常常會參閱的幾段 SQL。

問題很簡單，我們要查詢沒有獎金(comm)的部門。往下看前先試試解解看。

Table DEPT

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 會計部          紐約
        20 RESEARCH       DALLAS
        30 SALES          台南
        40 OPERATIONS     BOSTON
        70 資訊部          台南
        60 開發部          台南

Table EMP

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM DEPTNO
----- ---------- --------- ---------- ------------ ---------- ---------- ------
 7839 KING       PRESIDENT            17-NOV-81          5000                10
 8907 牸祢        ANALYST         7566 09-DEC-82          9002                10
 7782 楊建華      MANAGER         7902 09-JUN-81          2400                10
 7934 楊喆        CLERK           7902 23-JAN-82          1500                10
 7369 SMITH      CLERK           7902 17-DEC-80          8001                20
 7902 FORD       ANALYST         7566 03-DEC-81          3000                20
 7876 ADAMS      CLERK           7788 12-JAN-83          1100                20
 7566 吳煌珉      MANAGER         7839 02-APR-81          2975                20
 7788 SCOTT      ANALYST         7566 09-DEC-82         45300                20
 7900 JAMES      CLERK           7698 03-DEC-81         94998                30
 7844 하찮고      SALESMAN        7698 08-SEP-81          1500                30
 7654 葉習堃      SALESMAN        7698 28-SEP-81          1250       1400     30
 7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        303     30
 7698 BLAKE      MANAGER1        7839 01-MAY-81          2850        101     30
 9011 文英蔡      總鋪師           7788 28-AUG-18         77778        180     40
 7608 馬小九      ANALYST         7788 28-JUN-10          1000        100     40
 9006 林頂尚      ANALYST         7788 07-MAY-01         66666                70
 7607 バック      分析師           7788 24-MAR-08         45000        100     70
 7609 蔡大一      分析師           7788 28-JUN-10         60000                70

資料不多，直接目視就知道只有部門 30、40 與 70 有獎金，因此答案就是: 10(會計部)、20(RESEARCH) 與 60(開發部)。60 開發部雖然沒有員工，但是也應該包括在內。

這就來看看有哪些解法。在這裡的解法所得到的答案都是一樣，但並不表示將這些 SQL 套用到你的系統，所得到的答案都會一樣，因為這牽涉到你系統 Schema 的規劃，例如，欄位的 Contraints 等等。尤其要注意 Nulls 的問題。

Null 是所有資料庫中特有的一個值，不管資料庫是關聯式、或者是新興的非關聯式，都有 Null 值。因此所有的語言也都支援 Null 值的處理，包含 JSON。

相關性子查詢 Correlated Subquery

Correlated Subquery

select dname
  from dept d
 where not exists (select null
                     from emp e
                    where e.deptno = d.deptno
                      and comm is not null
                  )
;

第 5 行 where 條件句中主查詢(MasterQuery)與子查詢(SubQuery)有了相關性，所以稱為 Correlated Subquery。碰到 NOT 這種反向邏輯總是會腦袋變殘，要好好思考。這種 Correlated Subquery 是最基礎與通常的寫法。結果如下。

Result

DNAME
--------------
開發部
RESEARCH
會計部

不相關性子查詢 Uncorrelated Subquery

Uncorrelated Subquery

select dname
  from dept
 where deptno not in (select deptno
                        from emp
                       where comm is not null
                     )
;

主查詢與子查詢沒有直接的相關性，這稱為 Uncorrelated Subquery。

答案一樣這裡就不列出，不要直接複製貼上只看解答，瞄它一眼，然後自己寫寫看。

使用 NOT IN 時，小心 Null 值，我們先不要中斷這裡的思考，留待後面再來談這個 Nulls 的問題。

Outer Join

Outer Join

select dname
  from dept d
       left outer join emp e
         on e.deptno = d.deptno
        and e.comm is not null
where e.deptno is null
;

集合函数 Aggregate functions
Aggregate function，平常用的最多的就是 count( ), 你如果以為 Aggregate function 不外乎只有，SUM、AVG、MAX、MIN
的功能，那可能就太小看它了。注意，這些集合函數總是與 Group by 同時出現。

SUM

Aggregate function SUM

select dname
  from (select dname,
               case when comm is null then 0
                                      else 1
               end as flag
          from emp e,
               dept d
         where e.deptno (+) = d.deptno
       ) s
 group by dname
having sum(flag) = 0
;

這與上段類似，但直接省掉 Inline View。

Aggregate function SUM without Inline View

select dname
  from emp e,
       dept d
 where e.deptno (+) = d.deptno
 group by dname
having sum(case when e.comm is null then 0
                                    else 1
           end) = 0
;

MAX

Aggregate function MAX

select dname
  from emp e,
       dept d
 where e.deptno (+) = d.deptno
 group by dname
having max(coalesce(e.comm,0)) = 0
;

集合 Set Operation

Set Operation

select dname
  from dept
MINUS
select dname
  from dept d,
       emp e
 where d.deptno = e.deptno
   and e.comm is not null
;

Subquery Factoring

Subquery Factoring

with x as (
  select dname,
         case when comm is null then 0
                                else 1
         end as flag
    from emp e,
         dept d
   where e.deptno (+) = d.deptno
)
select dname
  from x
 group by dname
having sum(flag) = 0
;

Analytic function

Analytic function

with x as (
  select deptno,
         sum(coalesce(comm, 0)) over(partition by deptno) dept_comm
    from emp
)
select distinct dname
  from x,
       dept d
 where x.deptno (+) = d.deptno
   and coalesce(dept_comm, 0) = 0
;

每天都在 SQL 中打滾，應該都看得懂，希望你不是用複製貼上在驗證這些 SQL。現在就給個家庭作業，關掉這個網頁，寫出三個解答。

Nulls and NOT IN

Nulls 很討厭，它不知是甚麼東西的東西，你也無法把它當成是東西。但它無處不在。我們簡單的來看看。

SQL> select * from car_table;

CAR                  COLOR
-------------------- --------------------
Car1                 RED
Car2                 BLUE
Car3
Car4

SQL> select * from car_table where color != 'RED';

CAR                  COLOR
-------------------- --------------------
Car2                 BLUE

這個答案是正確的嗎? 大部分的人可能不會這麼想!

SQL> select * from car_table where color != 'RED' or color IS NULL;

CAR                  COLOR
-------------------- --------------------
Car2                 BLUE
Car3
Car4

為了正確的答案，你總必須把 NULL 加到你的每個 Query! 如果需要，可考慮加入 NOT NULL Constraint；NOT NULL Constraint 會影響 Optimizer 的 QUERY REWRITE。對 SQL 的效能也會有影響。尤其要注意，Unique Index 與 Unique Constraint 會有不同的 SQL 效能最佳化。

SQL> desc dual;
 Name                       Null?    Type
 -------------------------- -------- -----------------
 DUMMY                               VARCHAR2(1)

SQL> select * from dual;

D
-
X

SQL> select * from dual where dummy not in ('Y');

D
-
X

用最簡單的 DUAL Table 來看 NOT IN 的問題。這裡的 NOT IN 沒有問題。

SQL> select * from dual where dummy not in ('Y', NULL);

no rows selected

疑 ? no rows selected。使用 NOT IN 時，小心 Null 值。也許這是你要的結果；如果不是，要防止 NULL 值的出現。

colX not in (‘A’, ‘B’, ‘C’) 等於 (colX !=’A’ and colX !=’B’ and colX != ‘C’) 所以如果有 Null 值， colX != NULL 永遠不會是 true。記得， colX != null 與 colX is not null 是不相同的。

colX in (‘A’, ‘B’, ‘C’) 等於 (colX =’A’ or colX =’B’ or colX = ‘C’) 所以沒有 Null 的問題。要注意，IN 與 NOT IN 並不是完全對立的指令。

SQL> select empno, ename from emp where empno not in (select mgr from emp);

no rows selected

這裡想找出非主管人員，得到的答案是: 怎麼會都是主管 ?

SQL> select empno, ename from emp where mgr is null;

     EMPNO ENAME
---------- ----------
      7839 KING

總裁沒有上司。

SQL> select empno, ename
  2  from emp
  3  where empno not in (select mgr from emp where mgr is not null);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7607 バック
      7608 馬小九
      ...

使用 NOT IN 要小心 Nulls。這裡在 Subquery 中要防止 mgr 出現 NULL 值。

在規劃 Schema 時，不該有 NULL 的 Column 就應該要設 NOT NULL Constraint。

相對的，使用 NOT EXISTS 會比較安全。

SQL> select empno, ename
  2  from emp e
  3  where not exists (select null from emp e2 where e.empno = e2.mgr);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7607 バック
      7608 馬小九
      ...

祝，健康快樂。

相關性子查詢 Correlated Subquery

不相關性子查詢 Uncorrelated Subquery

Outer Join

集合函数 Aggregate functions

集合 Set Operation

Subquery Factoring

Analytic function

Nulls and NOT IN