【原创】JOIN 详述（上）

几种常见 join 的用法

创建表

join_order.png join_user.png

插入数据

image.png
image.png

INNER JOIN

select * from join_user u inner join join_order o on u.id = o.user_id
结果如下：

inner join result.png
结论：在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

LEFT JOIN

select * from join_user u left join join_order o on u.id = o.user_id
结果如下：

left join result.png
结论：left join 会返回左表所有的行，即使右表中没有匹配的行

select * from join_user u left join join_order o on u.id = o.user_id WHERE o.user_id is null
结果如下：

image.png

结论：返回左表特有的行

RIGHT JOIN

select * from join_user u right join join_order o on u.id = o.user_id
结果如下：

right left result.png
结论：right join 会返回右表所有的行，即使左表中没有匹配的行

select * from join_user u right join join_order o on u.id = o.user_id WHERE u.id is null
结果如下：

image.png
结论：会返回右表中独有的行

full join

MySQL 不支持 full join，通过left join 、right join、union 组合实现
select * from join_user u right join join_order o on u.id = o.user_id UNION select * from join_user u left join join_order o on u.id = o.user_id
结果如下：

full join result.png

两表中都没有出现的数据

select * from join_user u right join join_order o on u.id = o.user_id WHERE u.id is null UNION select * from join_user u left join join_order o on u.id = o.user_id WHERE o.user_id is null
结果如下：

image.png