银行面试题：左连接，以及and筛选和where筛选的区别

create table Aa(
id number(5),
name varchar2(10)
)
​
insert into Aa values (1,'A');
insert into Aa values (2,'B');
insert into Aa values (3,'C');
insert into Aa values (4,'D');
insert into Aa values (5,'E');
insert into Aa values (6,'F');
insert into Aa values (7,'G');
insert into Aa values (8,'H');
​
​
select * from Aa;

 

create table Ab(
id number(5),
name varchar2(10)
)
​
insert into Ab values (1,'A');
insert into Ab values (2,'b');
insert into Ab values (3,'C');
insert into Ab values (4,'d');
insert into Ab values (5,'E');
insert into Ab values (6,'f');
​
select * from Ab;

 

 

create table Ac(
id number(5),
name varchar2(10)
);
​
insert into Ac values (1,'A');
insert into Ac values (2,'B');
insert into Ac values (3,'C');
insert into Ac values (1,'A');
insert into Ac values (2,'B');
insert into Ac values (3,'C');
​
select * from Ac;

 

 

Aa表和Ab表的左连接，保留join左边的全部数据；

select *
from Aa a
left join Ab b
on a.id=b.id; 

 

 

Aa表和Ac表的左连接，保留join左边的全部数据加上重复的数据；

select *
from Aa a
left join Ac c
on a.id=c.id;

 

 

左连接里面如果使用and筛选和用where筛选的区别：

select *
from Aa a
left join Ab b
on a.id=b.id and b.id in(1,2,3)

 

 

and保留join左边的所有数据；

select *
from Aa a
left join Ab b
on a.id=b.id where b.id in(1,2,3)

 

 

where只保留相同部分的数据；