MySQL IS NULL 和 IS NOT NULL 查询

 基本概念

在 MySQL 中，`NULL` 表示缺失或未知的值，它与空字符串 `''` 和数值 `0` 有本质区别。`IS NULL` 和 `IS NOT NULL` 是专门用于判断 `NULL` 值的关键字。

基本语法

查询字段为 NULL 的记录

SELECT 字段列表 FROM 表名 WHERE 字段名 IS NULL;

查询字段不为 NULL 的记录

SELECT 字段列表 FROM 表名 WHERE 字段名 IS NOT NULL;

重要特点

1. 特殊比较运算符：

    必须使用 `IS NULL` 或 `IS NOT NULL` 来判断 NULL 值

    使用 `=`、`!=` 或 `<>` 比较 NULL 值都会返回 NULL（不会报错但得不到预期结果）

2. 与空字符串的区别：

    `NULL` 表示没有值

    `''` 是长度为0的字符串

3. 与0的区别：

    `NULL` 不是数值0

    数值计算中 `NULL` 参与运算结果通常为 `NULL`

使用示例

1. 查询 NULL 值记录

查询没有登录日期的学生

SELECT name, login_date 
FROM students 
WHERE login_date IS NULL;

2. 查询非 NULL 值记录

查询已填写登录日期的学生

SELECT name, login_date
FROM students
WHERE login_date IS NOT NULL;

3. 与其他条件组合

查询没有登录日期且年龄大于20的学生

SELECT name, age, login_date
FROM students
WHERE login_date IS NULL AND age > 20;

查询有登录日期或是女生的学生

SELECT name, sex, login_date
FROM students
WHERE login_date IS NOT NULL OR sex = 'F';

常见误区

1. 错误使用等号判断NULL：

   错误写法（不会返回结果）

   SELECT name FROM students WHERE login_date = NULL;

    正确写法

SELECT name FROM students WHERE login_date IS NULL;

2. 混淆NULL与空字符串：

查询空字符串（不是NULL）

SELECT name FROM students WHERE address = '';

    查询NULL值

SELECT name FROM students WHERE address IS NULL;

3. 在聚合函数中的处理：

    `COUNT(*)` 计算所有行数

    `COUNT(字段)` 忽略NULL值

实际应用场景

1. 数据完整性检查：

检查必填字段是否有NULL值

SELECT COUNT(*) FROM customers WHERE phone IS NULL;

2. 数据清洗：

找出没有邮箱的用户以便补充数据

SELECT user_id, username 
   FROM users 
   WHERE email IS NULL;

3. 报表统计：

统计已完成订单和未完成订单（未完成订单的完成时间为NULL）

 SELECT 
     COUNT(*) AS total_orders,
     COUNT(completion_time) AS completed_orders,
     COUNT(*) - COUNT(completion_time) AS pending_orders
   FROM orders;

4. 性能优化建议

1. 索引使用：

    对经常需要判断 `IS NULL` 的字段建立索引

    注意：MySQL 中 `IS NULL` 条件可以使用索引

2. 默认值替代NULL：

    考虑使用默认值（如空字符串或0）代替NULL

    可以减少NULL判断，简化查询逻辑

3. 避免在WHERE中对字段使用函数：

    不推荐（无法使用索引）

   WHERE IFNULL(login_date, '2000-01-01') > '2020-01-01';

    推荐

WHERE login_date IS NOT NULL AND login_date > '2020-01-01';

与COALESCE和IFNULL函数结合

 使用COALESCE处理NULL值（返回第一个非NULL值）

SELECT name, COALESCE(login_date, '未登录') AS login_status
FROM students;

 使用IFNULL处理NULL值

SELECT name, IFNULL(login_date, 'N/A') AS login_date
FROM students;