Question 20135 in Mysql Question posted in
The official MySQL documentation can be found here.

Alternative to doing FULL OUTER JOIN for multiple tables? – Mysql

I needed to run the following on MySQL, but it doesn’t allow FULL OUTER JOINs:

SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
       COALESCE(t0.hits1, 0) AS hits0,
       COALESCE(t1.hits2, 0) AS hits1,
       COALESCE(t2.hits3, 0) AS hits2
       COALESCE(t3.hits3, 0) AS hits3
FROM t0
FULL OUTER JOIN t1 ON t0.date = t1.date
FULL OUTER JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date;
FULL OUTER JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date;

How can I do that in MySQL?

I understand how to do it for two tables, for example, as per this example, I could do:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

Instead of:

SELECT * FROM t1 FULL OUTER JOIN t2
ON t1.id = t2.id

But how do I do my first example? The following I’ve come up with seems wrong:

SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
        COALESCE(t0.hits1, 0) AS hits0,
        COALESCE(t1.hits2, 0) AS hits1,
        COALESCE(t2.hits3, 0) AS hits2,
        COALESCE(t3.hits3, 0) AS hits3
FROM t0
LEFT JOIN t1 ON t0.date = t1.date
LEFT JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date
LEFT JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date
UNION ALL
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
        COALESCE(t0.hits1, 0) AS hits0,
        COALESCE(t1.hits2, 0) AS hits1,
        COALESCE(t2.hits3, 0) AS hits2,
        COALESCE(t3.hits3, 0) AS hits3
FROM t1
RIGHT JOIN t0 ON t0.date = t1.date
RIGHT JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date
RIGHT JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date
WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL

Because the following bit seems too restrictive:

WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL
Tags:

2

Answers


  1. 0

    You can make this work by applying the union approach, but using separate CTEs for each sequential cross join:

    WITH cte1 AS (
    SELECT t0.date AS date0, t1.date AS date1, t0.hits1, t1.hits2
    FROM t0
    LEFT JOIN t1 ON t1.date = t0.date
    UNION ALL
    SELECT t0.date, t1.date, t0.hits1, t1.hits2
    FROM t0
    RIGHT JOIN t1 ON t1.date = t0.date
    WHERE t0.date IS NULL
),
cte2 AS (
    SELECT t1.date0, t1.date1, t2.date AS date2,
           t1.hits1, t1.hits2, t2.hits3
    FROM cte1 t1
    LEFT JOIN t2 ON COALESCE(t1.date0, t1.date1) = t2.date
    UNION ALL
    SELECT t1.date0, t1.date1, t2.date, t1.hits1, t1.hits2, t2.hits3
    FROM cte1 t1
    RIGHT JOIN t2 ON COALESCE(t1.date0, t1.date1) = t2.date
    WHERE COALESCE(t1.date0, t1.date1) IS NULL
),
cte3 AS (
    SELECT t2.date0, t2.date1, t2.date2, t3.date AS date3,
           t2.hits1, t2.hits2, t2.hits3, t3.hits4
    FROM cte2 t2
    LEFT JOIN t3 ON COALESCE(t2.date0, t2.date1, t2.date2) = t3.date
    UNION ALL
    SELECT t2.date0, t2.date1, t2.date2, t3.date,
           t2.hits1, t2.hits2, t2.hits3, t3.hits4
    FROM cte2 t2
    RIGHT JOIN t3 ON COALESCE(t2.date0, t2.date1, t2.date2) = t3.date
    WHERE COALESCE(t2.date0, t2.date1, t2.date2) IS NULL
)

SELECT COALESCE(date0, date1, date2, date3) AS date,
       COALESCE(hits1, 0) AS hits0,
       COALESCE(hits2, 0) AS hits1,
       COALESCE(hits3, 0) AS hits2
       COALESCE(hits3, 0) AS hits3
FROM cte3;
    Login or Signup to reply.
  2. 0

    This may, or may not, be a viable alternative approach for your situation. You could build a full union of all the dates first and then left join to each table:

    SELECT
    d.date,
    COALESCE(t0.hits1, 0) AS hits0,
    COALESCE(t1.hits2, 0) AS hits1,
    COALESCE(t2.hits3, 0) AS hits2
    COALESCE(t3.hits3, 0) AS hits3
FROM (
    SELECT date FROM t0 UNION
    SELECT date FROM t1 UNION
    SELECT date FROM t2 UNION
    SELECT date FROM t3
) d
LEFT JOIN t0 ON d.date = t0.date
LEFT JOIN t1 ON d.date = t1.date
LEFT JOIN t2 ON d.date = t2.date
LEFT JOIN t3 ON d.date = t3.date;

    or possibly replacing the union of all the dates with a recursive cte:

    WITH RECURSIVE d (date) AS (
    SELECT '2023-01-01'
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM d
    WHERE date + INTERVAL 1 DAY <= CURRENT_DATE
)
SELECT d.date,
       COALESCE(t0.hits1, 0) AS hits0,
       COALESCE(t1.hits2, 0) AS hits1,
       COALESCE(t2.hits3, 0) AS hits2
       COALESCE(t3.hits3, 0) AS hits3
FROM d
LEFT JOIN t0 ON d.date = t0.date
LEFT JOIN t1 ON d.date = t1.date
LEFT JOIN t2 ON d.date = t2.date
LEFT JOIN t3 ON d.date = t3.date;

    and you could add a having clause to remove dates with no hits:

    HAVING hits0 OR hits1 OR hits2 OR hits3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search