mysql实现full outer join

由于mysql设计时不支持full outer join,

所以如果有全连接需求时，需要一点小技巧来实现。

full outer join = left outer join union all right outer join

看如下表结构：

A:

+------+----------+
| id   | username |
+------+----------+
|    1 | wuwh     |
|    2 | meimei   |
+------+----------+

B:

+------+------------+
| id   | objectname |
+------+------------+
|    1 | 电子     |
|    3 | 衣服     |
+------+------------+

C:

+------+--------------+
| id   | detail       |
+------+--------------+
|    1 | 电子详情 |
|    3 | 衣服详情 |
|    4 | 车子详情 |
+------+--------------+

先实现一个小需求：

A full outer join B之后，合并两列id，并且id不能重复,sql如下：

select distinct aaabbb.aaabbb_id,username,objectname from(

SELECT 

CASE 

WHEN aaa.id IS NOT NULL THEN aaa.id 

WHEN bbb.id IS NOT NULL THEN bbb.id 

END AS aaabbb_id, 

username, objectname

 FROM aaa left join bbb on aaa.id = bbb.id 

union all 

SELECT 

CASE 

WHEN aaa.id IS NOT NULL THEN aaa.id 

WHEN bbb.id IS NOT NULL THEN bbb.id 

END AS aaabbb_id, 

username, objectname 

FROM aaa right join bbb on aaa.id = bbb.id;

)ccc

结果：

+-----------+----------+------------+
| aaabbb_id | username | objectname |
+-----------+----------+------------+
|         1 | wuwh     | 电子     |
|         2 | meimei   | NULL       |
|         3 | NULL     | 衣服     |
+-----------+----------+------------+

接下来是三路full outer join

SELECT DISTINCT com_id
, username
, objectname
, detail
FROM (
SELECT IF(aaabbb.aaabbb_id IS NOT NULL, aaabbb.aaabbb_id, IF(ccc.id IS NOT NULL, ccc.id, '')) AS com_id
, username
, objectname
, detail
FROM (
SELECT CASE 
WHEN aaa.id IS NOT NULL THEN aaa.id
WHEN bbb.id IS NOT NULL THEN bbb.id
END AS aaabbb_id
, username
, objectname
FROM aaa
LEFT OUTER JOIN bbb
ON aaa.id = bbb.id
UNION ALL
SELECT CASE 
WHEN aaa.id IS NOT NULL THEN aaa.id
WHEN bbb.id IS NOT NULL THEN bbb.id
END AS aaabbb_id
, username
, objectname
FROM aaa
RIGHT OUTER JOIN bbb
ON aaa.id = bbb.id
) aaabbb
LEFT OUTER JOIN ccc
ON aaabbb.aaabbb_id = ccc.id
UNION ALL
SELECT IF(aaabbb.aaabbb_id IS NOT NULL, aaabbb.aaabbb_id, IF(ccc.id IS NOT NULL, ccc.id, '')) AS com_id
, username
, objectname
, detail
FROM (
SELECT CASE 
WHEN aaa.id IS NOT NULL THEN aaa.id
WHEN bbb.id IS NOT NULL THEN bbb.id
END AS aaabbb_id
, username
, objectname
FROM aaa
LEFT OUTER JOIN bbb
ON aaa.id = bbb.id
UNION ALL
SELECT CASE 
WHEN aaa.id IS NOT NULL THEN aaa.id
WHEN bbb.id IS NOT NULL THEN bbb.id
END AS aaabbb_id
, username
, objectname
FROM aaa
RIGHT OUTER JOIN bbb
ON aaa.id = bbb.id
) aaabbb
RIGHT OUTER JOIN ccc
ON aaabbb.aaabbb_id = ccc.id
) ddd;

结果：

+--------+----------+------------+--------------+
| com_id | username | objectname | detail       |
+--------+----------+------------+--------------+
| 1      | wuwh     | 电子     | 电子详情 |
| 3      | NULL     | 衣服     | 衣服详情 |
| 2      | meimei   | NULL       | NULL         |
| 4      | NULL     | NULL       | 车子详情 |
+--------+----------+------------+--------------+