oralce 面试题一

– 创建 R 表
create table R (
id int primary key,
R1 varchar(20)
);

insert into R
values(1, ‘hello’);
insert into R
values(2, ‘world’);

select * from R;

– 创建 B 表
create table B (
id int primary key,
R2 varchar(20)
);
insert into B
values(2, ‘Java’);

insert into B
values(3, ‘H5’);

select * from B;

– 第一种方式：使用外连接处理第一张表
select r.id, r.r1, nvl(b.R2, ‘null’)
from r, b
where r.id = b.id(+)

union

select b.id, nvl(r.r1, ‘null’), b.r2
from r, b
where r.id(+) = b.id;

– 第二种方式：标准 SQL 写法
select r.id, r.r1, nvl(b.R2, ‘null’)
from r left outer join b
on r.id = b.id

union

select b.id, nvl(r.r1, ‘null’), b.r2
from r right outer join b
on r.id = b.id;

– 第三种方式：全外连接
– 初始操作
select r.id, b.id, r.r1, b.r2
from r full outer join b
on r.id = b.id;
– 最终操作
select nvl(r.id, b.id) id, nvl(r.r1, ‘null’), nvl(b.r2, ‘null’)
from r full outer join b
on r.id = b.id;