什么是NULLNULL用来表示缺失值或者缺失未知数据，不是特定类型的值。数据表中的NULL值表示该值所在字段为空，值为NULL的字段没有值。特别是，应该理解NULL值不同于0或空字符串。你可能会觉得NULL有两种很奇怪，因为SQL中只有一种NULL。但是，在讨论NULL的时候，我们一般会把它分为两种思路：“未知”（unknown）和“不适用”（notapplicable，inapplicable）。以“我不知道戴墨镜的人的眼睛是什么颜色”的情况为例。这个人的眼睛肯定是有颜色的，但是如果他不摘下眼镜，别人就不知道他的眼睛是什么颜色的。这叫做未知。而“不知道冰箱的眼睛是什么颜色的”“不适用”。因为冰箱根本没有眼睛，所以属性“眼睛颜色”不适用于冰箱。诸如“冰箱眼睛的颜色”之类的陈述与“圆的体积”和“男性的出生数量”一样毫无意义。通常，我们习惯说“不知道”，但“不知道”有很多种。NULL在“不适用”的情况下在语义上更接近于“无意义”而不是“不确定”。这里总结一下：“未知”的意思是“虽然我们现在不知道，但是加上一定的条件后我们可以知道”；而“不适用”就是“无论我们如何努力，我们都无法知道”。关系模型的发明者E.F.Codd是第一个给出这种分类的人。下图是他对“丢失的信息”的分类：为什么一定要写成“ISNULL”而不是“=NULL”。相信很多人都有这样的困惑，尤其是刚学SQL的人。我们来看一个具体的案例，假设我们有如下表格和数据。DROPTABLEIFEXISTSt_sample_null;CREATETABLEt_sample_null(idINT(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',nameVARCHAR(50)NOTNULLCOMMENT'name',remarkVARCHAR(500)COMMENT'remark',主键(id))COMMENT'NULLsample';INSERTINTOt_sample_null(name,remark)VALUES('张三','张三'),('李四',NULL);我们要查询备注为NULL的记录（对于NULL这个名字本身就是错误的，但是我们在日常生活中已经习惯了，详见下文），如何查询，很多新手都会写这样的SQL。--SQL不报错，但找不到结果SELECT*FROMt_sample_nullWHEREremark=NULL;执行的时候没有报错，但是找不到我们想要的结果，这是为什么呢？让我们暂时离开这个问题。下面来看三元逻辑。这个三元逻辑不是三元运算，而是指三个逻辑值。有些人可能会有疑问。逻辑值不只有真（true）和假（false）。那么，第三个在哪里？说这话的时候，需要注意我们生活的环境。在主流编程语言（C、JAVA、Python、JS等）中，确实只有两个逻辑值，但在SQL中却有第三个逻辑值：unknown。这有点类似于我们平时说的：对，错，不知道。逻辑值unknown和UNKNOWN（未知），也就是NULL的一种，是不同的东西。前者是明确的布尔逻辑值，后者既不是值也不是变量。为便于区分，前者用小写字母unknown表示，后者用大写字母UNKNOWN表示。为了让大家明白其中的区别，让我们来看一个简单的方程式，比如x=x。当x是逻辑值unknown时，x=x的计算结果为true，而当x为UNKNOWN时，x=x的计算结果为unknown。--这是一个明确的逻辑值对比unknown=unknown→true--这相当于NULL=NULLUNKNOWN=UNKNOWN→三值逻辑的未知逻辑值表：NOTANDOR图中蓝色部分为唯一运算在三值逻辑中，这在二进制逻辑中不可用。所有其他SQL谓词都可以从这三个逻辑操作中组合起来。从这个意义上说，这些逻辑表可以说是SQL的矩阵。对于NOT，由于逻辑值表比较简单，所以很容易记住；但是对于AND和OR，因为有很多逻辑值组合在一起，想要全部记住是非常困难的。为了便于记忆，请注意这三个逻辑值之间的优先级顺序如下。AND的情况下：false>unknown>trueOR的情况下：true>unknown>false优先级高的逻辑值决定计算结果。例如trueANDunknown，因为unknown的优先级更高，所以结果是unknown。在trueORunknown的情况下，结果为true，因为true具有更高的优先级。记住这个顺序后，进行三值逻辑运算就比较方便了。特别要记住，当unknown包含在AND运算中时，结果永远不会为真（相反，如果AND运算的结果为true，则参与运算的双方都必须为true）。--假设a=2,b=5,c=NULL，以下表达式的逻辑值如下：ac→unknown>bORbc)→unknown"ISNULL"而不是"=NULL"回到问题：为什么一定要写成"ISNULL"而不是"=NULL"使用后得到的结果NULL上的比较谓词始终是未知的。查询结果只会包含WHERE子句中判断结果为真的行，不会包含判断结果为假或未知的行。不仅是等号，NULL上的其他比较谓词也有相同的结果。所以无论remark是否为NULL，比较结果都是未知的，永远不会返回任何结果。以下公式将被判断为未知。--下面的公式会被判断为unknown=NULL>NULLNULLNULL=NULL那么，为什么在NULL上使用比较谓词得到的结果永远不会为真呢？这是因为NULL既不是值也不是变量。NULL只是一个表示“无值”的标志，比较谓词只对值起作用。因此，在不是值的NULL上使用比较谓词是没有意义的。“该列的值为NULL”、“NULL值”等说法本身就是错误的。因为NULL不是值，所以它不在域中。相反，如果有人认为NULL是一个值，那么我们可以反过来想：它是什么类型的值？存在于关系数据库中的值必须属于某种类型，比如字符型或数字型。所以，如果NULL是一个值，它一定是某种类型。NULL很容易被认为是一个值，原因有两个。第一个是在高级编程语言中，NULL被定义为一个常量（很多语言都将其定义为整数0），这导致了我们的混淆。但是，SQL中的NULL与其他编程语言中的NULL是完全不同的东西。第二个原因是像ISNULL这样的谓词是由两个词组成的，所以很容易把IS当作谓词，把NULL当作一个值。特别是SQL中有ISTRUE、ISFALSE等谓词，我们可以由此推导出来，所以这样想也不无道理。但是正如关于标准SQL的书籍提醒人们的那样，我们应该将ISNULL视为谓词。因此，写成IS_NULL可能更合适。温和陷阱比较谓词和排中NULL定律不成立。排中律是指在同一个思维过程中，两个相互矛盾的想法不能为假，而必须有一个为真，即“非A即非A”。假设我们有学生表：t_student。DROPTABLEIFEXISTSt_student;CREATETABLEt_student(idINT(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',nameVARCHAR(50)NOTNULLCOMMENT'name',ageINT(3)COMMENT'age',remarkVARCHAR(500)NOTNULLDEFAULT''COMMENT'备注',primarykey(id))COMMENT'学生信息';INSERTINTOt_student(name,age)VALUE('zhangsan',25),('wangwu',60),('bruce',32),('yzb',NULL),('boss',18);SELECT*FROMt_student;表中数据yzb的age为NULL，表示yzb的age未知。在现实世界中，yzb是20岁，还是不是20岁，两者必为其中之一，这无疑是一个真命题。那么在SQL的世界里，排中律还适用吗？我们来看一个SQL。选择*FROMt_studentWHERE年龄=20或年龄<>20；乍一看，这不就是查询表中的所有记录吗？我们来看看实际结果：yzb没有检测到，这是为什么呢？我们分析一下，yzb的年龄为NULL，那么这条记录的判断步骤如下：--1.John的年龄为NULL(unknownNULL!)SELECT*FROMt_studentWHEREage=NULLORage<>NULL;--2.在NULL上使用比较谓词后，结果为unknownSELECT*FROMt_studentWHEREunknownORunknown;--3．unknownORunknown结果未知（参考三值逻辑逻辑值表）SELECT*FROMt_studentWHEREunknown;SQL语句的查询结果只有判断结果为真的行。要让yzb出现在结果中，需要加上下面的“第三个条件”。--添加3个条件：年龄为20岁，或未满20岁，或年龄未知SELECT*FROMt_studentWHEREage=20ORage<>20ORageISNULL;CASE表达式和NULL简单的CASE表达式如下：CASEcol_1WHEN=1THEN'o'WHENNULLTHEN'x'END此CASE表达式绝不能返回x。这是因为第二个WHEN子句是col_1=NULL的简写。我们知道，这个表达式的逻辑值永远是未知的，CASE表达式的判断方法和WHERE子句一样，只识别逻辑值为真的条件。正确的写法是像下面这样使用搜索CASE表达式。CASEWHENcol_1=1THEN'o'WHENcol_1ISNULLTHEN'x'ENDNOTIN和NOTEXISTS不等价我们在优化SQL语句性能的时候，经常使用的一个技巧就是把IN重写为EXISTS，也就是等价改写，没有问题。但是，将NOTIN改写为NOTEXISTS时，结果可能不一样。让我们看一个例子。我们有如下两张表：t_student_A和t_student_B，分别代表A班和B班的学生。DROPTABLEIFEXISTSt_student_A;CREATETABLEt_student_A(idINT(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',nameVARCHAR(50)NOTNULLCOMMENT'name',ageINT(3)COMMENT'age',cityVARCHAR(50)NOTNULLCOMMENT'city',remarkVARCHAR(500)NOTNULLDEFAULT''COMMENT'remark',primarykey(id))COMMENT'学生信息';INSERTINTOt_student_A(name,age,city)VALUE('zhangsan',25,'深圳'),('wangwu',60,'广州'),('bruce',32,'北京'),('yzb',NULL,'深圳'),('老板',43,'深圳');DROPTABLEIFEXISTSt_student_B;CREATETABLEt_student_B(idINT(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',nameVARCHAR(50)NOTNULLCOMMENT'name',ageINT(3)COMMENT'age',cityVARCHAR(50)NOTNULLCOMMENT'city',remarkVARCHAR(500)NOTNULLDEFAULT''COMMENT'remark',主键(id))评论“学生信息”；INSERTINTOt_student_B(name,age,city)VALUE('马化腾',45,'深圳'),('马三',25,'深圳'),('马云',43,'杭州'),('李彦宏',41,'深圳'),('年轻人',25,'深圳');*从t_st学生B；需求：查询B班的同学和A班的同学在深圳的不一样的，也就是说查询：马化腾和李彦宏，这个SQL怎么写，像这样？--查询B班的学生和A班住在深圳的学生不一样？SELECT*FROMt_student_BWHEREageNOTIN(SELECTageFROMt_student_AWHEREcity='深圳');我们看一下执行结果：我们发现结果为Empty，查询不到任何数据，为什么？这里NULL又开始闹事了，我们一步步来看是怎么回事--1.执行子查询获取年龄列表SELECT*FROMt_studentWHEREageNOTIN(43,NULL,25);--2.重写NOTINSELECT*FROMt_studentWHERENOTageIN(43,NULL,NULL,25);--3.等价地改写谓词INSELECT*FROMt_studentWHERENOT((age=43)OR(age=NULL)OR(age=25))为OR等价物；--4.使用DeMorgan定律等价地改写SELECT*FROMt_studentWHERENOT(age=43)ANDNOT(age=NULL)ANDNOT(age=25);--5.等价改写NOTand=SELECT*FROMt_studentWHERE(age<>43)AND(age<>NULL)AND(age<>25);--6.对NULL使用<>后，结果为unknownSELECT*FROMt_studentWHERE(age<>43)ANDunknownAND(age<>25);--7.如果AND中包含unknown运算，结果不为真（参考三值逻辑的逻辑值表）SELECT*FROMt_studentWHEREfalseorunknown；可以看出，经过一系列的转换，WHERE子句中没有记录被判断为真。也就是说，如果在NOTIN子查询中使用的表中selected列中存在NULL，那么SQL语句的整体查询结果将永远为空。这是一个可怕的现象！为了得到正确的结果，我们需要使用EXISTS谓词。--正确的SQL语句：马化腾和李彦宏会被查询到SELECT*FROMt_student_BBWHERENOTEXISTS(SELECT*FROMt_student_AAWHEREB.age=A.ageANDA.city='Shenzhen');执行结果如下：同理，我们一步步看一下这条SQL是如何处理age为NULL的行的。--1、在子查询中与NULL进行比较操作。此时A.age为NULLSELECT*FROMt_student_BBWHERENOTEXISTS(SELECT*FROMt_student_AAWHEREB.age=NULLANDA.city='Shenzhen');--2.NULL用"="后结果是unknownSELECT*FROMt_student_BBWHERENOTEXISTS(SELECT*FROMt_student_AAWHEREunknownANDA.city='Shenzhen');--3.如果unknown包含在AND运算，结果不会为trueSELECT*FROMt_student_BBWHERENOTEXISTS(SELECT*FROMt_student_AAWHEREfalseorunknown);--4.子查询不返回结果，所以相反，NOTEXISTS为trueSELECT*FROMt_student_BB哪里是真的；也就是说，yzb被当成了“和谁都年龄不一样”。EXISTS只会返回true或false，永远不会返回未知。因此混淆了IN和EXISTS可以互换使用，但NOTIN和NOTEXISTS不能。还有一些其他的陷阱，比如：限定谓词和NULL，限定谓词和极值函数不等价，聚合函数和NULL等。总结1.NULL用来表示缺失值或者缺失未知数据。它不是特定类型的值，不能在其上使用谓词。2.在NULL上使用谓词的结果是未知的。当unknown参与逻辑运算时，SQL的运行会和预期的不一样。3.ISNULL是一个整体的谓词，不是：IS是一个谓词，NULL是一个值；同样，有ISTRUE和ISFALSE4。解决NULL引起的各种问题，最好的办法应该是在表中加入NOTNULL约束，尽量排除NULL我的项目中有一个硬性规定：所有字段必须为NOTNULL，创建一个表时就加入了这个约束桌子。
