1-3 三值逻辑和null-SQL进阶教程

二值逻辑
布尔型只有true和false两个值

三值逻辑
布尔型除true和false之外还有第三个值unknown

null既不是值也不是变量，null只是一个表示“没有值”的标记
因为null不是值，所以不能对其使用谓词
对null使用谓词后的结果是unknown

1. 比较谓词和null(1)：排中律不成立

排中律
把命题和它的否命题通过‘or’连接而成的命题全都是真命题
由于存在null值，在SQL的世界里，排中律是不成立的

-- 添加第3个条件：年龄是20岁，或者不是20岁，或者年龄未知
select *
from Students
where age=20
or age<>20
or age is null;

2. 比较谓词和null(2) ：CASE表达式和null

-- 错误写法
case col_1
when 1 then '○'
when null then '×' -- 这里的when子句是col_1=null的缩写形式
end;

-- 正确写法
case 
when col_1=1 then '○'
when col_1 is null then '×'
end;

3. not in和not exists不是等价的

-- 查询与B班住在东京的学生年龄不同的A班学生
select *
from Class_A
where age not in(
	select age
	from Class_B
	where city = '东京'
);
/*
如果not in子查询中用到的表里被选择的列中存在null，则SQL语句整体的查询结果永远是空
*/

-- 正确的SQL语句：拉里和伯杰将被查询到
select *
from Class_A A
where not exists(
	select *
	from Class_B B
	where A.age=B.age
	and B.city='东京'
);
/*
exists谓词永远不会返回unknown。exists只会返回true或者false
因此in和exists可以互相替换使用，而not in和not exists却不可以互相替换
*/

4. 限定谓词和null

-- 查询比B班住在东京的所有学生年龄都小的A班学生
select *
from Class_A
where age<all(
	select age
	from Class_B
	where city='东京'
);
/*
如果山田年龄不详，这条SQL语句的执行结果将为空
因为all谓词其实是多个以and连接的逻辑表达式的省略写法
*/

5. 限定谓词和极值函数不是等价的

select *
from Class_A
where age<(
	select min(age)
	from Class_B
	where city='东京'
);
/*
即使山田的年龄无法确定，这段代码也能查询到拉里和伯杰两人
因为，极值函数在统计时会把为null的数据排除掉
*/

限定谓词≠极值函数:
①表里存在null时
②谓词（或者函数）的输入为空集

比较对象原本就不存在时，根据业务需求有时需要返回所有行，有时需要返回空集
需要返回所有行时，需要使用all谓词，或者使用coalease函数将极值函数返回的null处理成合适的值

6. 聚合函数和null

当输入为空表时返回null的不只是极值函数，count以外的聚合函数也是如此

-- 查询比住在东京的学生的平均年龄还要小的A班学生
select * 
from Class_A
where age<(
	select avg(age)
	from Class_B
	where city='东京'
);
/*
没有住在东京的学生时，avg函数返回null
这种情况的解决方法只有两种：要么把null改写成具体值，要么闭上眼睛接受null
但是如果某列有not null约束，而我们需要往其中插入平均值或汇总值，那么就只能选择将 null改写成具体值了

聚合函数和极值函数的这个陷阱是由函数自身带来的，所以仅靠为具体列加上not null约束是无法从根本上消除的
*/