left join 基本用法_left join用法

 

废话不多说，来看例子

一、建表，导入测试数据

create table temp1
(
  aid       VARCHAR2(5) not null,
  car       VARCHAR2(10) not null
); 
 
create table temp2
(
  bid       VARCHAR2(5) not null,
  username       VARCHAR2(10) not null
);
 
create table temp3
(
  cid        VARCHAR2(5) not null,
  dogname       VARCHAR2(10) not null
);  
 
insert into temp1(aid,car) values('001','benz');           
insert into temp1(aid,car) values('001','BMW');
insert into temp1(aid,car) values('001','ford');
insert into temp1(aid,car) values('001','jeep');
insert into temp1(aid,car) values('002','jeep');
insert into temp1(aid,car) values('003','hongqi');
 
insert into temp2(bid,username) values('001','mayun');           
 
insert into temp3(cid,dogname) values('001','lily'); 
insert into temp3(cid,dogname) values('001','lucy');
insert into temp3(cid,dogname) values('002','xiaohua');

查一下数据长什么样：

select * from temp1;
select * from temp2;
select * from temp3;

 

 

 

 

 

 

 

二、左连接测试 

--1.左连接，把左边的全部查出来，右边有的则匹配，没有则为null

select * from temp1 t1 left join temp2 t2 on t1.aid=t2.bid ;

 

select * from temp2 t2 left join temp1 t1 on t2.bid=t1.aid ;

--2.若是三张表，通过两个left join来连接，则把前面两张表先left join之后当作一张表，然后再与第三张表left join，同理，多张表的left join 以此类推

select * from temp1 t1 left join temp2 t2 on t1.aid=t2.bid left join temp3 t3 on t2.bid=t3.cid ;

select * from temp3 t3 left join temp1 t1 on t3.cid=t1.aid left join temp2 t2 on t3.cid=t2.bid;

--3.right join 与left join相对应，会将右边的数据全部查出来（例子略）

 

 

-- 一年多以后回过头来，发现第三张表的数据没有造好，也不想更正了，将就看吧，见谅