left join理解

create table lily1 (idx number(12), unamex varchar2(100));

create table lily2 (idy number(12), unamey varchar2(100));

create table lily3 (idz number(12), unamez varchar2(100));

 

insert into lily1 values ( 1, 'flower1');

insert into lily1 values ( 2, 'flower2');

insert into lily1 values ( 3, 'flower3');

 

insert into lily2 values ( 1, 'flowertype1');

insert into lily2 values ( 2, 'flowertype2');

 

insert into lily3 values ( 1, 'flowercolor1');

insert into lily3 values ( 2, 'flowercolor2');

insert into lily3 values ( 3, 'flowercolor3');

 

--SQL1 left join就像是from, 只不过隐含了left join的意思

select * from lily1 a 

left join lily2 b  on a.idx=b.idy

left join lily3 c  on b.idy=c.idz

where a.idx=1

 

--SQL1的运行结果

IDXUNAMEXIDYUNAMEYIDZUNAMEZ

1flower11flowertype11flowercolor1

 

 

SELECT * FROM lily1 A LEFT JOIN lily2 B ON A.idx=B.idy WHERE B.idy=2; --对外连结果加条件

SELECT * FROM lily1 A LEFT JOIN lily2 B ON A.idx=B.idy AND B.idy=2;   --Ｂ表添加过滤条件为与Ａ外连

 

 

 

drop table lily1;

drop table lily2;

drop table lily3;