使用两表关联可能稍微好一些,当然,还要注意索引的使用
select a.* from a ,b where a.dept_no = b.dept_no and b.dept_stu<> 'STU'
也可以使用 exits
具体见:http://hi.baidu.com/zhao_e893/blog/item/54d643da667558dbb6fd48b5.html
select a.* from a ,b where a.dept_no = b.dept_no and b.dept_stu<> 'STU'
也可以使用 exits
具体见:http://hi.baidu.com/zhao_e893/blog/item/54d643da667558dbb6fd48b5.html
WHERE DEPT_NO NOT IN(
SELECT DEPT_NO FROM B WHERE B.DEPT_STU<>'STU');----------
select a.dept_no from a where not exists (select 'x' from b where a.dept_no = b.dept_no and b.dept_stu <>'STU');
select a.dept_no from a,b where a.dept_no=b.dept_no(+)
and b.dept_no<>'STU';
或者:
select a.dept_no
from a,b where a.dept_no=b.dept_no(+)
and b.dept_no(+)<>'STU' and b.dept_no is null;
WHERE NOT exists(
SELECT 'X' FROM B WHERE B.DEPT_STU<>'STU')