-- 創(chuàng)建一個測試表
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

-- 插入示例數(shù)據(jù)
INSERT INTO customers (id, name, age) VALUES 
(1, 'John Doe', NULL),
(2, 'Jane Smith', 25),
(3, NULL, 30);

-- 判斷字段是否為空
SELECT name FROM customers WHERE age IS NULL;
SELECT name FROM customers WHERE age IS NOT NULL;

-- 測試IS NULL
SELECT CASE 
  WHEN (age IS NULL) THEN 'Age is NULL'
  ELSE 'Age is not NULL'
END AS result FROM customers;

-- 測試IS NOT NULL
SELECT CASE 
  WHEN (age IS NOT NULL) THEN 'Age is not NULL'
  ELSE 'Age is NULL'
END AS result FROM customers;

-- 使用COALESCE判斷字段是否為空
SELECT name FROM customers WHERE COALESCE(age, '') = '';

-- 返回第一個非NULL參數(shù)的值
SELECT COALESCE(NULL, 'Hello', 'World'); -- 輸出Hello
SELECT COALESCE(NULL, NULL, NULL); -- 輸出NULL

-- 使用COALESCE函數(shù)進行非空判斷
SELECT CASE 
  WHEN COALESCE(age, '') = '' THEN 'Age is NULL'
  ELSE 'Age is not NULL'
END AS result FROM customers;

-- 使用IFNULL函數(shù)進行非空處理
SELECT IFNULL(name, 'N/A') FROM customers;

-- 使用IFNULL函數(shù)進行非空處理
SELECT IFNULL(name, 'N/A') FROM customers;

-- 使用NULLIF函數(shù)進行非空判斷
SELECT NULLIF(age, 0) FROM customers;

-- 使用NULLIF函數(shù)進行非空判斷
SELECT CASE 
  WHEN NULLIF(age, 0) IS NULL THEN 'Age is NULL'
  ELSE 'Age is not NULL'
END AS result FROM customers;