01.select * from emp where emp_no not in (select emp_no from emp_bill)
select * from emp where emp_no not in (select emp_no from emp_bill)
因为NOT IN 效率比较低,因此需要优化,有两种方法:
方法一:
01.select * from emp a where not exists (select 1from emp_bill b where b.emp.no=a.emp_no)
select * from emp a where not exists (select 1from emp_bill b where b.emp.no=a.emp_no)
select 1 是一种用法,也可以select 某字段 或者select 'x'都行的,也就是说exists其实是判断是否有数据返回,而不关心返回的数据。
方法二:
01.select a. * from emp a ,emp_bill b where a.emp_no = b.emp_no( + ) and b.emp_no is n
select a. * from emp a ,emp_bill b where a.emp_no = b.emp_no( + ) and b.emp_no is n