sql exists oracle,[Oracle] exists 和 not exists

简单用两个例子说明一下exists和not exists的用法.

SQL> select * from tt2;

ID NAME

---------- --------------------

100 EDITION

116 TABLE

280 SYNONYM

1098 SEQUENCE

1279 PACKAGE

1300 VIEW

6 rows selected.

SQL> select * from tt1;

ID NAME

---------- --------------------

100 EDITION

116 TABLE

117 SYNONYM

SQL> select * from tt2 where name in(select name from tt1);

ID NAME

---------- --------------------

100 EDITION

116 TABLE

280 SYNONYM

SQL> select * from tt2 where exists (select name from tt1);     #tt1查询出来的为true，故tt2所有值返回

ID NAME

---------- --------------------

100 EDITION

116 TABLE

280 SYNONYM

1098 SEQUENCE

1279 PACKAGE

1300 VIEW

6 rows selected.

SQL> select * from tt2 where exists (select name from tt1 where tt1.name=tt2.name);    #tt1连接tt2后查询出来的为true，故tt2所有值返回

ID NAME

---------- --------------------

100 EDITION

116 TABLE

280 SYNONYM

SQL> select * from tt2 where not exists (select * from tt1);

no rows selected

SQL> select * from tt2 where not exists (select * from tt1 where tt1.name=tt2.name);

ID NAME

---------- ----------------------------------------

1279 PACKAGE

1300 VIEW

1098 SEQUENCE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-2134035/，如需转载，请注明出处，否则将追究法律责任。