SQL: FULL Outer Join Not Working [duplicate]

Question

I have two tables, one of them is empty but the other isn't.

• hugot_votes_stats : http://prntscr.com/72ft7d
• hugot_comment_stats: Empty

I understand that I can't use Inner JOIN because it will only matches the value as specified in the ON part. In this case one table has no value.

SELECT  t0.hugot_id                     as hugot_id,
        t0.upvotes                      as upvotes,
        t1.comment_count                as comment_count
FROM
    hugot_votes_stats as t0
FULL OUTER JOIN
    hugot_comment_stats as t1
ON
    t0.hugot_id = t1.hugot_id

This is the part I figured using FULL JOIN. What I was expecting is that empty tables (in this case the comment_count) will show a default value (i.e: 0) if there is none found.

Yet I am given an error as you can see 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Answer 1

MySQL doesn't have syntax keyword FULL OUTER JOIN. You have to use combination of LEFT and RIGHT JOIN to obtain full joins.

SELECT  t0.hugot_id                     as hugot_id,
        t0.upvotes                      as upvotes,
        t1.comment_count                as comment_count
FROM
    hugot_votes_stats as t0
LEFT JOIN
    hugot_comment_stats as t1
ON
    t0.hugot_id = t1.hugot_id

UNION ALL 

SELECT  t0.hugot_id                     as hugot_id,
        t0.upvotes                      as upvotes,
        t1.comment_count                as comment_count
FROM
    hugot_votes_stats as t0
RIGHT JOIN
    hugot_comment_stats as t1
ON
    t0.hugot_id = t1.hugot_id