'MySQL One-to-many association with where clause and null check

A fiddle of the question below can be found here.

I'm attempting to get a list of all articles regardless of their joined association.

Let's say we have the following tables:

articles

id title
1 Article 1
2 Article 2
3 Article 3

article_translations

id article_id language
1 1 nl-NL
2 2 en-GB

Notice there is no entry for article_translations.article_id = 3.

The goal is to look up an article's associated language and also return the article record even if it's missing an entry in article_translations. The query I'm using now is the following:

SELECT
    articles.id,
    MAX(article_translations.language)        
FROM articles
LEFT JOIN
    article_translations ON article_translations.article_id = articles.id

WHERE  (
    article_translations.language = 'nl-NL'
    or (
        article_translations.language IS NULL
    )
)
GROUP BY articles.id

The expected result would be:

id language
1 NULL
2 en-GB
3 NULL

The actual result is:

id language
2 en-GB
3 NULL

This issue seems to be caused by the article_translations table actually containing a valid join for the desired record, but the where-clause seems to filter it out somehow.

If we change the query to look up nl-NL in stead of en-GB, we find the previously missing record, but now miss the previously found record.

id language
1 nl-NL
3 NULL

Does anyone have any clue what's actually going on and how to get the results I need?

--- UPDATE ---

Thanks @Bill Karwin for pointing me in the right direction. Based on his left outer join I managed to create another query that returns the results that I expected:

left outer join fiddle

SELECT
    articles.id,
    MAX(article_translations.language)        
FROM articles
LEFT OUTER JOIN
    article_translations ON article_translations.article_id = articles.id
    AND (
        article_translations.language = 'en-GB'
        OR (
            article_translations.language IS NULL
        )
    )
GROUP BY articles.id

I am still wondering however, why the left outer join combined with a clause returns the expected results, but when using the same join, combined with a where clause returns different results:

left outer join + where clause fiddle

SELECT
    articles.id,
    MAX(article_translations.language)        
FROM articles
LEFT OUTER JOIN
    article_translations ON article_translations.article_id = articles.id    
WHERE (
        article_translations.language = 'en-GB'
        OR (
            article_translations.language IS NULL
        )
    )
GROUP BY articles.id

So the question that remains is: is it possible to have a join (without a clause), combined with a where that is able to returns all article's in the set.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source