如何在MySQL中对外部表进行FULL OUTER JOIN [英] How to FULL OUTER JOIN multiple tables in MySQL

问题描述

我需要 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.

推荐答案

我认为要在3个表中使用FULL OUTER JOIN，您需要这样做:

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.

这篇关于如何在MySQL中对外部表进行FULL OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！