SQL NULL 值處理 - 小人物看世界

NULL 值介紹 (SQL NULL Value)

這篇 SQL NULL 值處理教學將帶你認識 NULL 的基本概念與常見處理方式。在 SQL 中，NULL 代表「無值」或「未知值」，它既不是 0、也不是空字串，而是一個特殊的標記，表示該欄位沒有資料。

當資料表中的某個欄位沒有被賦予值時，該欄位就會自動存入 NULL。由於 NULL 不等於任何值（甚至不等於另一個 NULL），因此在查詢或運算時需要特別注意處理方式，否則可能會得到意想不到的結果。

IS NULL 與 IS NOT NULL 語法 (Syntax)

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

說明：

語法說明IS NULL判斷欄位值是否為 NULLIS NOT NULL判斷欄位值是否不為 NULL

注意：不能使用 = NULL 或 <> NULL 來判斷 NULL 值，因為 NULL 與任何值的比較結果都是 UNKNOWN，而非 TRUE 或 FALSE。必須使用 IS NULL 或 IS NOT NULL 來判斷。

IS NULL 與 IS NOT NULL 用法 (Example)

假設我們有一個員工資料表 employees 如下（若需了解如何建立資料表，請參考 CREATE TABLE 教學）：

NameDepartmentPhoneSalary張一業務部02-1234567835000王二資訊部NULL42000李三NULL07-1234567838000趙四資訊部03-12345678NULL陳五人資部NULL40000

查詢欄位為 NULL 的資料

使用 IS NULL 可以查詢某個欄位值為 NULL 的資料列。例如，查詢沒有填寫電話的員工：

SELECT Name, Phone
FROM employees
WHERE Phone IS NULL;

查詢結果如下：

NamePhone王二NULL陳五NULL

查詢欄位不為 NULL 的資料

使用 IS NOT NULL 可以查詢某個欄位值不為 NULL 的資料列。例如，查詢有填寫部門的員工：

SELECT Name, Department
FROM employees
WHERE Department IS NOT NULL;

查詢結果如下：

NameDepartment張一業務部王二資訊部趙四資訊部陳五人資部

COALESCE 函數 (SQL COALESCE Function)

COALESCE 是 SQL 標準函數，用來從一組參數中回傳第一個非 NULL 的值。如果所有參數都是 NULL，則回傳 NULL。COALESCE 是處理 NULL 值最常用也最通用的函數，幾乎所有主流資料庫都支援。

COALESCE 語法

COALESCE(value1, value2, ..., valueN)

COALESCE 會依序檢查每個參數，回傳第一個不是 NULL 的值。常見用法是將可能為 NULL 的欄位替換成預設值。

COALESCE 用法範例

使用前面的 employees 資料表，將 NULL 的電話欄位顯示為「未提供」：

SELECT Name, COALESCE(Phone, '未提供') AS Phone
FROM employees;

查詢結果如下：

NamePhone張一02-12345678王二未提供李三07-12345678趙四03-12345678陳五未提供

也可以提供多個替代值，COALESCE 會依序檢查，回傳第一個非 NULL 的值：

SELECT Name, COALESCE(Phone, Department, '無資料') AS contact_info
FROM employees;

查詢結果如下：

Namecontact_info張一02-12345678王二資訊部李三07-12345678趙四03-12345678陳五人資部

各資料庫的 NULL 處理函數

除了通用的 COALESCE 之外，不同的資料庫系統也提供了各自專屬的 NULL 處理函數。這些函數的功能類似，都是在欄位值為 NULL 時回傳替代值，但語法和名稱有所不同。

資料庫函數說明MySQLIFNULL(expr, alt)若 expr 為 NULL 則回傳 altOracleNVL(expr, alt)若 expr 為 NULL 則回傳 altSQL ServerISNULL(expr, alt)若 expr 為 NULL 則回傳 alt所有資料庫COALESCE(v1, v2, …)回傳第一個非 NULL 的值（SQL 標準）

MySQL IFNULL 用法

MySQL 的 IFNULL 函數接受兩個參數，若第一個參數為 NULL，則回傳第二個參數的值：

-- MySQL
SELECT Name, IFNULL(Salary, 0) AS Salary
FROM employees;

查詢結果如下：

