1.先上结论

只有is null能查到null值记录。null既不属于是也不属于非（即score = '1'与score != '1'均查不到null记录），同理以下均查不到null值。

序号

条件

1

=、!=、<>

2

>、<、>、>=、<=

3

in、not in

4

like、not like

5

between、not between

6

is not null

2.测试

  • 基于mysql8、oracle

2.1.建表造数据

-- 建表
drop table if exists student;
create table student
(
    id    int default null,
    score int default null
);
-- 造数据5条，其中1条null
insert into student (id, score) values (1, 1);
insert into student (id, score) values (2, 2);
insert into student (id, score) values (3, 3);
insert into student (id, score) values (4, 4);
insert into student (id, score) values (5, null);

2.2.查询

-- 只有 is null 能查到null值记录
select '1',count(*) from student where score = '1' or score != '1'
union all
select '2',count(*) from student where score > '1' or score <= '1'
union all
select '3',count(*) from student where score in ('1') or score not in ('1')
union all
select '4',count(*) from student where score like '1%' or score not like '1%'
union all
select '5',count(*) from student where score between '1' and '1' or score not between '1' and '1'
union all
select '6',count(*) from student where score is not null
union all
select '7',count(*) from student where score is null
  • 查询结果