mysql left join、right join、inner join、union、union all使用以及图解

左外连接：left join

sql语法：LEFT JOIN = LEFT OUTER JOIN

首先需要创建两张表做测试，表数据如下所示

table 1 表：
table2 表：
查询sql：

select * from table1 a LEFT JOIN table2 b on a.id=b.id 

总结：

查询sql：

select a.id as aid, b.id as bid from table1 a LEFT JOIN table2 b on a.id=b.id where b.id is null 

右外连接：right join

sql语法：RIGHT JOIN = RIGHT OUTER JOIN

查询sql：

select * from table1 a RIGHT JOIN table2 b on a.id=b.id 

总结：

查询sql：

select a.id as aid, b.id as bid from table1 a RIGHT JOIN table2 b on a.id=b.id where a.id is null 

内连接：inner join

查询sql：

select a.id as aid, b.id as bid from table1 a INNER JOIN table2 b on a.id=b.id

联合查询：union 和 union all

union：用来聚合两个查询结果，并去除重复数据
union all：聚合查询结果，不去重

select * from table1 UNION select * from table2

select * from table1 UNION ALL select * from table2