一、新邏輯狀態(tài)：未知

• 對于大多數(shù)其他語言的邏輯判斷，一般只有兩種結果：真(TURE)或假(FALSE)
• 但在SQL中，還會有第三種判斷結果：未知(UNKNOWN)，表示無法判斷出真或者假。

未知狀態(tài)會影響傳統(tǒng)邏輯運算（與或非等）的結果，總結如下：

1. 邏輯與操作

特別注意：

• 對于AND運算符，只有當兩邊的運算結果都為真時，最終結果才為真
• 真 AND 未知 = 未知， 并不是真

2. 邏輯或操作

• 對于OR運算符，只要兩邊的運算有一個為真時，最終結果就為真，否則最終結果為假或者未知

3. 邏輯非操作

二、SQL中"未知"狀態(tài)的判斷影響

SQL語句中的WHERE、HAVING、CASE表達式，只返回邏輯運算結果為真的數(shù)據(jù)，不返回為假或者未知的數(shù)據(jù)

三、NULL空值

在數(shù)據(jù)庫中，空值NULL是一個特殊值，表示缺失或者未知
在SQL語句中，任何數(shù)據(jù)與空值進行算術比較的結果是未知，而非真或非假.。所以空值NULL無法通過 “WHERE c1 = NULL” 來判斷，需要寫成 WHERE c1 IS NULL ，才能作為條件篩選出查詢字段為NULL的數(shù)據(jù)

1. NULL判斷的特殊性

即使兩個未知數(shù)據(jù)進行比較，運算結果也是未知的，比如下面的比較，都得不出TRUE，而是未知。以下例子則判斷為未知

NULL = 0
NULL != 0
NULL = ‘’ (空字符串)
NULL = !‘’
NULL = NULL
NULL = !NULL

因此在WHERE語句中進行判斷時，務必要注意查詢列或者查詢條件（即等號兩邊的數(shù)據(jù)）都有沒有可能為NULL，如果有則要用IS NULL來判定

替代方案

Mysql提供了<=>運算符，即可等值比較，也可空值比較；

-- mysql
SELECT 1 <=> 1, NULL <=> NULL;

PostgreSQL提供的是：IS [NOT] DISTINCT FROM

-- postgreSQL
SELECT 1 IS DISTINCT FROM 1, NULL IS DISTINCT FROM NULL;

2. NULL對IN() 運算符的影響

IN運算符為判斷所給條件是否在某個集合中。內部實際使用等值運算符=來判斷是否和集合中的每個元素相等，再用OR串聯(lián)起來得到最后的邏輯結果。

SELECT *
FROM student
WHERE name IN("LiLei", "HanMeimei")		-- 等同于 WHERE name = "LiLei" OR name = "HanMeimei"

因此如果想通過IN() 運算符中加NULL元素來將被查字段中的NULL值也篩選出來，實際是無法生效的。比如想實現(xiàn)以下SQL篩選出學生姓名為NULL的，不會有效

-- 無法篩選出name為NULL的記錄
SELECT *
FROM student
WHERE name IN("LiLei", "HanMeimei", NULL)		
/* 等同于 WHERE name = "LiLei" OR name = "HanMeimei" or name = NULL   
   對于真正name為NULL的數(shù)據(jù)，此表達式最終的結果為未知，不會被篩選出來   */

在NOT IN() 中使用NULL，影響會更大，使得判斷無法篩選出任何記錄

-- 無法篩選出任何記錄
SELECT *
FROM student
WHERE name NOT IN("LiLei", "HanMeimei", NULL)	
/* 因為原句等同于： WHERE name != "LiLei" and  name != "HanMeimei" and name != NULL. 
   任何值在最后一句中的判斷結果都會為"UNKOWN"，使得整個判斷變?yōu)槲粗贿^濾掉 */

3. NULL對子查詢語句中，ALL()/ANY() 運算符的影響

子查詢中，可以通過比較運算符(=、!=、<、<=、>、>=)與ALL、ANY的組合，來表示等于、不等于、大于…集合中的全部數(shù)據(jù)

SELECT *
FROM student
WHERE class =ANY (		-- 查找屬于1年級的學生
	SELECT class 
	FROM teacher
	WHERE grade = 1
)

• ALL運算符相當于：對其中每個選項進行比較運算符計算，并用AND運算符串聯(lián)
• IN運算符相當于：對其中每個選項進行=運算符計算，并用OR運算符串聯(lián)
• ANY與IN類似，也是由OR運算符串聯(lián)，比較運算符寫于ANY之前；如果是=ANY，則與IN相同

對于ALL、ANY等運算符，后面加上NULL不會成功篩選出想要的NULL數(shù)據(jù)，相反會導致比較離譜的運算結果

總結

• 使用IN/NOT IN/ALL/ANY時，切記不要在選項中設置NULL。對于子查詢做以上匹配結果時，也要注意務必過濾下NULL數(shù)據(jù)

4. 空值處理

COALESCE函數(shù)

COALESCE(exp1, exp2, exp3, …)接收一個輸入列表，返回第一個非NULL的參數(shù)；若都為空，則返回NULL

SELECT COALESCE(yuwen_score, shuxue_score, yingyu_score)
FROM student

可以用COALESCE將NULL轉換為別的默認值，類似于CASE WHEN

SELECT COALESCE(yuwen_score, 0)   -- 若語文成績?yōu)镹ULL， 則記為0分
FROM student

NULLIF函數(shù)

NULLIF(exp1, exp2)接收兩個入?yún)ⅲ喝粝嗟葎t返回NULL；若不等則返回exp1

SELECT NULLIF(yuwen_score, 0)   -- 若語文成績?yōu)?， 則記為NULL；不為0，則取此成績
FROM student

NULLIF函數(shù)最大的目的是被用來防止除零錯誤

SELECT AVG(yuwen_score)/NULLIF(yuwen_score, 0) --若某同學語文成績?yōu)?，則分母為NULL（不是0），此時不會報錯
FROM student

IFNULL函數(shù)

MYSQL與SQLite才有，入?yún)⒅挥袃蓚€，功能是返回兩個入?yún)⒅械谝粋€非空的值（可視為入?yún)⒐潭閮蓚€的COALESCE函數(shù)）。注意與NULLIF區(qū)分。

SELECT IFNULL(yuwen_score, 0)   -- 若語文成績?yōu)镹ULL， 則記為0
FROM student

到此這篇關于SQL 特殊狀態(tài)“未知“以及“空值NULL“的判斷的文章就介紹到這了,更多相關SQL 未知狀態(tài)及“空值NULL內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！ 

最新評論