mysql三值运算

# 基础知识

# 三值逻辑

题目虽简单，但是背后包含的知识点真的也不少，你都掌握了吗？

NULL有两种，“未知”unknown和“inapplicable”不适用。不知道戴眼镜的人眼睛是什么颜色，就是未知。只要不摘掉眼睛，就不知道，但是这个人眼睛肯定有颜色的。不知道冰箱的眼睛是什么颜色。这就是不适用，这个属性不适用于冰箱。冰箱是没有眼睛的。现在DBMS都将这两种类型NULL归为了一类，并采用三值逻辑。

# 为什么是IS NULL, 而不是"= NULL"?

很奇怪，是不是？小学的时候学的=就是表示相等关系。但是，对NULL使用谓词得到的结果是unknown。

Tip: SQL的保留字中，很多都被归类为谓词一类，例如>,<>,= 等比较谓词，以及BETWEEN, LIKE, IN, IS NULL等。总结，谓词是一种特殊的函数，返回值是真值。(前面提到的诶个谓词，返回值都是true, false, unknown,SQL是三值逻辑，所以有三种真值）

因为查询结果只会包含WHERE子句里的判断结果为true的行！不包含判断结果为false和unknown的行。且不仅是等号，对NULL使用其他比较谓词（比如> NULL），结果也都是unknown。

重点理解： NULL不是值，所以不能对其使用谓词，如果使用谓词，结果是unknown。可以认为它只是一个没有值的标记,而比较谓词只适用于比较值。因此对非值的NULL使用比较谓词是没有意义的

# 如何理解IS NULL?是两个单词？IS空格NULL?

"NULL值" 和 "列的值为NULL"这个说法是错误的。NULL不属于关系型数据库中的某种类型。我们为什么会误认为NULL是一个值？可能因为混淆了别的语言，在一些语言中NULL是一个常量。还有个重要原因是IS NULL是两个单词，所以我以前也把IS当作谓词，比如IS-A,所以误认为NULL是一个值。特别是SQL里有IS TRUE, IS FALSE。在讲解SQL标准的书里提醒人那么样，我们应该把IS NULL看作一个谓词，如果可以IS_NULL或许更合适。

# 三值逻辑运算。

unknown小写，是第三个真值。与作为NULL的一种UNKNOWN(未知)是不同的东西。小写是明确的布尔类型的真值，后者大写的既不是值也不是变量。为了对比不同：看x=x的情况。

unknown = unknown -> true
UNKNOWN = UNKNOWN ->unknown

1
2

重点：【三值逻辑运算】

NOT unknown => unknown

true          OR unknown => true
unknown OR unknown => unknown
false         OR unknown => unknown

true          AND unknown => unknown
unknown AND unknown => unknown
false         AND unknown => false

记忆：优先级：
AND:    false > unknown > true
OR:       true > unknown > false

1
2
3
4
5
6
7
8
9
10
11
12
13

# 实战

# 题解

法一：使用NOT IN, 注意不能先查出name，否则可能name有重名，id不同，导致数据查询不全。我一开始就犯了这个错误。应该查询出id，去避免这个问题。同样，用UNION也会自动去重，要用的话用UNION ALL. 注意code格式规范：所有关键字大写，关键字右边对齐，子句缩进。即使很短的代码，也要严格要求自己，好的代码不仅自己能懂，也要让一个新人能很容易读懂。切不可随意潦草，我自己要慢慢修炼！

SELECT  name
 FROM  customer C
WHERE C.id NOT IN (  SELECT C1.id 
                                        FROM customer C1
                                      WHERE C1.referee_id = 2  );

1
2
3
4
5

法二：使用exists，先查出referee_id的数据，再用exists,注意语法，exists需要关联表。

SELECT C.name FROM customer C 
WHERE NOT EXISTS (SELECT C1.name FROM  customer C1 
                    WHERE  C1.id  = C.id AND  C1. referee_id = 2);

1
2
3

法三：直接增加第二个判断，用OR。

SELECT C.name 
  FROM customer C
WHERE C. referee_id <> 2
       OR C. referee_id IS NULL;
法四: 用UNION ALL


SELECT name 
 FROM customer 
WHERE referee_id <> 2
UNION ALL
SELECT name 
 FROM customer 
WHERE referee_id IS NULL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

为什么有时NOT IN 会查不到值，以及推导公式。如果用WHERE id NOT IN (1, 2, NULL), 是不能查出数据的。推导【经典】：

=> WHERE NOT id IN (1, 2, NULL) //NOT和IN等价改写NOT IN 
=> WHERE NOT（（id = 1）OR（id = 2）OR（id= NULL））//用OR等价改写谓词IN
=> WHERE NOT (id = 1) AND NOT (id = 2) AND NOT( id = NULL) //德摩根定律等价改写
=> WHERE (id <> 1) AND (id <> 2) AND (id <> NULL)//用<>改写NOT 和 =
=> WHERE (id <> 1) AND (id <> 2) AND unknown //对NULL使用<>,结果为unknown
=> WHERE false 或者 unknown //AND运算包含unknown,结果不可能为true

1
2
3
4
5
6

结论：如果NOT IN的子查询用到的表里被选择的列中存在NULL，则SQL整体的查询结果永远是空！

为了解决烦人的NULL，最好在表里添加NOT NULL约束来尽力排除NULL

上次更新: 2022/12/09, 22:58:08

← mysql 查询 mysql 查→