oracle中左外连接和右外连接
为了方便理解,举例说明是最好的了,如下:
外连接分为:①左外连接②右外连接③完全外连接
create table stu(
id number,
name varchar2(32)
);
insert into stu values(1,'Jack');
insert into stu values(2,'Tome');
insert into stu values(3,'Kitty');
insert into stu values(4,'Mark');
create table exam(
id number,
grade number
);
insert into exam values(1,56);
insert into exam values(2,46);
insert into exam values(11,22);
习题1:显示所有人的成绩,如果没有成绩,也要显示该人的id和name,成绩显示为空
方法一:select stu.id,stu.name,exam.grade from stu left join exam on stu.id=exam.id;
方法二:select stu.id,stu.name,exam.grade from stu , exam where stu.id=exam.id(+);//没有"+"的一方就是左表,需要//全都显示的
方法三:select stu.id,stu.name,exam.grade from exam right join stu on stu.id=exam.id;
方法四:select stu.id,stu.name,exam.grade from stu ,exam where exam.id(+)=stu.id;