Alternative to doing FULL OUTER JOIN for multiple tables?

Question

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

@GoldenLion A cross join can't do an outer join. If any one of the tables were empty, the cross join would be empty. — Bill Karwin, Apr 10 '23 at 14:43
you can do a left join with one dataset and right join with another and union all — Golden Lion, Apr 10 '23 at 17:19

Tim Biegeleisen · Answer 1 · 2023-04-10T16:19:13.070

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;

Shouldn't cte2's right join WHERE be `WHERE COALESCE(t1.date0, t1.date1) is NULL` that is the left table key of the right join exactly? Same for cte3. — Serg, Apr 10 '23 at 16:14
Thank you for your time! This is my second favourite answer, if the other doesn't work then I will try this — Jodes, Apr 10 '23 at 18:41

score 1 · Accepted Answer · answered Apr 10 '23 at 16:49

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

Thank you so much! Your first answer looks ideal, it makes a great deal of sense to me, it looks so straight forward! thank you for your time — Jodes, Apr 10 '23 at 18:40

Alternative to doing FULL OUTER JOIN for multiple tables?

2 Answers2