[Oracle]Not 的妙用

最近遇到了一個需求，讓我驚覺Not 的妙用~~

欄位一等於A，且欄位二等於B，不列入計算
欄位一等於A，且欄位二等於C，不列入計算

這個看似簡單的條件，可讓我小小思考了一下，才寫出自己滿意的寫法。

以employees table為範例，假設條件為呈現所有 employees的資料，但濾除以下兩個條件：

job_id = SA_REP 且  manager_id = 145

job_id = SA_REP 且  manager_id = 146

解法一：

select *
  from employees t
 where not (t.job_id = 'SA_REP' and t.manager_id in (145, 146));

解法二：

select *
  from employees t
 where not exists (select 'X'
          from employees
         where t.job_id = 'SA_REP'
           and t.manager_id in (145, 146));

解法三：

select *
  from employees t
 where (t.job_id, t.manager_id) not in (select 'SA_REP', 145 from dual)
   and (t.job_id, t.manager_id) not in (select 'SA_REP', 146 from dual);

解法四：

select *
  from employees t
 where (t.job_id, t.manager_id) not in
       (select 'SA_REP', 145
          from dual
        union
        select 'SA_REP', 146 from dual)