SQL嵌套查询

一、带IN谓词的子查询

查询和张三同一个系的学习的学生

时间复杂度M+N

select sname
from student
where sdept in (select sdept 
                from student 
                where name = '张三')

用连接查询同样可以实现 

时间复杂度M*N（SQL是高度非过程化语言，内部会优化）

select y.sname
from student as x, student as y
where x.sname = '张三' and x.sdept = y.sdept and x.sname <> y.sname

查询选修了陈国教授课程的学生

select sname 
from student 
where sno in(
    select sno
    from sc
    where cno in(
    select cno 
    from course
    where teacher = '陈国'))

连接查询

select sname
from student，sc，course
where student.sno = sc.sno and course.cno = sc.cno and teacher = '陈国'

子查询不依赖于父查询的查询称为不相关子查询

二、带有比较运算符的子查询（>,<、=、>=、<=、！=、<>...）

三、带有限量谓词的子查询