SQL进阶--三值逻辑和NULL

not in 和 not existsweb

-- not in 和 not exists 
drop table if exists class_a;
create table class_a( name1 varchar(20), age int8, city varchar(30) );

insert into class_a values('布朗', 22, '东京');
insert into class_a values('拉里', 19, '琦玉');
insert into class_a values('伯杰', 21, '千叶');
drop table if exists class_b;
create table class_b like class_a;
insert into class_b values('齐藤', 22, '东京');
insert into class_b values('天口', 23, '东京');
insert into class_b values('山田', NULL, '东京');
insert into class_b values('和泉', 18, '千叶');
insert into class_b values('武田', 20, '千叶');
insert into class_b values('石川', 19, '神奈川');

查询与条件中存在NULLsql

-- 查询与B班住在东京的学生年龄不一样的A班的学生
select * from class_a where age not in (select age from class_b where city = '东京'); -- 没有结果

-- 用not exists 能够查出
select * from class_a a where not exists (select * from class_b b where a.age = b.age and city = '东京');

not exists 语句处理NULL值的逻辑svg

-- 1 子查询中
select * from class_a a where not exists (select * from class_b b where a.age = null and city = '东京');
-- 2 
select * from class_a a where not exists (select * from class_b b where unknown and city = '东京');
-- 3
select * from class_a a where not exists (select * from class_b b where False);
-- 4
select * from class_a a where True;
-- 即山田被做为“与任何人的年龄都不一样的人”来处理，EXISTS 只会返回true 或者 False

内容多来自 《SQL进阶教材》，仅作笔记。spa