Mysql 多表查询

内连接：

 

 

 两张表共有的部分(内连接)，取交集

select * from student t1
inner join st t2 on t1.s_id=t2.s_id

左连接：

 

 

 A独有的部分加上和A和B公共 的部分。也叫左外连接。

select * from student t1
left join st t2 on t1.s_id=t2.s_id

 

 

 这张图就是A表独有的部分。

select * from student t1
left join st t2 on t1.s_id=t2.s_id
where t2.s_id is null

右连接：

 

 

  b独有的部分加上和A和B公共 的部分。也叫右外连接。

select * from student t1
right join st t2 on t1.s_id=t2.s_id

 

 

 这张图是B表独有的部分。

select * from student t1
right join st t2 on t1.s_id=t2.s_id
where t1.s_id is null

 全连接：

 

 

 

select * from student t1
left join st t2 on t1.s_id=t2.s_id
union all
select * from student t1
right join st t2 on t1.s_id=t2.s_id

全外连接：

select * from student t1
left join st t2 on t1.s_id=t2.s_id
where t2.s_id is null
union all
select * from student t1
right join st t2 on t1.s_id=t2.s_id
where t1.s_id is null

参考链接