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

Some time ago, when I was working on a small function in the company, I counted how many data there were in a certain situation and then modified the problem. It seemed very simple at the time, so 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 data are in t1 and not in t2? The result is: 0, that is, all the data in t1 are in t2. However, it is easy to find that some data is in t1 but not in t2, so it feels very strange. This SQL seems to be fine. After some searching, it turns out that the c1 field of t2 contains a null value. Modifying it to the following two forms can get the expected result:

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 also added the empty string to avoid errors). The reason is that the implementation principle of not in is to compare each t1.c1 with each t2.c1 (the query results in the brackets) for inequality (!=).

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

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.