'mysql select rows from table b not present in table a not working

I thought this should work:

SELECT * 
FROM `table1` a 
left join table2 b on b.word = a.word 
where b.id is null;

Count of results: 823,184. (all results are empty, not joined)

Btw this query, which does something very other, joining only results presented in both tables:

SELECT * 
FROM `table1` a 
join table2 b on a.word = b.word;

Count of results: 823,184. (all results are joined)

So what is problem with the first query? And btw even the second results must be wrong, because I know that some data in table A are not in table B.

Both tables word columns is utf8_bin.

====

Edit: this is the only query that works as expected.

select * 
from ( SELECT a.*, 
              b.id as bid 
       FROM `table1` a 
       left join table2 b on b.word = a.word
     ) t 
where t.bid is null;

Count of results: 142k, only those present in table a, missing in table b.



Sources

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

Source: Stack Overflow

Solution Source