join的七种用法

join的7种连接方式

sq199语法才有join

语法:

​ select 查询列表

​ from 表1 别名 [连接类型]

​ join 表2 别名

​ on 连接条件

​ [where 筛选条件]

​ [group by 分组]

​ [having 筛选条件]

​ [order by 排序列表]

分类:

内连接(※):inner join

外连接:

​ 左外连接(※):left[outer]

​ 右外连接(※):right[outer]

​ 全外连接:full[outer]

内连接(inner join)

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

左连接(left join)

第一种

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

第二种

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

右连接(right join)

第一种

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

第二种

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

全连接(full join)（MySQL不支持）

第一种

//常规写法（MySQL不支持）
select * from a full join b on a.key = b.key

//Mysql写法
select * from a left join b on a.key = b.key
union
select * from a right join b on a.key = b.key

第二种

//常规写法（MySQL不支持）
select * from a full join b on a.key = b.key 
where a.key is null or b.key is null

//MySQL写法
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