The difference and reasons between the MySQL query conditions not in and in

Write a SQL first

SELECT DISTINCT from_id
FROM cod
WHERE cod.from_id NOT IN (37, 56, 57)

When I was writing SQL today, I found that the query results were incomplete, missing NULL values, and even excluded null when not in.

When using in, it does not include null

I feel that MySQL is not designed properly.

Because I always thought that in and not in should complement each other, just like this should be the whole search:

SELECT DISTINCT from_id
FROM cod
WHERE cod.from_id NOT IN (37, 56, 57) or cod.from_id IN (37, 56, 57)

The result is as expected, there is a missing null

Later I checked online and found a reasonable explanation:

null is false when compared to any value

For example, if from_id is (37, 56, 57,28,null), not in (37, 56, 57) is true when compared with 28, so 28 appears in the result set.

When null is compared with the condition not in (37, 56, 57), the result is false, so it does not appear in the result set.

Supplement: How to handle NULL values ​​on both sides of MySQL conditional queries IN and NOT IN

topic

Given a table tree, id is the number of the tree node, and p_id is the id of its parent node.

+----+------+

| id | p_id |

+----+------+

| 1 | NULL |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 2 |

+----+------+

Each node in the tree is of one of three types:

Leaf: If this node does not have any child nodes.

Root: If this node is the root of the entire tree, that is, it has no parent node.

Internal node: If the node is neither a leaf node nor a root node.

Write a query statement to output the numbers and types of all nodes, and sort the results by node number. The result of the above example is:

+----+------+

| id | TYPE |

+----+------+

| 1 | Root |

| 2 | INNER|

| 3 | Leaf |

| 4 | Leaf |

| 5 | Leaf |

+----+------+

explain

Node '1' is the root node because its parent is NULL and it has child nodes '2' and '3'.

Node '2' is an internal node because it has a parent node '1' and also has child nodes '4' and '5'.

Nodes '3', '4' and '5' are leaf nodes because they have a parent node and no child nodes.

The tree in this example looks like this:

 1

 / \\

 twenty three

 / \\

 4 5

First create the table

1. Create a table

CREATE TABLE tree(
id INT ,
p_id INT 
)

Here is how I do it:

SELECT id,(
CASE 
 WHEN tree.p_id IS NULL THEN 'Root'
 WHEN tree.id NOT IN ( -- When id is not in the parent node p_id column, it is considered a leaf node, which is logically correct!
 SELECT p_id
 FROM tree
 GROUP BY p_id
 ) THEN 'Leaf'
 ELSE 'Inner'
END
)TYPE
FROM tree

I think that when the id is not in the p_id column of the parent node, it is considered a leaf node. There is no logical problem at all. However, things are not that simple. The query results are as follows: Starting from id=3, I did not find the results I wanted! Isn’t it amazing!

So after another night, I finally solved the problem. I will first give the correct approach:

SELECT id,(
CASE 
 WHEN tree.p_id IS NULL THEN 'Root'
 WHEN tree.id NOT IN (
 SELECT p_id
 FROM tree
 WHERE p_id IS NOT NULL -- Added a SQL statement
 GROUP BY p_id
 ) THEN 'Leaf'
 ELSE 'Inner'
END
)TYPE
FROM tree

Why is this happening?

it is known

The IN operator in MySQL is used to determine whether the value of an expression is in a given list; if so, the return value is 1, otherwise the return value is 0.

The function of NOT IN is exactly the opposite of IN. NOT IN is used to determine whether the value of the expression does not exist in the given list; if not, the return value is 1, otherwise the return value is 0.

This is how we usually use it, and the result is what we want. But we often encounter the following special situations!

(1) There are no NULL values ​​on either side of in or not in

[Example 1] Using IN and NOT IN operators in SQL statements:

mysql> SELECT 2 IN (1,3,5,'thks'),'thks' IN (1,3,5, 'thks');
+---------------------+---------------------------+
| 2 IN (1,3,5,'thks') | 'thks' IN (1,3,5, 'thks') |
+---------------------+---------------------------+
| 0 | 1 |
+---------------------+---------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT 2 NOT IN (1,3,5,'thks'),'thks' NOT IN (1,3,5, 'thks');
+-------------------------+-------------------------------+
| 2 NOT IN (1,3,5,'thks') | 'thks' NOT IN (1,3,5, 'thks') |
+-------------------------+-------------------------------+
| 1 | 0 |
+-------------------------+-------------------------------+
1 row in set, 2 warnings (0.00 sec)

From the results, we can see that the return values ​​of IN and NOT IN are exactly opposite.

But I ignored the NULL value problem

Handling of NULL values

When either side of the IN operator is NULL, the return value is NULL if no match is found; if a match is found, the return value is 1.

(2) NULL values ​​are on both sides of in

Please see the following SQL statement:

mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
+------------------------+-------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |
+------------------------+-------------------------+
| NULL | NULL |
+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');
+------------------------+--------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |
+------------------------+--------------------------+
| NULL | 1 |
+------------------------+--------------------------+
1 row in set (0.00 sec)

(3) NULL on one side of NOT IN

NOT IN is just the opposite. When one of the two sides of the NOT IN operator is a null value NULL, if no match is found, the return value is NULL; if a match is found, the return value is 0.

Please see the following SQL statement:

mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,0,NULL,'thks');
+----------------------------+-----------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,0,NULL,'thks') |
+----------------------------+-----------------------------+
| NULL | NULL |
+----------------------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,10,NULL,'thks');
+----------------------------+------------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,10,NULL,'thks') |
+----------------------------+------------------------------+
| NULL | 0 |
+----------------------------+------------------------------+
1 row in set (0.00 sec)

Based on the result of (3) NULL on one side of NOT IN, we can see the problem

Let's query the following SQL statement first and slowly find the problem

SELECT p_id
FROM tree
GROUP BY p_id

The above query results contain NULL values

So the following SQL statement will not find anything, because NOT IN returns NULL

SELECT id 
FROM tree
WHERE id NOT IN (
 SELECT p_id
 FROM tree
 GROUP BY p_id
 )

So if you want to query the results, you must first deal with the NULL value! OK, the bug is fixed!

There is another way to do this problem:

SELECT id,(
CASE 
 WHEN tree.p_id IS NULL THEN 'Root'
 WHEN tree.id IN (
 SELECT p_id
 FROM tree
 GROUP BY p_id
 ) THEN 'Inner'
 ELSE 'Leaf'
END
)TYPE
FROM tree

Why is it right? Let's leave it for you to think about~

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.