Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to solve the problem of NOT IN filling holes in MySQL as null, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

When the company is doing a small function, count how many pieces of data there are in a certain situation, and then modify the problem. It felt very simple at that time. A SQL was written as follows:

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 is not in T2 in T1, so it feels strange. This SQL looks fine. After some query, it turns 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 added the empty string to avoid errors), because not in is implemented by comparing each t1.c1 and each t2.c1 (query result in parentheses) unequally (! =).

Foreach C1 in t2:if t1.c1! = c1:continueelse:return falsereturn true

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

Thank you for reading this article carefully. I hope the article "how to solve the problem of filling holes in NOT IN in MySQL as null" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.