drop table stu_cour;
drop table student;
drop table course;

create table student(
sid char(10) constraint student_sid_pk primary key, --学号
sname varchar(20) not null --姓名
);

create table course(
cid char(10) constraint course_cid_pk primary key, --学号
cname varchar(20) not null --班级名
);

create table stu_cour(
sid char(10) constraint stu_cour_sid_fk references student(sid),
cid char(10) constraint stu_cour_cid_fk references course(cid),
score number(4,1) constraint stu_cour_score_ck check (score between 0 and 100),
constraint stu_cour_sid_cid_pk primary key (sid,cid)
);

drop sequence s1;

create sequence s1
start with 1001
increment by 1;

drop sequence s2;

create sequence s2
start with 1001
increment by 1;

insert into student(sid, sname) values ('s'||s1.nextval,'a');
insert into student(sid, sname) values ('s'||s1.nextval,'b');
insert into student(sid, sname) values ('s'||s1.nextval,'c');

insert into course(cid, cname) values ('c'||s2.nextval, 'c1');
insert into course(cid, cname) values ('c'||s2.nextval, 'c2');
insert into course(cid, cname) values ('c'||s2.nextval, 'c3');

insert into stu_cour(sid, cid, score) values ('s1001', 'c1001', 90);
insert into stu_cour(sid, cid, score) values ('s1001', 'c1002', 80);
insert into stu_cour(sid, cid, score) values ('s1001', 'c1003', 80);

insert into stu_cour(sid, cid, score) values ('s1002', 'c1001', 90);
insert into stu_cour(sid, cid, score) values ('s1002', 'c1002', 80);

insert into stu_cour(sid, cid, score) values ('s1003', 'c1001', 90);
insert into stu_cour(sid, cid, score) values ('s1003', 'c1002', 80);
insert into stu_cour(sid, cid, score) values ('s1003', 'c1003', 80);
commit;

select c.cid
from course c left join stu_cour sc 
on c.cid = sc.cid 
and sc.sid = ('s1002') --对
where sc.sid is null and sc.cid is null;
这样可以实现

select c.cid
from course c left join stu_cour sc 
on c.cid = sc.cid 
and (sc.sid = (select sid
                    from student
                    where sname = 'b')) -- 错了
where sc.sid is null and sc.cid is null;
但将其换成子查询为啥就不可以了？子查询的结果是's1002'啊

各个子句的执行顺序不是 from --> and --> on --> where --> select 么？

--应该改为in就可以了，因你的sc.sid为一条记录，不能等于一个记录集，只能是存在于(in)
select c.cid
from course c left join stu_cour sc 
on c.cid = sc.cid 
and (sc.sid in (select sid
                    from student
                    where sname = 'b')) 
where sc.sid is null and sc.cid is null;

------解决方案--------------------
探讨

SQL code



drop table stu_cour;
drop table student;
drop table course;

create table student(
sid char(10) constraint student_sid_pk primary key, --学号
sname varchar(20) not null --姓名
);

create tabl……


------解决方案--------------------
上面写了这么多建表语句  直接上下面俩sql就可以了 

唯一关系才返回一个值  多条记录 in返回数据集