'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 |
|---|
