由于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 | 车子详情 |
+--------+----------+------------+--------------+