Strange behavior with NOT IN

Question

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 request 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, should not subquery contain the other half? What am I missing here?

thanks

Answer 1

I would suggest that tbl_1.clean_deep_link is NULL for the rest of the lines.

These values ​​are not IN and NOT IN your subquery.

---

Another reason may be that you have NULL in tbl_2.clean_deep_link .

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 NOT IN MySQL must check 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 the NOT IN operator with null values

---

Check out the example in SQL Fiddle .

Answer 2

MySQL's NULL columns are considered excellent, so the three NULL values ​​are considered different.

As mentioned elsewhere, you cannot compare NULL with other values ​​using the usual comparison operators, including IN and NOT IN .

The following statements can handle NULL values.

 x <=> y - returns 1 if x == y (even if both are NULL) x IS NULL - returns 1 if x is null x IS NOT NULL - returns 0 if x is null 

Answer 3

 SELECT COUNT(*) - COUNT(1) FROM tbl_1 WHERE clean_deep_link IN ( SELECT clean_deep_link FROM tbl_2 );