子查询(in exists)

select * from [dbo].[student]
where StudentNo in(1501,1503,1505);--这个是返回1501,1503,1505

select * from [dbo].[student]
where StudentNo not in(1501,1503,1505);--非运算符的用法

select * from [dbo].[student]
where StudentName in('李四','赵六');--字符串也是可以的


select * from [dbo].[student]--主查新
where StudentNo in (select StudentNo from [dbo].[student_lesson])--子查询

 

select a.StudentNo,a.StudentName,a.Age,a.Sex from [dbo].[student] as a --as取别名 as是可以省略的
where exists (select * from [dbo].[student_lesson] as b where a.StudentNo=b.StudentNo);--返回是 true 或者 false  看是否存在


select a.StudentNo,a.StudentName,a.Age,a.Sex from [dbo].[student] as a 
where not exists (select * from [dbo].[student_lesson] as b where a.StudentNo=b.StudentNo);