MySQL多表查询笔记

多表查询的分类

• 内连接：join，inner join
• 外连接：left join，left outer join，right join，right outer join，union
• 交叉连接：cross join

内连接

select a.*, b.* from tablea a  
join tableb b  
on a.id = b.id  

外连接

左连接

select a.*, b.* from tablea a  
left join tableb b  
on a.id = b.id  

TABLE B中不存在的记录填充为Null

右连接

select a.id aid,a.age,b.id bid,b.name from tablea a  
right join tableb b  
on a.id = b.id  

TABLE A中不存在的记录填充为Null

right join + where A.column is null

select a.id aid,a.age,b.id bid,b.name from tablea a  
right join tableb b  
on a.id = b.id  
where a.id is null  

全连接（left join union right join）

select a.id aid,a.age,b.id bid,b.name from tablea a  
left join tableb b  
on a.id = b.id  
union  
select a.id aid,a.age,b.id bid,b.name from tablea a  
right join tableb b  
on a.id = b.id  

full join +is null(left join +is null)union(right join + is null)

select a.id aid,a.age,b.id bid,b.name from tablea a  
left join tableb b  
on a.id = b.id  
where b.id is null  
union  
select a.id aid,a.age,b.id bid,b.name from tablea a  
right join tableb b  
on a.id = b.id  
where a.id is null  

交叉连接（cross join）

TableA：

TableB:

select a.id aid,a.age,b.id bid,b.name from tablea a  
cross join tableb b  

A记录数*B记录数