sql-is-not-null

SQL - IS NOT NULL 运算符

理解 NULL：‘未知’ 的概念

在 SQL 中，NULL 是一个特殊的标记，用于表示数据库中不存在数据值。理解 NULL 与零 (0)、空字符串 ('') 或布尔值 FALSE 不同至关重要。NULL 代表缺失、未知或不适用的值。

由于 NULL 意味着“未知”，因此不能使用标准比较运算符（如 = 或 !=）来测试它。some_column = NULL 的结果既不是真也不是假；它也是 NULL（未知）。要专门检查某个值是否存在，我们必须使用 IS NOT NULL 运算符。

SQL IS NOT NULL 运算符

IS NOT NULL 运算符用于 WHERE 子句中，以筛选出特定列包含实际值（即不为 NULL）的行。这对于处理可能包含不完整记录的数据集至关关重要。

语法

select column_names
from table_name
where column_name is not null;

示例设置

我们创建一个包含一些 NULL 值的 customers 表来演示。

create table customers(
    id INT primary key,
    name VARCHAR(50) not null,
    age INT,
    shipping_address VARCHAR(255),
    last_login_date DATE
);

insert into customers values
(1, 'Ramesh', 32, '123 Maple St', '2023-10-15'),
(2, 'Khilan', 25, NULL, '2023-11-01'),
(3, 'Kaushik', NULL, '456 Oak Ave', NULL),
(4, 'Chaitali', 25, '789 Pine Ln', '2023-09-20'),
(5, 'Hardik', 27, NULL, NULL);

SELECT 语句中的 IS NOT NULL

以下查询检索所有具有已知 shipping_address 的客户。

select id, name, shipping_address
from customers
where shipping_address is not null;

输出

id	姓名	送货地址
1	Ramesh	123 Maple St
3	Kaushik	456 Oak Ave
4	Chaitali	789 Pine Ln

将 IS NOT NULL 与聚合函数一起使用

聚合函数（如 COUNT()、SUM() 和 AVG()）对 NULL 值的行为有所不同。COUNT(*) 将计算所有行，但 COUNT(column_name) 将只计算 column_name 不为 NULL 的行。

示例

我们来计算有多少客户有记录的年龄。

-- 这将只计算“age”列中非 NULL 的值。
select count(age) as customers_with_known_age
from customers;

输出

已知年龄的客户数量
4

DELETE 和 UPDATE 语句中的 IS NOT NULL

您还可以使用 IS NOT NULL 来定位要修改或删除的行。

UPDATE 示例

假设我们想给所有有记录年龄的客户 10% 的折扣。我们创建一个 discount 列并更新它。

alter table customers add column discount DECIMAL(3, 2) default 0.00;

update customers
set discount = 0.10
where age is not null;

执行此查询后，ID 为 1、2、4 和 5 的客户的折扣将设置为 0.10。

DELETE 示例

让我们删除所有没有上次登录日期的客户记录，也许是为了清理不活跃的账户。

-- 注意：这是一个永久性操作！
delete from customers
where last_login_date is null;

此查询将从表中删除 ID 为 3 和 5 的客户。

最佳实践：使用 NOT NULL 约束强制数据完整性

虽然 IS NOT NULL 对于查询很有用，但更好的方法是首先阻止在不允许 NULL 值的地方插入 NULL 值。您可以通过在创建表时向列添加 NOT NULL 约束来实现这一点。

这在数据库层面强制了数据完整性，确保了关键数据始终存在。

create table users (
    id INT primary key,
    email VARCHAR(255) not null, -- 数据库将拒绝任何尝试将 email 设置为 NULL 的插入/更新操作。
    username VARCHAR(50) not null,
    phone_number VARCHAR(20) -- 此列可以为 NULL，因为用户可能没有电话号码。
);

通过主动使用 NOT NULL 约束，可以使数据更整洁，查询更简单，因为您无需持续检查那些不应为空的列中的 NULL 值。