[Mysql] IN | NOT IN过滤数据

练习案例数据

DROP TABLE IF EXISTS purchase_info; 
CREATE TABLE purchase_info( 
commodity_id VARCHAR(8), 
category VARCHAR(16), 
colour VARCHAR(16),
purchase_quantity INT, 
purchase_date DATE 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
purchase_info 
(commodity_id,category,colour,purchase_quantity,purchase_date) 
VALUE ('c001','clothing','black',150,'2020-04-25') 
,('c002','clothing','white',50,'2020-04-05') 
,('c003','shoes','white',500,'2020-03-23') 
,('c004','shoes','red',200,'2020-04-07') 
,('c005','clothing','blue',120,'2020-04-15');

purchase_info表(商品进货信息表)

---

IN关键字用来指定条件范围，范围中的每个条件都可以进行匹配

语法格式

-- IN
SELECT column_name(s)
FROM table_name
WHERE column_name IN(value1,value2,...);

-- NOT IN
SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN(value1,value2,...);

问题1:查询商品进货信息表中商品颜色为"黑色"、"白色"和"蓝色"的商品记录

SELECT *
FROM purchase_info
WHERE colour = 'black' OR colour = 'white' OR colour = 'blue';

上述代码写法过于繁琐，可以使用IN关键字进行简化

SELECT *
FROM purchase_info
WHERE colour IN('black','white','blue');

结果展示:

IN是WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当 

通过IN关键字将相同字段的多个不同值组成一个集合，统一进行筛选，可以从书写上简化代码

NOT是WHERE子句中用来否定后跟条件的关键字，可与IN关键字搭配成NOT IN进行取反操作

问题2:查询商品颜色不为"黑色"、"白色"和"蓝色"的商品记录

SELECT *
FROM purchase_info
WHERE colour NOT IN('black','white','blue');

结果展示:

问题3:筛选出除红色外的颜色且颜色信息不为空的商品记录

SELECT *
FROM purchase_info
WHERE colour NOT IN('red',NULL);

结果展示:

上述查询结果为空，对此针对结果分析原因:

colour NOT IN('red',NULL)相当于colour<>'red' AND colour<>NULL

因为NULL不是普通的值，不能使用<>进行比较，所以colour<>NULL的结果为FALSE，导致整个colour<>'red' AND colour<>NULL都为FALSE，也就无法返回结果

对上述代码进行如下修改:

SELECT *
FROM purchase_info
WHERE colour <> 'red'
AND colour IS NOT NULL;

结果展示:

注意:NULL只能使用IS和IS NOT判断

用IN操作符的优点

1.在使用长的合法选项清单时，IN操作符的语法更清楚且更直观

2.在使用IN时，计算的次序更容易管理（因为使用的操作符更少）

3.IN操作符一般比OR操作符清单执行更快

4.IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句