inner join和left join和outer join

1、有两个表数据

a、表accu

create table accu
(
    day_code varchar2(10),
    buss_month varchar2(10),
    fnum int
)
insert into accu values('20210927','202109',1);
insert into accu values('20210928','202110',2);
insert into accu values('20210927','202109',2);
insert into accu values('20210929','202110',3);
insert into accu values('20210930','202110',4);
insert into accu values('20211001','202110',5);

b、表sell

create table sell
(
  day_code varchar2(10),
  sname varchar2(50),
  fruit varchar2(50)  
)
insert into sell values('20210927','张三','苹果');
insert into sell values('20210927','李四','西瓜');
insert into sell values('20210930','王五','里');
insert into sell values('20211027','张三','李子');

 

2、inner join

select accu.*,sell.* from ACCU inner join sell on accu.day_code=sell.day_code;
--返回两个表中关联字段相等的行

 

 

 3、left join

select accu.*,sell.* from ACCU left join sell on accu.day_code=sell.day_code;
--返回字段关联相等时左边表和右边表组成的行，还有字段关联不相等时左边关联不成功的行，右边表字段为空组成的行

 

 4、outer join

outer joio 可分为left outer join、right outer join和full outer join。

left outer join和left join是一样的，right outer join和right join是一样的。

select accu.*,sell.* from accu full outer join sell on accu.day_code=sell.day_code;
--字段关联相等时左边表和右边表字段组成的行，字段关联不相等时左边表字段和右边表字段设为空的行，还有右边表字段和左边表字段设为空的行