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

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

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

1. 邏輯與操作

特別注意：

• 對于AND運(yùn)算符，只有當(dāng)兩邊的運(yùn)算結(jié)果都為真時(shí)，最終結(jié)果才為真
• 真 AND 未知 = 未知， 并不是真

2. 邏輯或操作

• 對于OR運(yùn)算符，只要兩邊的運(yùn)算有一個(gè)為真時(shí)，最終結(jié)果就為真，否則最終結(jié)果為假或者未知

3. 邏輯非操作

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

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

三、NULL空值

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

1. NULL判斷的特殊性

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

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

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

替代方案

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

-- 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() 運(yùn)算符的影響

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

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

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

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

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

-- 無法篩選出任何記錄
SELECT *
FROM student
WHERE name NOT IN("LiLei", "HanMeimei", NULL)	
/* 因?yàn)樵涞韧冢?WHERE name != "LiLei" and  name != "HanMeimei" and name != NULL. 
   任何值在最后一句中的判斷結(jié)果都會(huì)為"UNKOWN"，使得整個(gè)判斷變?yōu)槲粗贿^濾掉 */

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

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

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

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

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

總結(jié)

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

4. 空值處理

COALESCE函數(shù)

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

SELECT COALESCE(yuwen_score, shuxue_score, yingyu_score)
FROM student

可以用COALESCE將NULL轉(zhuǎn)換為別的默認(rèn)值，類似于CASE WHEN

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

NULLIF函數(shù)

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

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

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

SELECT AVG(yuwen_score)/NULLIF(yuwen_score, 0) --若某同學(xué)語文成績?yōu)?，則分母為NULL（不是0），此時(shí)不會(huì)報(bào)錯(cuò)
FROM student

IFNULL函數(shù)

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

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

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

最新評論