select * from emp where emp_no in(select emp_no from emp minus select emp_no from emp_bill );select a.* from emp a,(select emp_no from emp minus select emp_no from emp_bill ) b where a.emp_no = b.emp_no;可以么?
select * from emp a where no exists (select 1 from emp_bill b where b.emp.no=a.emp_no)
方法一select * from emp a where not exists (select 1 from emp_bill b where b.emp.no=a.emp_no)
方法一 还有点错误 select * from emp a where not exists (select 1 from emp_bill b where b.emp_no=a.emp_no) 方法2 select a.* from emp a ,emp_bill b where a.emp_no=b.emp_no(+) and b.emp_no is null
这种情况一般用exits处理。 select * from emp a where not exists (select 1from emp_bill b where b.emp.no=a.emp_no)
select a.* from emp a,emp_bill b where a.emp_no <>b.emp_no select * from emp a where emp_no not exists (select emp_no b from emp_bill where a.emp_no <>b.emp_no)
SELECT * FROM EMP A WHERE NOT EXISTS (SELECT 1 FROM EMP_BILL B WHERE B.EMP_NO = A.EMP_NO)
1. select a.* from emp a, emp_no b where a.emp_no != b.emp_no2. select * from emp where emp.emp_no in (select emp_no from emp minus select emp_no from emp_bll)
这种情况一般用not exits处理。
表连接效率最好,Not Exists其次,Not in最低
4楼的写得不错,大致补充几点:1, select a.* from emp a ,emp_bill b where a.emp_no=b.emp_no(+) and b.emp_no is null改为 select a.* from emp a ,emp_bill b where a.emp_no=b.emp_no(+) and NVL(b.emp_no, '1') = '1'要好一些 2, 如果DB是10g, 可考虑用LNNVL function, 如下:SELECT A.* FROM EMP A, EMP_BILL B WHERE LNNVL(A.EMP_NO = B.EMP_NO);
from emp
where emp_no in(select emp_no from emp minus select emp_no from emp_bill );select a.*
from emp a,(select emp_no from emp minus select emp_no from emp_bill ) b
where a.emp_no = b.emp_no;可以么?
from emp a
where no exists (select 1
from emp_bill b
where b.emp.no=a.emp_no)
from emp a
where not exists (select 1
from emp_bill b
where b.emp.no=a.emp_no)
select *
from emp a
where not exists (select 1
from emp_bill b
where b.emp_no=a.emp_no) 方法2
select a.*
from emp a ,emp_bill b
where a.emp_no=b.emp_no(+)
and b.emp_no is null
select * from emp a where
not exists (select 1from emp_bill b where b.emp.no=a.emp_no)
select * from emp a where emp_no not exists (select emp_no b from emp_bill where a.emp_no <>b.emp_no)
FROM EMP A
WHERE NOT EXISTS (SELECT 1 FROM EMP_BILL B WHERE B.EMP_NO = A.EMP_NO)
select 1 是一种用法,也可以select 某字段
或者select 'x'都行的,也就是说exists其实
是判断是否有数据返回,而不关心返回的数据。
select a.*
from emp a, emp_no b
where a.emp_no != b.emp_no2.
select *
from emp
where emp.emp_no in (select emp_no from emp minus select emp_no from emp_bll)
from emp a ,emp_bill b
where a.emp_no=b.emp_no(+)
and b.emp_no is null改为
select a.*
from emp a ,emp_bill b
where a.emp_no=b.emp_no(+)
and NVL(b.emp_no, '1') = '1'要好一些
2, 如果DB是10g, 可考虑用LNNVL function, 如下:SELECT A.* FROM EMP A, EMP_BILL B WHERE LNNVL(A.EMP_NO = B.EMP_NO);