#PostgreSQL NULL值

-- NULL的比较
SELECT NULL = NULL;      -- 结果: NULL（不是TRUE）
SELECT NULL <> NULL;     -- 结果: NULL
SELECT NULL = 0;         -- 结果: NULL
SELECT NULL = '';        -- 结果: NULL

-- 查找空值
SELECT * FROM users WHERE email IS NULL;

-- 查找非空值
SELECT * FROM users WHERE email IS NOT NULL;

-- 错误写法（永远不会返回结果）
SELECT * FROM users WHERE email = NULL;  -- 不要这样写！

SELECT 10 + NULL;    -- 结果: NULL
SELECT 10 * NULL;    -- 结果: NULL
SELECT 10 / NULL;    -- 结果: NULL
SELECT NULL + NULL;  -- 结果: NULL

SELECT 'Hello' || NULL;  -- 结果: NULL
SELECT CONCAT('Hello', NULL, 'World');  -- 结果: 'HelloWorld'（CONCAT忽略NULL）

SELECT TRUE AND NULL;   -- 结果: NULL
SELECT FALSE AND NULL;  -- 结果: FALSE
SELECT TRUE OR NULL;    -- 结果: TRUE
SELECT FALSE OR NULL;   -- 结果: NULL
SELECT NOT NULL;        -- 结果: NULL

-- 基本用法
SELECT COALESCE(NULL, 'default');  -- 结果: 'default'
SELECT COALESCE(NULL, NULL, 'third');  -- 结果: 'third'
SELECT COALESCE('first', NULL, 'third');  -- 结果: 'first'

-- 实际应用
SELECT
    username,
    COALESCE(nickname, username) as display_name
FROM users;

-- 处理可能为NULL的计算
SELECT
    product_name,
    COALESCE(discount_price, original_price) as actual_price
FROM products;

-- 基本用法
SELECT NULLIF(10, 10);  -- 结果: NULL
SELECT NULLIF(10, 20);  -- 结果: 10

-- 避免除零错误
SELECT 100 / NULLIF(divisor, 0) FROM calculations;

-- 将空字符串转为NULL
SELECT NULLIF(email, '') FROM users;

-- PostgreSQL没有NVL，使用COALESCE替代
SELECT COALESCE(commission, 0) FROM employees;

-- COUNT(*)计算所有行，COUNT(column)忽略NULL
SELECT COUNT(*) FROM users;         -- 所有行
SELECT COUNT(email) FROM users;     -- email不为NULL的行

-- SUM、AVG等忽略NULL值
SELECT AVG(salary) FROM employees;  -- 只计算非NULL值的平均

-- 示例数据
-- salary: 100, 200, NULL, 300
SELECT AVG(salary);  -- 结果: 200（不是150）

-- 如果需要将NULL视为0
SELECT AVG(COALESCE(salary, 0)) FROM employees;

-- 统计NULL值数量
SELECT COUNT(*) - COUNT(column_name) as null_count FROM table_name;

-- ASC时NULL排在最后
SELECT * FROM products ORDER BY price ASC;

-- DESC时NULL排在最前
SELECT * FROM products ORDER BY price DESC;

-- NULL排在最前
SELECT * FROM products ORDER BY price NULLS FIRST;

-- NULL排在最后
SELECT * FROM products ORDER BY price DESC NULLS LAST;

-- DISTINCT将所有NULL视为相等
SELECT DISTINCT category FROM products;
-- 多个NULL只返回一个NULL

-- 默认B-tree索引不包含NULL值的部分查询
CREATE INDEX idx_email ON users(email);

-- 查询NULL时可能不使用索引
SELECT * FROM users WHERE email IS NULL;

-- 创建只包含非NULL值的索引
CREATE INDEX idx_email_not_null ON users(email)
WHERE email IS NOT NULL;

-- 创建只包含NULL值的索引
CREATE INDEX idx_email_null ON users((email IS NULL))
WHERE email IS NULL;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,  -- 不允许NULL
    email VARCHAR(100)              -- 允许NULL
);

-- UNIQUE允许多个NULL值
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE
);

INSERT INTO test (code) VALUES (NULL);
INSERT INTO test (code) VALUES (NULL);  -- 成功！允许多个NULL

-- CHECK约束中NULL被视为通过
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL CHECK (price > 0)
);

INSERT INTO products (price) VALUES (NULL);  -- 成功！

SELECT
    first_name,
    last_name,
    COALESCE(middle_name, '') as middle_name,
    COALESCE(phone, 'N/A') as phone
FROM customers;

SELECT
    product_name,
    CASE
        WHEN stock IS NULL THEN 'Unknown'
        WHEN stock = 0 THEN 'Out of Stock'
        ELSE 'In Stock'
    END as availability
FROM products;

SELECT
    order_id,
    quantity * COALESCE(unit_price, 0) as line_total
FROM order_items;