not in / not exists /外连接 互相改写

SQL> select count(*) from employees where department_id is null;

  COUNT(*)
----------
         1

SQL> select count(*) from departments where department_id is null;

  COUNT(*)
----------
         1

employees里面有null值，任何值不能与null进行比较。所以没有返回行数。

SQL> select department_name
  2   from hr.departments dept
  3   where department_id NOT IN
  4   (select department_id from hr.employees);

no rows selected

而一旦去除employees里面的null值，但是departments里面的null值与employees里面的值不能比较，故也不返回。

SQL> select count(department_name)
  2   from hr.departments dept
  3   where department_id NOT IN
  4   (select department_id from hr.employees emp where emp.department_id is not null);

COUNT(DEPARTMENT_NAME)
----------------------
                    16

直接改写成not exists，与not in不同，not exists取不存在于employees里面的值，而null值与null值不能比较，故也返回了。

SQL> select count(*)
  2   from hr.departments dept
  3   where  NOT exists
  4   (select 1 from hr.employees emp where dept.department_id=emp.department_id) ;

  COUNT(*)
----------
        17

SQL> select count(*)
  2   from hr.departments dept
  3   where  NOT exists
  4   (select 1 from hr.employees emp where dept.department_id=emp.department_id) and dept.department_id is not null;

  COUNT(*)
----------
        16

外连接

SQL> select count(distinct department_name) from hr.departments dept left join  hr.employees emp on dept.department_id=emp.department_id where  emp.department_id is  null;

COUNT(DISTINCTDEPARTMENT_NAME)
------------------------------
                            17

SQL> select count(distinct department_name) from hr.departments dept left join  hr.employees emp on dept.department_id=emp.department_id 
where  emp.department_id is  null and dept.department_id is not null;

COUNT(DISTINCTDEPARTMENT_NAME)
------------------------------
                            16