幾種常見(jiàn) join 的用法
創(chuàng)建表
插入數(shù)據(jù)
INNER JOIN
select * from join_user u inner join join_order o on u.id = o.user_id
結(jié)果如下：
結(jié)論：在表中存在至少一個(gè)匹配時(shí)，INNER JOIN 關(guān)鍵字返回行。
LEFT JOIN
select * from join_user u left join join_order o on u.id = o.user_id
結(jié)果如下：
結(jié)論：left join 會(huì)返回左表所有的行，即使右表中沒(méi)有匹配的行
select * from join_user u left join join_order o on u.id = o.user_id WHERE o.user_id is null
結(jié)果如下：
結(jié)論：返回左表特有的行
RIGHT JOIN
select * from join_user u right join join_order o on u.id = o.user_id
結(jié)果如下：
結(jié)論：right join 會(huì)返回右表所有的行，即使左表中沒(méi)有匹配的行
select * from join_user u right join join_order o on u.id = o.user_id WHERE u.id is null
結(jié)果如下：
結(jié)論：會(huì)返回右表中獨(dú)有的行
full join
MySQL 不支持 full join，通過(guò)left join 、right join、union 組合實(shí)現(xiàn)
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
結(jié)果如下：
兩表中都沒(méi)有出現(xiàn)的數(shù)據(jù)
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
結(jié)果如下：
