SQL: FULL Outer Join Not Working

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

Answer 2

You're getting that error because MySQL does not support (or recognize) the FULL OUTER JOIN syntax.

However, it is possible emulate a FULL OUTER JOIN in MySQL.

We actually need two queries.

One query return all the rows from the table on the left. (A left outer join.)

We need to append to that the results from a second query, which looks just like the first, except that we need the table on the right side to be the driver and we need to eliminate all rows that had a match (to avoid duplicating rows that were returned in the first query.)

We append the results from the second query to the first using a UNION ALL set operator.

As an example:

SELECT t0.hugot_id                     AS hugot_id
     , t0.upvotes                      AS upvotes
     , t1.comment_count                AS comment_count
  FROM hugot_votes_stats t0
  LEFT
  JOIN hugot_comment_stats 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 t0
 RIGHT
  JOIN hugot_comment_stats t1
    ON t0.hugot_id = t1.hugot_id
 WHERE t0.hugot_id IS NULL

Note the predicate in the WHERE clause of the second query. That filters out all rows that found a match. (Those rows were already returned by the first query; the second query uses an "anti-join" pattern to return rows from t1 that don't have a match.

Answer 3

You could use something like this to show your information:

SELECT  t0.hugot_id,
        t0.upvotes,
        ifnull(t1.comment_count,0) as commentcount
FROM
    hugot_votes_stats as t0
left join
    hugot_comment_stats as t1
ON
    t0.hugot_id = t1.hugot_id