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