left join 基本用法

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/wheredata/article/details/87191983

 

废话不多说,来看例子

一、建表,导入测试数据

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;

 

temp1 temp2 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相对应,会将右边的数据全部查出来(例子略)

 

小弟刚入行,如有不妥之处,望指正

 

展开阅读全文

没有更多推荐了,返回首页