Skip to main content

MySQL不在

MySQL非IN操作符简介

NOT运算符否定IN运算符:

value NOT IN (value1, value2, value2)

如果值不等于列表中的任何值,NOT IN运算符将返回一。否则,它返回0。

以下示例使用NOT IN运算符检查数字1是否不在列表(1,2,3)中:

SELECT 1 NOT IN (1,2,3);

输出:

+------------------+
| 1 NOT IN (1,2,3) |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

它返回0(false),因为1不在列表中。

以下示例使用NOT IN运算符检查0是否不在列表(1,2,3)中:

SELECT 0 NOT IN (1,2,3);

输出:

+------------------+
| 0 NOT IN (1,2,3) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

如果IN运算符左侧的值为NULL,NOT IN运算符将返回NULL。例如:

SELECT NULL NOT IN (1,2,3);

输出:

+---------------------+
| NULL NOT IN (1,2,3) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)

从技术上讲,不在运营商等同于以下内容:

NOT (value = value1 OR value = value2 OR value = valu3)

或者:

value <> value1 AND value <> value2 AND value <> value3

MySQL不在操作符示例中

我们将使用示例数据库中的Office表来说明NOT IN运算符:

MySQL NOT IN Operator Example

以下示例使用NOT IN操作符查找不在法国和美国的办公室:

SELECT 
officeCode,
city,
phone
FROM
offices
WHERE
country NOT IN ('USA' , 'France')
ORDER BY
city;

输出:

+------------+--------+------------------+
| officeCode | city | phone |
+------------+--------+------------------+
| 7 | London | +44 20 7877 2041 |
| 6 | Sydney | +61 2 9264 2451 |
| 5 | Tokyo | +81 33 224 5000 |
+------------+--------+------------------+
3 rows in set (0.02 sec)

总结