left join和right join和inner join

此图仅限于理解他们之间的关系,下面还有举例,例子更好明白.

    left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
　inner join(等值连接) 只返回两个表中联结字段相等的行

下图是t1和t2两张表格

 

t1表格

 

t2表格

 

#1.
select * from
t1 a left join t2 b
on a.L1 = b.L2;

 

#2.
select * from
t2 a right join t1 b
on a.L2 = b.L1;

 

#3.
select * from
t2 a left join t1 b on a.L2 = b.L1;

 

#4.
select * from
t2 a inner join t1 b
on a.L2 = b.L1;

 

#5.
select * from
t2 a,t1 b
where a.L2 = b.L1;

 

#6.
select * from
t1 a left join t2 b
on a.L1 = b.L2
where a.L1 = b.L2;

 

#7.
select * from
t1 a left join t2 b
on a.L1 = b.L2
where a.L1 = null;

 

#8.
select * from
t1 a left join t2 b
on a.L1 = b.L2
where b.L2 is null;

 

#9.
select * from
t1 a left join t2 b
on a.L1 = b.L2
where b.L2 is not null;

 

#10.
select * from
t1 a left join t2 b
on a.L1 = b.L2
where a.L1 = 1;

 

 

#11.
select * from
t1 a left join t2 b
on a.L1 = b.L2
where b.L2 = 1;