用SQL语句 ，连接运算进行表的查询

来源：百度知道 编辑：UC知道 时间：2024/05/15 17:37:38
查询没有选修1号课程的学生姓名。 代码如下：
select sname
from student
where not exists
(select *
from sc
where snumber=student.snumber and cno='1')

用连接运算来实现上面的查询。
要高手！！ 谢谢！！

很简单(适用于ORACLE,SQLSERVER ACCESS等)：
select distinct student.* from student left join sc on sc.snumber=student.snumber and sc.cno='1' where sc.snumber is null

好复杂，我用的是oracle里面的写法，判断字段为空的方法各种数据库不一致，你自己看着修改就行，基本思路是这样：

select s.*
from student s left outer join (select * from sc where cno='1') sc
on sc.snumber=s.snumber
where sc.cno is null ;

使用了嵌套sql

select sname
from student
where not in
(select *
from sc
where snumber=student.snumber and cno='1')

select sname from student ,sc where sc.snumber=student.snumber and sc.cno <> '1'
或者
select sname from student ,sc where sc.snumber=student.snumber and sc.cno != '1'

select sname
from student
where not in
(select *
from sc
where snumber=student.snumber and cno='1')