Solve the problem of MySQL using not in to include null values

Notice! ! !

select * from user where uid not in (a,b,c,null);

This sql will not return any results. Avoid null in the not in list.

in addition:

– If null participates in an arithmetic operation, the value of the arithmetic expression is null. (For example: +, -, *, / addition, subtraction, multiplication and division)

– If null is involved in the comparison operation, the result can be considered false. (For example: >=,<=,<> greater than, less than, not equal to)

–If null is involved in an aggregation operation, the aggregation function will be set to null (this situation can be avoided by using methods such as isnull(field,0)). Except count(*), count(1), count(0), etc. (rows where count(field) is null are not counted).

--If there is a null value in the not in subquery, no data will be returned.

Supplement: MySQL in, not in, exists, not exists and null

Null is a strange thing in data and is also special in comparison. The following records and summarizes the impact of null on the judgment results in in, not in, exists, and not exists.

Let's make some descriptive statements. The one on the left side of the comparison operator is called the left comparison operator, and the one on the right side of the comparison operator is called the right comparison operator. For example, 1 in (1,2), then the 1 on the left side of in is the left comparison operator, and the (1,2) on the right side of in is the right comparison operator.

1.in

1.1 When the left comparison operator is null, null is returned in any case.

mysql> select null in (1,2);
+---------------+
| null in (1,2) |
+---------------+
| NULL |
+---------------+
1 row in set (0.00 sec)

mysql> select null in (1,2,null);
+--------------------+
| null in (1,2,null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

1.2 When the right comparison symbol contains null, it returns 1 only when the left comparison symbol is not null and the right comparison symbol contains the left comparison symbol. In other cases, it returns null.

mysql> select null in (1,2,null);
+--------------------+
| null in (1,2,null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select 3 in (1,2,null);
+-----------------+
| 3 in (1,2,null) |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.00 sec)

mysql> select 1 in (1,2,null);
+-----------------+
| 1 in (1,2,null) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)

2. not in

2.1 When the left comparison operator is null, null is returned in any case.

mysql> select null not in (1,2,null);
+------------------------+
| null not in (1,2,null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)

mysql> select null not in (1,2);
+-------------------+
| null not in (1,2) |
+-------------------+
| NULL |
+-------------------+
1 row in set (0.00 sec)

2.2 When the right comparison symbol contains null, when the right comparison symbol contains the left comparison symbol, it returns 0, and null is returned in all other cases.

mysql> select 1 not in (1,2,null);
+---------------------+
| 1 not in (1,2,null) |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)

mysql> select 1 not in (2,3,null); 
+---------------------+
| 1 not in (2,3,null) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)

3.exists

Exists is evaluated to true when the subquery returns null.

mysql> select exists (select null);
+----------------------+
| exists (select null) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)

4. not exists

The not exists subquery is considered false if it returns null.

mysql> select not exists (select null);
+--------------------------+
| not exists (select null) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)

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.