Solution to the problem of null in the column of NOT IN filling pit in MySQL

When I was doing a small function in the company for a period of time, I counted how many pieces of data there were under certain circumstances and then modified the problem. At that time, I felt very simple and wrote the following SQL:

SELECT COUNT(*) FROM t1 where tl.c1 not IN (SELECT t2.c1 FROM t2);

The expected result is: how many pieces of data are in t1 and not in t2, and the result is: 0, that is, the data in t1 are all in t2, but it is easy to find that some data are in t1 but not in t2, so it feels strange that this SQL looks fine. After 1 query, it turned out that the c1 field of t2 contains the null value, and the expected results can be obtained by modifying the following two forms:

SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c1 IS NULL OR t2.c1 = '';

Or

select COUNT(*) from t1 where t1.c1 not in (
select t2.c1 from t2 where t2.c1 is not null AND t2.c1 != ''
);

So it's all caused by null (I've added empty strings to avoid errors), because not in is implemented by unequal comparisons (! =) between every t1. c1 and every t2. c1 (query results in parentheses).

foreach c1 in t2:
if t1.c1 != c1:
continue
else:
return false
return true

And any in SQL! = The operation results of null are all false, so if there is one null in t2, the query of not in will always return false, that is, the query result is empty.

Summarize