mysql 实现full join_mysql 里怎么实现full join

mysql现在还不支持full join ，那如果有这种需求怎么办呢？可以用union all来实现

数据源如下

CREATE TABLE L AS

SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL

SELECT 'left_2','2' AS v FROM dual UNION ALL

SELECT 'left_3','3' AS v FROM dual UNION ALL

SELECT 'left_4','4' AS v FROM dual;

CREATE TABLE R AS

SELECT 'right_3' AS str,'3' AS v FROM dual UNION ALL

SELECT 'right_4','4' AS v FROM dual UNION ALL

SELECT 'right_5','5' AS v FROM dual UNION ALL

SELECT 'right_6','6' AS v FROM dual;

只需要left join 加 反连接就可以了

SELECT l.str AS left_str, r.str AS right_str

FROM l

LEFT JOIN r ON r.v = l.v

UNION ALL

SELECT l.str AS left_str, r.str AS right_str

FROM l

RIGHT JOIN r ON r.v = l.v

WHERE l.v IS NULL

ORDER BY 1 NULLS LAST, 2 NULLS LAST;