Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

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

I wanted to execute the following query:

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

But the parser says it doesn't have an OUTER JOIN clause. The parser does not provide any help at all in understanding what's wrong with my query. At minimum, it should display the text of the query with a caret under the first character of the statement that caused the parsing step to fail.

3 Answers

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.