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

This article mainly explains "what is the hidden trap of not in in mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the hidden trap of not in in mysql".

1. Phenomenon 1.1.Using not int subquery SELECT * FROM `users` WHERE id NOT IN (SELECT uid FROM role_user)

The query results are as follows:

1.2. Is the result right?

Of course not.

1.2.1. Query the uid result SELECT uid FROM role_user of role_user

The query results are as follows:

1.2.2. Query the data of users table SELECT * FROM `users`

1.2.3. Analyze the query results

The data uid of the role_user table has only one 1 and null, so you should be able to query the id=2 data of the users table.

The sql actually executed is:

SELECT * FROM `users` WHERE id NOT IN (1 zero null)

However, the result of the query is still:

If I change the sql:

SELECT * FROM `users` WHERE id NOT IN (1)

So you can see that the data cannot be queried because the result in not in has null.

2. Why did it produce such a result? 2.1.What does null belong to? 2.2.The underlying implementation of not in: SELECT * FROM `users` WHERE id NOT IN (1dint null)

The implementation principle of multiple values of not in is as follows

SELECT * FROM `users` WHERE id! = 1 and id! = null

Do you think the first reaction is consistent with it? The id of the users table is the primary key, so it is not null.

But why is that?

Let's execute a sql.

Select 1! = null

You can see that the result of the query is Null, so the result of the null identicals in the sql above is also null.

Since Null cannot participate in the boolean operation, the default is false, so in the above condition, the idled invalid null after and is always false.

Thank you for your reading, the above is the content of "what is the implicit trap of not in in mysql". After the study of this article, I believe you have a deeper understanding of what the implicit trap of not in in mysql is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.