Left join resulting in null rows for column with no null rows

This has me confused, I'm getting NULL columns for this query even though the table movie_image doesn't have any NULL values in the column mi.filename.

SELECT m.*, 开发者_开发技巧mi.filename, COUNT(p.movieid) AS publicationsCount
                FROM movies m
           LEFT JOIN (movie_publications p, movie_image mi)
                  ON (m.movieid = p.movieid
                      AND
                      p.movieid = mi.movieid)
               GROUP BY m.movieid

A left Join will take all rows from the first table (movies) and try to match with the second table (movie_publications). When it's not possible NULL columns will be inserted instead of the columns of the second table.

If you don't want this to happen you should use an INNER JOIN.

UPDATE: you said in the comments that movies can have or not publication, but will always have an image, so you could rewrite the query as follows. In case you're not totally sure that all movies have an image you could use LEFT JOIN also for movie_image.

SELECT m.*, mi.filename, COUNT(p.movieid) AS publicationsCount
    FROM movies m
    LEFT JOIN movie_publications p
        ON (m.movieid = p.movieid)
    INNER JOIN movie_image mi
        ON (m.movieid = mi.movieid)
    GROUP BY m.movieid

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table, and NULL is for the no matches.

If I get you right, it is exactly what LEFT JOIN is for: to get a result even for "non-matching joins".

Your query walks through movies and emits at least one result row for every row, even if there is no "matching partner" in the other tables.