My query has an OUTER JOIN clause but the parser incorrectly determines that it doesn't.

MySQL does not have a full OUTER JOIN, but it can be emulated:

https://dev.mysql.com/doc/refman/5.7/en/outer-join-simplification.html

The documentation at dev.mysql is inscrutable and hard to navigate. I need practical answers. I a not a DBA so whatever the mySQL parser is doing behind the scenes is of no interest to me at all. I just want my query to run correctly. LOL

After googling the topic it seems that for mySQL we can emulate a full outer join by performing the same query twice: once with LEFT JOIN and once again with RIGHT JOIN, placing a UNION ALL clause in between the two query variants. So for example perhaps my query could be rewritten:

SELECT title, IFNULL(AVG(score), 0) AS average 
FROM reviews 
LEFT JOIN movies 
ON reviews.movie_id = movies.id 
GROUP BY movie_id 
HAVING average < 2

UNION ALL

SELECT title, IFNULL(AVG(score), 0) AS average 
FROM reviews 
RIGHT JOIN movies 
ON reviews.movie_id = movies.id 
GROUP BY movie_id 
HAVING average < 2

I'll try it to see if it will execute or not.

Thank you

Hi Adiv,

Are you on this challenge? https://teamtreehouse.com/library/database-foundations/sql-calculating-aggregating-and-other-functions/grouping-joining-and-cleaning-up-2

If so, the outer join it's talking about is either LEFT OUTER JOIN or RIGHT OUTER JOIN.

The OUTER keyword is supposed to be optional in mysql but I've found the challenge requires it. Either LEFT or RIGHT is required.

right joins are converted to the equivalent left join operation so I would just recommend doing LEFT OUTER JOIN but the right join version passes as well.

The syntax would be table1 LEFT OUTER JOIN table2

table1 will have all of its rows included but table2 will not necessarily have all its rows included. Only the ones that match the on condition.

Everything in your original query was fine except for:

reviews 
FULL OUTER JOIN movies

Let me know if you need a further hint.