- 基本语法:
表达式 [not] in (子查询)
语法中,表达式的最简单形式就是列名或常数
语义:判断某一表达式的值是否在子查询的结果中。
示例:列出张三,王二同学的所有信息
select * from student
where sname in ("张三","王二")
上述示例相当于
select * from student
where sname = "张三" or sname = "王二"
示例:列出既选修了001号课程的学生又选修002号课程的学生的学号
select sno from sc
where cno = '001' and sno in
(
select * from sc
where cno = '002'
)
示例 : 没学过李明老师讲授课程的所有学生的姓名
正确的:
select sname from student
where sno not in
(
select sno from sc,course c,teacher t
where t.tname='李明' and sc.cno = c.cno and t.tno = c.tno
)
错误的:
select sname from student s,sc,course c ,teacher t
where t.tname<>'李明' and sc.cno = c.cno and t.tno = c.tno
相关子查询 : 求学过001号课程的同学的姓名
select sname from student stu
where sno in
(
select sno from sc
where sno = stu.sno and cno = '001'
)