NameSalary張一35000王二42000李三38000趙四0陳五40000

Oracle NVL 用法

Oracle 的 NVL 函數功能與 IFNULL 相同，語法也類似：

-- Oracle
SELECT Name, NVL(Salary, 0) AS Salary
FROM employees;

查詢結果與上方 IFNULL 範例相同。Oracle 還提供了 NVL2 函數，可以針對 NULL 和非 NULL 分別指定不同的回傳值：

-- Oracle NVL2(expr, not_null_value, null_value)
SELECT Name, NVL2(Phone, '已填寫', '未填寫') AS phone_status
FROM employees;

SQL Server ISNULL 用法

SQL Server 的 ISNULL 函數功能同樣類似：

-- SQL Server
SELECT Name, ISNULL(Salary, 0) AS Salary
FROM employees;

建議：如果需要跨資料庫相容，優先使用 COALESCE，因為它是 SQL 標準語法，所有主流資料庫都支援。

NULL 與聚合函數

在使用聚合函數時，NULL 值的處理方式是一個重要的觀念。大多數聚合函數（如 SUM、AVG、MIN、MAX）會自動忽略 NULL 值，而 COUNT 的行為則取決於參數。

-- COUNT(*) 計算所有資料列（包含 NULL）
SELECT COUNT(*) AS total_rows
FROM employees;

-- COUNT(column) 只計算該欄位非 NULL 的資料列
SELECT COUNT(Salary) AS has_salary
FROM employees;

-- AVG 會忽略 NULL 值
SELECT AVG(Salary) AS avg_salary
FROM employees;

查詢結果如下：

查詢結果說明COUNT(*)5計算所有資料列，包含有 NULL 的列COUNT(Salary)4只計算 Salary 不為 NULL 的列（趙四被排除）AVG(Salary)38750(35000+42000+38000+40000)/4，忽略 NULL

特別注意 AVG 的計算：因為趙四的 Salary 為 NULL 被忽略，所以平均值是四筆資料的平均 (35000+42000+38000+40000)/4 = 38750，而不是除以 5。如果你希望將 NULL 視為 0 來計算，可以搭配 COALESCE 使用：

SELECT AVG(COALESCE(Salary, 0)) AS avg_salary
FROM employees;
-- 結果：(35000+42000+38000+0+40000)/5 = 31000

NULL 與排序 (ORDER BY)

使用 ORDER BY 排序時，NULL 值的排列位置因資料庫而異。在 MySQL 和 SQL Server 中，NULL 被視為最小值，升序排序時會出現在最前面；在 Oracle 和 PostgreSQL 中，NULL 被視為最大值，升序排序時會出現在最後面。

SELECT Name, Salary
FROM employees
ORDER BY Salary ASC;

PostgreSQL 和 Oracle 支援 NULLS FIRST 和 NULLS LAST 語法，可以明確指定 NULL 的排列位置：

-- PostgreSQL / Oracle
SELECT Name, Salary
FROM employees
ORDER BY Salary ASC NULLS LAST;

NULL 運算注意事項

NULL 在運算中有一些特殊的行為，這些是使用 SQL 時必須注意的重點：

運算結果說明NULL = NULLUNKNOWNNULL 不等於 NULLNULL <> NULLUNKNOWNNULL 也不「不等於」NULLNULL + 10NULL任何值與 NULL 做算術運算結果都是 NULLNULL = ”UNKNOWNNULL 不等於空字串NULL AND TRUEUNKNOWNNULL 參與邏輯運算結果可能是 UNKNOWNNULL OR TRUETRUEOR 運算中只要一方為 TRUE 結果就是 TRUE

由於 NULL 與任何值的算術運算結果都是 NULL，在計算欄位時要特別注意。例如：

SELECT Name, Salary, Salary * 12 AS annual_salary
FROM employees;

查詢結果如下：

NameSalaryannual_salary張一35000420000王二42000504000李三38000456000趙四NULLNULL陳五40000480000

趙四的 Salary 為 NULL，所以 NULL * 12 的結果仍然是 NULL。若要避免這種情況，可以搭配 COALESCE 將 NULL 替換為 0：

SELECT Name, COALESCE(Salary, 0) * 12 AS annual_salary
FROM employees;

延伸閱讀

ㄚ槌