Solution to the problem that not in is null in MySQL

Time:2020-8-11

Some time ago, when I was doing a small function in the company, I counted the number of data in a certain situation and then modified the problem. At that time, I thought it was very simple. I 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 but not in T2 at the same time. The result is: 0, that is, all the data in T1 are in T2. However, it is easy to find that some data is not in T2 in T1, so I feel very strange. This SQL looks ok. After some queries, it turns out that the C1 field of T2 contains a null value. 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 = '';

perhaps


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

Therefore, they are all caused by null (in order to avoid errors, I added the empty string), because the implementation principle of not in is to compare each t1.c1 and each t2.c1 (query results in brackets) unequally (! =).


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

However, any operation result of! = null in SQL is false, so if there is a null in T2, the query with not in will always return false, that is, the query result is empty.

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developeppaer.