多表查询的分类
- 内连接: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记录数