Mysql学习笔记（3）——IN 和 NOT IN 的坑

SELECT *
FROM `house-prices`
WHERE Bedrooms = 2
OR Bedrooms = 3
OR Bedrooms = 4;

写法比较冗余，可以使用IN

SELECT *
FROM `house-prices`
WHERE Bedrooms IN (2,3,4); 

SELECT *
FROM `house-prices`
WHERE Bedrooms NOT IN (2,3,4); 

那NOT IN 的等价实现应该为

SELECT *
FROM `house-prices`
WHERE Bedrooms <> 2
AND Bedrooms <> 3
AND Bedrooms <> 4;

SELECT *
FROM `house-prices`
WHERE Neighborhood IN ('East','West','',NULL)
ORDER BY Neighborhood;

利用ORDER BY 排序，方便发现是否能查找出NULL，因为NULL会排在第一位，但结果发现并没有NULL。

SELECT *
FROM `house-prices`
WHERE Neighborhood NOT IN ('East','West');

同样，NOT IN 也不能筛选出NULL,可以筛选出空字符串。

SELECT *
FROM `house-prices`
WHERE Neighborhood NOT IN ('East','West',NULL);

这样写会整体数据查询失败，是一个很大的坑！