join orcl的left_Oracle关联查询关于left/right join的那点事

/*题外话

--更改foreign key约束定义引用行(delete cascade/delete set null/delete no action),默认delete on action

--引用行(当主表条记录被删除时确定何处理字表外部码字段)：

--delete cascade : 删除子表所有相关记录

--delete set null : 所有相关记录外部码字段值设置NULL

--delete no action: 做任何操作

--left 以左表为主，左表中的数据都查询出来

--约束唯一 unique

--多对多

*/

drop table stud;

drop table course;

select * from USER_TABLES;

--创建学生表

create table stud(

id int primary key,

name varchar(30)

);

--课程表

create table course(

id int primary key,

name varchar(30),

hours int

);

--

create table sc (

sid int ,

cid int,

constraint sc_pk primary key(sid,cid),

constraint sc_fk1 FOREIGN key(sid) references stud(id),

constraint sc_fk2 FOREIGN key(cid) references course(id)

);

select * from sc;

--先写入几个学生

insert into stud values(1,'Jack');

insert into stud values(2,'张三');

insert into stud values(3,'李四');

insert into stud values(4,'Rose');

--再写入几个课程

insert into course values(101,'Java',120);

insert into course values(102,'C#',60);

insert into course values(103,'Oracle',75);

insert into course values(104,'.NET',60);

commit;

--开始选课

insert into sc values (1,101);

insert into sc values (1,102);

insert into sc values (2,101);

insert into sc values (3,104);

commit;

-----------------------------------------------

-------------------开始查询---------------------

-----------------------------------------------

--查学生选了什么课

select s.name as 学生,c.name as 成绩

from stud s,course c,sc

where s.id=sc.SID and c.id=sc.CID;

-----inner join

select s.name ,c.name from stud s

inner join sc on s.id=sc.sid

inner join course c on c.id=sc.cid;

--查询没有选课的

select s.name,c.name from stud s

LEFT join sc on s.id=sc.SID

LEFT join COURSE c on c.ID=sc.cid

where c.name is null;

select s.name from course c

right join sc on c.id=sc.cid

right join stud s on s.id=sc.sid

where c.name is null;

--查询那些课没人选

select s.name,c.name as cname

from stud s

right join sc on s.id=sc.sid

right join course c on sc.cid=c.id

where s.name is null;

select s.name,c.name as cname

from course c

left join sc on c.id=sc.cid

left join stud s on sc.sid=s.id

where s.name is null;