How to FULLY INTERNAL JOINT of multiple tables in MySQL

Question

I need some tables. I know how two tables are from here . But I have some tables, and I cannot apply them to them. How can i achieve this? My SQL code is below: FULL OUTER JOINFULL OUTER JOIN

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

I also tried this query:

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

The last request takes a very long time, I need a faster request.

Answer 1

I think for FULL OUTER JOINmore than three 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

Alternatively for this:

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 , .