Why doesn't LEFT JOIN return NULL records when used with WHERE clause?

Question

Today I've tried some more complex MySQL queries and I've noticed that MySQL's LEFT JOIN is not working with WHERE clause. I mean, it does return some records but it does not return the ones which are empty on the right side.

For example let's say we've got to tables:

albums                                   ; albums_rap
  id artist        title         tracks  ;    id artist    title           rank
---- -------- ---------- --------------- ;  ---- --------- ----- --------------
   1 John Doe     Mix CD              20 ;     3 Mark      CD #7            15
   2 Mark          CD #7              35 ;

And when I run this query:

SELECT 
    t1.artist as artist,
    t1.title as title,
    t1.tracks as tracks,
    t2.rank as rank,
FROM
    albums as t1
LEFT JOIN
    albums_rap as t2
ON 
    t1.artist LIKE t2.artist
AND
    t1.title LIKE t2.title
WHERE
    t2.rank != 17

I get this:

artist title tracks  rank
------ ----- ------ -----
Mark   CD #7     35    15

but when I replace "WHERE" with "AND" in this query I get:

artist     title tracks  rank
------ --------- ------ -----
Mark       CD #7     35    15
John Doe  Mix CD     20  NULL

Why the first one is not returning records with "NULL" (null is not equal to 17...)

I hope You understood what I meant and you'll explain somehow me the difference. Sorry for my bad english, it's not my mother tongue.

Answer 1

A left join condition and where condition filter are not both same. Data is filtered by the where clause after the physical join is done. if you look a left join it will normally return every row from your left table, but once you have a where clause, it will filter the output of the join so the result is like an inner join. You will want to focus on the two diagrams on the left side of the image below.