EXISTS和IN

SQL> select * from student;

SNO SNAME SCLASS
---------- ---------- ----------
1001 Tom 1
1002 Lucy 3
1003 Lily
1004 Jerry 3

SQL> select * from class;

CNO CNAME
---------- ----------
1 计算机1班
2 化学1班
3 物理1班

SQL> select sname,sno from student where sclass in (select cno from class);

SNAME SNO
---------- ----------
Tom 1001
Lucy 1002
Jerry 1004

SQL> select sname,sno from student where exists (select * from class where cno = sclass);

SNAME SNO
---------- ----------
Tom 1001
Lucy 1002
Jerry 1004

当数据量大的时候，使用EXISTS比IN高