常见通用的Join查询

sql执行顺序（以下示例，从上到下是有顺序的）

咱们写的sql语句

select distinct
	<select_list>
from
	<left_table> <join_type>
join
	<right_table> on <join_condition>
where
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
order by
	<order_by_condition>
limit
	<limit number>

机器的读取顺序

from <left_table>
on <join_condition>
<join_type> join <right_table>
where <where_condition>
group by <group_by_list>
having <having_condition>
select
distinct <select_list>
order by <order_by_condition>
limit <limit_number>

图示（顺序从左到右）

join图

内连接

select <select_list> from tableA A inner join tableB B on A.key=B.key

左连接

select <select_list> from tableA A left join tableB B on A.key=B.key

右连接

select <select_list> from tableA A right join tableB B on A.key=B.key

左外连接

select <select_list> from tableA A left join tableB B on A.key=B.key where B.key is null

右外连接

select <select_list> from tableA A right join tableB B on A.key=B.key where A.key is null

全连接

select <select_list> from tableA A full outer join tableB B on A.key=B.key

在mysql上，这句因为full outer运行了会报错，所以mysql上执行时要使用union关键字把第二种和第三种语句结合起来使用（union会自动去掉重复的部分）:

select <select_list> from tableA A left join tableB B on A.key=B.key
union
select <select_list> from tableA A right join tableB B on A.key=B.key

我也不知道叫什么连接

select <select_list> from tableA A full outer join tableB B on A.key=B.key where A.key is null or B.key is null
同上，这句也会报错，同样使用union把第四种和第五种语句结合起来用：

select <select_list> from tableA A left join tableB B on A.key=B.key where B.key is null
union
select <select_list> from tableA A right join tableB B on A.key=B.key where A.key is null