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;