select jname from j where not exists( select * from p where not exists ( select * from spj where j.jno=spj.jno and p.pno=spj.pno));
查表spj中所有滿足條件的spj表中的數據
select jname from j where not exists( select * from p where not exists
( select sno,spj.pno,spj.jno,price,qty from spj,p,j where j.jno=spj.jno and p.pno=spj.pno));
從三個表spj,p,j中取得滿足條件的數據
查表spj中所有滿足條件的spj表中的數據
select jname from j where not exists( select * from p where not exists
( select sno,spj.pno,spj.jno,price,qty from spj,p,j where j.jno=spj.jno and p.pno=spj.pno));
從三個表spj,p,j中取得滿足條件的數據
select * from spj where j.jno=spj.jno and p.pno=spj.pno
和
select sno,spj.pno,spj.jno,price,qty from spj,p,j where j.jno=spj.jno and p.pno=spj.pno
执行效果就不一样,我原以为这句的意思是说在表spj中找出等于表J.jno和表P.pno的纪录
如果这样理解,上面两条应该效果一样,还请大家不吝赐教,给我讲讲清楚,谢了
select jname from j where not exists( select * from p where not exists ( select * from spj where j.jno=spj.jno and p.pno=spj.pno));
第一句的执行计划.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE
FILTER
TABLE ACCESS FULL DESCO.J
FILTER
TABLE ACCESS FULL DESCO.P
TABLE ACCESS FULL DESCO.SPJ
第二句的执行计划
select jname from j where not exists( select * from p where not exists
( select sno,spj.pno,spj.jno,price,qty from spj,p,j where j.jno=spj.jno and p.pno=spj.pno));Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE
FILTER
TABLE ACCESS FULL DESCO.J
FILTER
TABLE ACCESS FULL DESCO.P
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL DESCO.J
SORT JOIN
TABLE ACCESS FULL DESCO.SPJ
SORT JOIN
TABLE ACCESS FULL DESCO.P
是说取J和P的笛卡尔积,查找spj中没有的纪录,再从中查找J中没有的纪录,等效于以下语句
create table temp as(selecet jno,pno from J,p);select * from j where jno not in (select * from temp where not exists (select jno,pno from spj where jno=temp.jno and pno=temp.pno));但是我对 not exists又有新的问题比如:问题 表1 no 表2
1 1
2
select * from temp2 where not exists(select * from temp1 );错误
select * from temp2 where not exists(select * from temp1 where no=temp2.no);正确不知道为什么,还请大家教教我