sql中的null值问题

目录

一、null值介绍

null并不代表空字符串，null在sql中表示缺失或未知的数据。以下是在实际工作中经常遇到的问题。

二、测试数据

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,
    hire_date DATE,
    bonus DECIMAL(10,2)
);

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(50),
    budget DECIMAL(15,2),
    established_date DATE
);

-- 插入部门数据
INSERT INTO departments VALUES
(1, '销售部', '北京', 1000000.00, '2010-05-15'),
(2, '技术部', '上海', 1500000.00, '2012-03-20'),
(3, '人事部', NULL, 800000.00, '2015-11-10'),
(4, '财务部', '深圳', NULL, '2018-07-01'),
(5, '市场部', '广州', 1200000.00, NULL);

-- 插入员工数据
INSERT INTO employees VALUES
(101, '张三', '销售部', 8500.00, NULL, '2018-06-15', 2000.00),
(102, '李四', '技术部', 12000.00, 201, '2016-03-10', NULL),
(103, '王五', '销售部', NULL, 101, '2019-11-20', 1500.00),
(104, '赵六', '人事部', 7800.00, 202, '2020-02-05', NULL),
(105, '钱七', '技术部', 11000.00, 201, '2017-09-12', 3000.00),
(106, '孙八', NULL, 9500.00, 101, '2021-04-18', NULL),
(107, '周九', '财务部', NULL, 203, '2019-07-22', 2500.00),
(108, '吴十', '市场部', 10500.00, NULL, '2022-01-30', NULL),
(201, '郑经理', '技术部', 18000.00, NULL, '2015-08-10', 5000.00),
(202, '王经理', '人事部', 16000.00, NULL, '2016-05-15', NULL),
(203, '李经理', '财务部', 17000.00, NULL, '2017-12-20', 4000.00);

三、null值常见问题

3.1 比较运算中的null

在sql中，null不与任何值相等，包括自身，即null值与任何其他值 (即使是null) 相比永远不为真。
如要判断某列是否含null，使用is null 或 is not null

select 0 = null  -- null
select 'a' <> null -- null
select null = null  -- null
select '' = null  -- null

select 0 is null  -- 0
select null is null  -- 1
select '' is null -- 0
select 1 is not null -- 1

3.2 算数运算中的null

同比较运算符，null与任何值计算都为null

select 3+null  -- null
select null+null  -- null

3.3 逻辑运算中的null

select null or true -- 1
select null or false -- null
select null and true -- null
select null and false -- 0
select not null -- null

结果可能和预期不一样，具体原因：
在sql中，null与布尔值（true/false）进行逻辑运算时遵循三值逻辑(true/false/unknown)，这与传统的二值逻辑不同。
具体分析：
select null or true -- 1
or运算，有真则真。无论null代表什么未知值，结果都为真

select null or false -- null
and运算，有假则假。null可能为true，可能为false，如果为true，则返回1，如果为false则返回0，由于不确定，sql返回null（unknown）

select null and true -- null
同上，null可能为true，可能为false，如果为true，则返回1，如果为false则返回0，由于不确定，sql返回null（unknown）

select null and false -- 0
无论null为什么值，都返回false

select not null -- null
同上，null可能为true，可能为false，如果为true，则返回0，如果为false则返回1，由于不确定，sql返回null（unknown）

3.4 聚合函数中的null

聚合函数（如sum()、avg()、count()）忽略null值，但count()除外，count(coln)计算单个列中的值，而count()计算行。

select avg(salary) 直接使用avg的平均工资
			,sum(salary)/count(salary) `使用sum/count得到的平均工资`
			,sum(salary) 工资总和
			,count(emp_name) 员工数
			,sum(salary)/count(emp_name) `工资总和/员工数得到的平均工资`
			,count(*) 总行数
			,count(manager_id) 经理id数
from employees;

结果：

从以上sql中可以看出，直接使用avg的平均工资 ≠ 工资总和/员工数得到的平均工资，但 直接使用avg的平均工资 = 使用sum/count得到的平均工资。
因为聚合函数忽略null值，即员工总共有11个，但是对应只有9个员工有工资，即工资数为9，剩下3个均为null，因此使用avg() 为总工资/9而不是总工资/11。从而也说明了总行数为何≠经理id数

3.5 group by 和 order by 中的null

group by ：null值会被视为相同的值分组在一起。
order by ：默认情况下，null值在升序（asc）排序中排在最后，降序（desc）排序中排在最前。

3.6 join 操作中的null

在join操作中，null不与任何值匹配，包括null

select e.emp_name, d.location
from employees e
left join departments d on e.department = d.dept_name;

结果：

3.7 in 和 not in 中的null

in :

select * from employees where department in ('技术部','市场部',null);

结果：

可见in中有null不会导致整个查询返回null
因为in相当于是or，即select * from employees where department='技术部' or department='市场部' or department=null;

not in :

select * from employees where department not in ('技术部','市场部',null);

结果：

可见即使表中含有除'技术部','市场部'以外的数据，仍不返回任何行
因为not in 等价于一系列 != 的and组合，即select * from employees where department != '技术部' and department != '市场部' and department != null,在前面【逻辑运算中的null】部分提到过，and有假则假，null不确定为何值，sql返回null(unknown)

四、处理null的函数

coalesce(value1,value2,...,valuen):返回第一个非null值 -> 增强版的ifnull()
ifnull(value1,value2):如果value1为null，则返回value2 -> ISNULL (SQL Server) / IFNULL (MySQL) / NVL (Oracle)
nullif(value1,value2):如果两个值相等则返回null,不相等则返回第一个

五、null在不同数据库中的显现

虽然逻辑相同，但不同数据库显示方式可能不同

MySQL: TRUE=1, FALSE=0, NULL=NULL
PostgreSQL: 显示为 t/f/nil
SQL Server: 显示为 TRUE/FALSE/NULL

以上可参考官方文档：
Problems with NULL Values
Working with NULL Values

posted @ 2025-05-16 00:12  夏悠然h  阅读(57)  评论(0)    收藏  举报