Solve the problem of not finding NULL from set operation to mysql not like

An interesting discovery:

There is a table with a total of 1000 records. Now there is a query statement:

#Query statement 1
#Find the fields in the table whose id contains 'A' or 'B' or 'C' select * from table1 where id like '%A%' or id like '%B%' or id like '%C%';
#300 results were found successfully

Well, the query is normal, there are 300 records.

Then I typed the query again randomly...:

#Query statement 2
#Find the fields in the table whose id does not contain 'A', 'B', or 'C' select * from table1 where id not like '%A%' and id not like '%B%' and id not like '%C%';
#400 results were found successfully

Well, we found 400 items...what? ! Only 400? ? Shouldn’t there be 700? ! !

I was struck by lightning - what was wrong? ?

Logically, "find the fields in the table whose id contains 'A' or 'B' or 'C'" should be equivalent to "find the fields in the table whose id does not contain 'A', 'B', or 'C'"! This is determined by set operations!

"Find the fields in the table that have 'A' or 'B' or 'C' in their id" >> think of it as >> A∪B∪C

"Find the fields in the table whose id does not contain 'A', 'B', or 'C'" >> think of it as >> ∁UA∩∁UB∩∁UC

However, according to the operation rules, we know that: ∁U(A∪B∪C) = ∁UA ∩ ∁UB ∩ ∁UC

That is to say, shouldn't the results of the two query statements be complementary? ? Theoretically, the sum of the rows should be equal to 1000, so why are 300 missing now?

That's strange. What went wrong? ?

So change the query statement:

#Query statement 3
#Find the fields in the table whose id does not contain 'A' or 'B' or 'C' select * from table1 where id not in (select * from table1 where id like '%A%' or id like '%B%' or id like '%C%' );
#700 results were found successfully

Huh? ? How can we find the complementary set of query statement 1 in this way?

I compared the results of query statement 2 and query statement 3 and found that the 300 missing records were those where id was NULL!

In other words, not like '%A%' cannot find NULL records!

After understanding, we found that:

Found another detail knowledge~

Supplement: Note that when there is a null value in the not in query value of MySQL, the query result returned will always be empty

Now the database table is

When we use the following query:

select * from user
where username not in 
(
select username 
from user
where id != 2
)

At this time, the result of the subquery contains null values, so the result will always return an empty query instead of the expected row with id 2.

We can modify the query slightly:

select * from user
where username not in 
(
select username 
from user
where id != 2 and username is not null
)

At this time, by excluding the empty username column, you can get the expected result.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.