相关子查询

[size=medium][b]从一个表查找另一个表没有的值[/b][/size]
create table new_dept(deptno integer);
insert into new_dept values(10);
insert into new_dept values(50);
insert into new_dept values(null);

select * from new_dept;

DEPTNO
-----------
10
50
[b][color=red]null[/color][/b]

select d.dname, d.deptno from dept d
where not exists (select null from new_dept n where d.deptno=n.deptno);

DNAME DEPTNO
-------------- ------
RESEARCH 20
SALES 30
OPERATIONS 40

select deptno from dept minus select deptno from new_dept;

DEPTNO
-----------
20
30
40