Strange behaviour with NOT IN
mysql, sql
Solution
I would assume that `tbl_1.clean_deep_link` is `NULL` for the rest of the rows.
These values are neither `IN` nor `NOT IN` your sub-query.
The other reason could be, that you have `NULL` in `tbl_2.clean_deep_link`.
Please try the following:
select count(1) from tbl_1
where clean_deep_link not in (select clean_deep_link
from tbl_2 WHERE clean_deep_link IS NOT NULL);
The problem with `NULL` is that it is neither `=`, nor `<>` any other value (including `NULL`).
When checking for `NOT IN`, MySQL needs to check for each value in `tbl_1` that it is not contained in `tbl_2` and thus checks if they are `<>`.
Your values were not `<> NULL`, so they were not `NOT IN`.
See also: Using NOT IN operator with null values
Check example in SQL Fiddle.
Problem
I have this table : ``` select count(distinct clean_deep_link) from tbl_1; +---------------------------------+ | count(distinct clean_deep_link) | +---------------------------------+ | 121211 | +---------------------------------+ ``` I have this query : ``` select count(1) from tbl_1 where clean_deep_link IN (select clean_deep_link from tbl_2); +----------+ | count(1) | +----------+ | 66360 | +----------+ ``` But when I change the query to `not in` it returns an empty set : ``` select count(1) from tbl_1 where clean_deep_link not in (select clean_deep_link from tbl_2); +----------+ | count(1) | +----------+ | 0 | +----------+ ``` How is this possible? if the subquery contains about half of the records, shouldn't the `not` of the subquery contain the other half? What am I missing here? Thanks