Mysql的七种join查询方法

为了大家使用方便代码单独拿出来

左连接 查a表独有和b表共有部分

select * from a left join b on a.key=b.key;

右连接 查b表独有和a表共有部分

select * from a right join b on a.key=b.key;

内连接 查a、b共有部分

select * from a inner join b on a.key=b.key;

只查a独有的部分

select * from a left join b on a.key=b.key where b.key is null;

只查b独有的部分

select * from a right join b on a.key=b.key where a.key is null; 

全连接

select * from a left join b on a.key=b.key union select * from a right join b on a.key=b.key;

查a、b共有的部分

select * from a left join b on a.key=b.key where b.key is null union select * from a right join b on a.key=b.key where a.key is null;

oracle 中的全连接和查a、b共有部分

select * from a full outer join b on a.key=b.key

select * from a full outer join b on a.key=b.key where a.key is null or b.key is null