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记录数