《sql进阶教程》之三值逻辑和NULL

本文是《sql进阶教程》阅读笔记,感兴趣可以阅读该书对应章节,这本适合有一定sql基础的同学阅读。另外作者《sql基础教程》也值得一看。

一、 NOT IN 和 NOT EXISTS 不是等价的

在对 SQL 语句进行性能优化时,经常用到的一个技巧是将 IN 改写成EXISTS 这是等价改写,并没有什么问题。问题在于,将 NOT IN改写成 NOT EXISTS 时,结果未必一样

请看下面这两张班级学生表:
Class_A

name(名字)age(年龄)city(住址)
布朗22东京
拉里19埼玉
伯杰21千叶

Class_B

name(名字)age(年龄)city(住址)
齐藤22东京
田尻23东京
山田东京
和泉18千叶
武田20千叶
石川19神奈川

注:B 班山田的年龄是NULL

要求:与 B 班住在东京的学生年龄不同的 A 班学生” 即:希望查询到的是拉里和伯杰。因为布朗与齐藤年龄相同。

--创建表A
CREATE TABLE Class_A(
	id serial PRIMARY KEY,
	name VARCHAR(10),
	age INT,
	city VARCHAR(50)
);
INSERT INTO Class_A(name,age,city) VALUES
('布朗',22,'东京'),
('拉里',19,'埼玉'),
('伯杰',21,'千叶');

-- 创建B
CREATE TABLE Class_B(
	id serial PRIMARY KEY,
	name VARCHAR(10),
	age INT,
	city VARCHAR(50)
);

INSERT INTO Class_B(name,age,city) VALUES
('齐藤',22,'东京'),
('田尻',23,'东京'),
('山田',null,'东京'),
('和泉',18,'千叶'),
('武田',20,'千叶'),
('石川',19,'神奈川');

查询与B 班住在东京的学生年龄不同的A 班学生的SQL 语句

SELECT * FROM Class_A
WHERE age NOT IN ( 
   SELECT age 
   FROM Class_B
   WHERE city = '东京'
) 

在这里插入图片描述

实际上,如果山田的年龄不是 NULL ;是存在值的,但是为什么查询结果为空呢。关键就在这个NULL上

通过SQL来变换来查询其本质:

--1. 执行子查询,获取年龄列表
SELECT * FROM Class_A
WHERE age NOT IN (22, 23, NULL);
-- 查询结果为空

--2.用NOT 和IN 等价改写NOT IN
SELECT * FROM Class_A
WHERE NOT age IN (22, 23, NULL);
-- 查询结果为空

--3. 用OR 等价改写谓词IN
SELECT *FROM Class_A
WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL)) 
-- 查询结果为空

--4. 使用德· 摩根定律等价改写
SELECT * FROM Class_A
WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);
-- 查询结果为空

--5. 用<> 等价改写 NOT 和 =
SELECT * FROM Class_A
WHERE (age <> 22) AND (age <> 23) AND (age <> NULL);
-- 查询结果为空

--6. 对NULL 使用<> 后,结果为unknown
/*
SELECT * FROM Class_A
WHERE (age <> 22) AND (age <> 23) AND unknown;
*/


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

为了得到正确的结果,我们需要使用 EXISTS 谓词

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 却不可以互相替换的混乱现象


二、限定谓词和 NULL

SQL 里有 ALL 和 ANY 两个限定谓词。因为 ANY 与 IN 是等价的,所以我们不经常使用 ANY。ALL 可以和比较谓词一起使用,用来表达“与所有的××都相等”,或“比所有的××都大”的意思

给 B 班表里为 NULL 的列添上具体的值;然后,使用这张新表来思考一下用于查询“比 B 班住在东京的所有学生年龄都小的 A 班学生”的 SQL 语句。

Class_A

name(名字)age(年龄)city(住址)
布朗22东京
拉里19埼玉
伯杰21千叶

Class_B

name(名字)age(年龄)city(住址)
齐藤22东京
田尻23东京
山田20东京
和泉18千叶
武田20千叶
石川19神奈川

--注意:如果没有将null替换,则依然不能查出任何值。
SELECT * FROM Class_A
WHERE age < ALL ( SELECT age FROM Class_B WHERE city = '东京' );

在这里插入图片描述


三、限定谓词和极值函数不是等价的

使用极值函数代替 ALL 谓词;即使出现NULL也没关系。 除去 count(*)的聚集函数会忽略NULL

-- 即使山田的年龄为NULL。
-- 查询比B 班住在东京的年龄最小的学生还要小的A 班学生
SELECT * FROM Class_A
WHERE age < ( SELECT MIN(age)
FROM Class_B
WHERE city = '东京' );

注意:如果Class_B中没有住在东京的同学,或者查询结果为NULL。 则查询到的结果会是空。这个时候可以使用COALESCE 函数将极值函数返回的 NULL处理成合适的值。

ALL 谓词 和极值函数表达的命题含义:
ALL 谓词:他的年龄比在东京住的所有学生都小 —— Q1
极值函数:他的年龄比在东京住的年龄最小的学生还要小 ——Q2


小结

  • NULL 不是值。 因为 NULL 不是值,所以不能对其使用谓词。
  • 对 NULL 使用谓词后的结果是 unknown 。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值