如何在 MySQL 中完全 OUTER JOIN 多个表
我需要FULL OUTER JOIN
多个表.我知道如何从这里FULL OUTER JOIN
两个表.但是我有几张表,我无法将其应用于它们.我怎样才能实现它?
我的 SQL 代码如下:
I need to FULL OUTER JOIN
multiple tables. I know how to FULL OUTER JOIN
two tables from here. But I have several tables, and I can't apply it over them. How can I achieve it?
My SQL code, below:
INSERT INTO table
(
customer_id
,g01
,g02
,g03
,has_card
,activity
)
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
LEFT JOIN s_category sc
ON sc.customer_id = sgd.customer_id
UNION
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
RIGHT JOIN s_category sc
ON sc.customer_id = sgd.customer_id
UNION
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
LEFT JOIN s_activity a
ON a.customer_id = sgd.customer_id
UNION
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
RIGHT JOIN s_activity a
ON a.customer_id = sgd.customer_id
我也试过这个查询:
INSERT INTO reportls.table
(
customer_id
,g01
,g02
,g03
,has_card
,activity
)
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
LEFT JOIN s_category sc
ON sc.customer_id = sgd.customer_id
LEFT JOIN s_activity a
ON sc.customer_id = sgd.customer_id
UNION
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
LEFT JOIN s_category sc
ON sc.customer_id = sgd.customer_id
RIGHT JOIN s_activity a
ON a.customer_id = sgd.customer_id
UNION
SELECT sgd.customer_id, sgd.g01,sgd.g02,sgd.g03,sc.value, a.activity
FROM s_geo_data sgd
RIGHT JOIN s_category sc
ON sc.customer_id = sgd.customer_id
LEFT JOIN s_activity a
ON a.customer_id = sgd.customer_id
最后一个查询执行很长时间,我需要更快的查询.
Last query executes very long time, I need faster query.
推荐答案
我认为有一个 FULL OUTER JOIN
超过 3 个表,你需要这样做:
I think to have a FULL OUTER JOIN
over 3 tables, you need to do it like this:
SELECT t1.value, t2.value, t3.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value
LEFT JOIN t3 ON t1.value = t3.value
UNION ALL
SELECT t1.value, t2.value, t3.value
FROM t2 LEFT JOIN t1 ON t1.value = t2.value
LEFT JOIN t3 ON t2.value = t3.value
WHERE t1.value IS NULL
UNION ALL
SELECT t1.value, t2.value, t3.value
FROM t3 LEFT JOIN t1 ON t1.value = t3.value
LEFT JOIN t2 ON t2.value = t3.value
WHERE t1.value IS NULL AND t2.value IS NULL
作为替代方案:
SELECT t1.value, t2.value, t3.value
FROM t1 FULL OUTER JOIN t2 ON t1.value = t2.value
FULL OUTER JOIN t3 ON t1.value = t3.value
<小时>
我建议你创建一些临时表,比如 t1
、t2
和 t3
来存储你的查询结果,然后使用上面的查询
I suggest you to create some temporary tables like t1
, t2
and t3
for storing results of your queries, then use above query over those.
相关文章