SQL进阶--三值逻辑和NULL

not in 和 not exists

-- 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, '神奈川');

查询与条件中存在NULL

-- 查询与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值的逻辑

-- 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进阶教材》，仅做笔记。