SQL中三值逻辑和NULL的具体使用

SQL中的三值逻辑和NULL是处理缺失信息的重要概念。让我详细解释：

一、三值逻辑（Three-Valued Logic, TVL）

在SQL中，逻辑运算结果有三种可能：

• TRUE（真）
• FALSE（假）
• UNKNOWN（未知） - 当涉及NULL时

逻辑运算真值表

-- AND运算
TRUE AND NULL = UNKNOWN
FALSE AND NULL = FALSE
NULL AND NULL = UNKNOWN

-- OR运算
TRUE OR NULL = TRUE
FALSE OR NULL = UNKNOWN
NULL OR NULL = UNKNOWN

-- NOT运算
NOT TRUE = FALSE
NOT FALSE = TRUE
NOT UNKNOWN = UNKNOWN

二、NULL的特性

1. NULL代表"未知"或"缺失"

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    hire_date DATE
);

-- salary为NULL表示工资未知
-- department为NULL表示部门未分配

2. 比较运算中的NULL

-- 以下比较结果都是UNKNOWN：
NULL = NULL    -- 不是TRUE！
NULL <> NULL   -- 不是TRUE！
NULL > 100     -- UNKNOWN
NULL = 'John'  -- UNKNOWN

三、处理NULL的关键操作

1. IS NULL / IS NOT NULL

-- 正确：查找salary为NULL的记录
SELECT * FROM employees WHERE salary IS NULL;

-- 错误：这不会返回任何行（因为UNKNOWN）
SELECT * FROM employees WHERE salary = NULL;

-- 正确：查找salary不为NULL的记录
SELECT * FROM employees WHERE salary IS NOT NULL;

2. COALESCE函数

-- 返回第一个非NULL值
SELECT name, COALESCE(salary, 0) as actual_salary
FROM employees;
-- 如果salary为NULL，显示0

SELECT COALESCE(department, '未分配', 'N/A') 
FROM employees;

3. NULLIF函数

-- 如果两个值相等，返回NULL
SELECT name, NULLIF(salary, 0) as salary
FROM employees;
-- 如果salary=0，显示为NULL

四、聚合函数与NULL

-- COUNT(*) 计算所有行，包括NULL
SELECT COUNT(*) FROM employees;  -- 返回总行数

-- COUNT(column) 忽略NULL值
SELECT COUNT(salary) FROM employees;  -- 只计算非NULL的salary

-- 其他聚合函数自动忽略NULL
SELECT 
    AVG(COALESCE(salary, 0)) as avg_with_zeros,
    AVG(salary) as avg_ignore_nulls  -- 忽略NULL
FROM employees;

-- SUM、MIN、MAX等都忽略NULL

五、连接操作中的NULL

-- INNER JOIN：NULL不会匹配
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
-- dept_id为NULL的行不会出现在结果中

-- LEFT JOIN：保留左表所有行
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- dept_id为NULL时，dept_name显示为NULL

六、ORDER BY和GROUP BY中的NULL

-- ORDER BY：NULL默认排在最后（MySQL、PostgreSQL）
SELECT * FROM employees ORDER BY salary;
-- NULL值排在最后

-- GROUP BY：所有NULL被分到同一组
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
-- department为NULL的行被分到一组

七、实际应用示例

场景1：处理缺失值

-- 统计报表，处理可能的NULL值
SELECT 
    name,
    COALESCE(department, '未分配') as dept,
    CASE 
        WHEN salary IS NULL THEN '未设置'
        WHEN salary = 0 THEN '待定'
        ELSE CAST(salary AS VARCHAR)
    END as salary_status
FROM employees;

场景2：安全比较

-- 安全的数值比较
SELECT * FROM products
WHERE (price IS NULL AND :input_price IS NULL)
   OR price = :input_price;

-- 或者使用 <=> 运算符（MySQL特有）
SELECT * FROM products
WHERE price <=> :input_price;  -- 可以正确处理NULL

八、最佳实践建议

九、不同数据库的差异

-- MySQL：NULL安全比较运算符
SELECT * FROM t1 WHERE col1 <=> NULL;

-- PostgreSQL：IS DISTINCT FROM
SELECT * FROM t1 WHERE col1 IS DISTINCT FROM col2;

-- SQL Server：ANSI_NULLS设置影响比较行为
SET ANSI_NULLS ON;  -- 标准行为
SET ANSI_NULLS OFF; -- NULL = NULL 返回TRUE

理解三值逻辑和正确使用NULL是编写健壮SQL查询的关键，特别是在处理真实世界数据时，缺失值是常见情况。