SQL Server null知多少？

来源:http://www.cnblogs.com/i6first/archive/2016/03/01/5229633.html
-Advertisement-
Play Games

null是什麼？ 不知道。我是說，他的意思就是不知道（unknown）。 它和true、false組成謂詞的三個邏輯值，代表“未知”。與true和false相比，null最難以令人捉摸，因為它沒有明確的值，在不同的場景下，它能代表不同的含義。下文以例子的方式給大家分享下null使用的典型場景及對應的


null是什麼？

不知道。我是說，他的意思就是不知道（unknown）。

它和true、false組成謂詞的三個邏輯值，代表“未知”。與true和false相比，null最難以令人捉摸，因為它沒有明確的值，在不同的場景下，它能代表不同的含義。下文以例子的方式給大家分享下null使用的典型場景及對應的用法。

1.check約束與null

之前在SQL ServerCentral.com上看到一個關於check約束的null問題，

作者創建了一個表，在欄位orderstatus上設置了check約束，只能插入指定的value的行，現在插入幾行數據，其中有一行的value為null，最後問最終有幾行可以插入成功。

原文如下：

I want to ensure that the status column for my Orders table only contains specific values. I decide to use this code:

create table Orders
( OrderID int primary key
, OrderTotal MONEY
, OrderStatus VARCHAR(20)
constraint Orders_Status_Code check( OrderStatus in ('ACTIVE', 'INACTIVE', 'TBD'))
);
go

Now I want to insert data into the table. I run this batch.

insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL

How many rows are in the table? I am running on a default, SQL Server 2014 instance with US English defaults.

（大家先想想答案，如果沒有把握就找個測試環境試一試）

《T-SQL基礎教程》中關於check約束與null的描述，著者用了一句言簡意賅的口訣“check約束拒絕false，接受true和null”。

在上面的例子中，當orderstatus為‘Avative’和’InActive’時，check約束判斷的結果是true，所以會插入成功，當為'Not Active’判斷的結果為false，插入不成功，最後當為'Null’時，判斷的結果是null，插入成功。

所以，正確答案是3。

 

2.比較運算與null

null一個特殊性在於它無法比較（和計算）。null與任何值的任何比較（和計算）都等於null。（unique約束除外，在unique約束中，null是相等的，同一個欄位不允許出現兩次null）

比如判斷null=null的結果是null，判斷null<>null的結果也還是null。下麵我以不等於(<>)為例，演示比較運算對null的判斷。

我先創建一個表，然後插入多行數據，其中有一行orderstatus的值為null，

 
if object_id(N’Orders’) is not null drop table orders
 
create table Orders
( OrderID int primary key
, OrderTotal MONEY
, OrderStatus VARCHAR(20)
);
go
 
 
insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL
 
 

現在我執行了一個where orderstatus<>'Active' 的查詢，

select * from orders where OrderStatus<>'Active'

大家想想null所在的行會不會在查詢結果裡面。

在上面的例子中，當orderstatus為'InActive' 和'Not Active' 時，where條件判斷的結果為true，但當orderstatus為'null' 時，where OrderStatus<>'Active'等價於where null <>'Active'，而null與任何一個值的比較結果還是null，所以where條件判斷的結果為null。

在SQL Server中，where篩選的原則是“接受true，拒絕false和null”（《T-SQL基礎教程》）。所以orderstatus為'InActive' 和'Not Active'的行顯示在結果集總，而orderstatus為null的行不會出現在結果集中。

最終，正確答案是：只會返回兩行

image

 

3.Not in與null和Not exists與null

not in和not exists都可以用來判斷某個對象的存在與否，在大多數場景下兩者可以相互替換，但在遇到null時，因為前者是三值邏輯（true|false|unknow）判斷而後者只會返回true或false，因此處理的結果會有很大不同。

為了演示兩者的區別，我們還是沿用上文的表，分別使用not in和not exists執行一個查詢，找出OrderStatus 不為'Active'和'InActive'的行。

 
if object_id(N’Orders’) is not null drop table orders
 
create table Orders
( OrderID int primary key
, OrderTotal MONEY
, OrderStatus VARCHAR(20)
);
go
 
 
insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL
 

 

3.1Not In與null

在下麵這個查詢中，where子句中使用not in來過濾數據，where子句的條件是OrderStatus not in ('Active'，'InActive')，我們期望結果集中包含orderstatus為'Not Active'、'NULL'這兩行的數據。




select * from orders where OrderStatus not in ('Active','InActive')



這個查詢中，當OrderStatus為null時， 原where子句等價於where null <>'Active' OR  null<>'InActive'，這就變成了上文中介紹的比較運算與null的問題。where的判斷結果還是null，所以該行不會出現在結果集中。而當OrderStatus為'Not Active'時，滿足where篩選的為true的條件，會顯示在結果集中。


最終，正確答案是：只有一行。


image


說明：in與null的關係與此同理。


 


3.2Not exists與null


現在我們還是期望結果集中包含orderstatus為'Not Active'、'NULL'這兩行的數據，這次用Not exists。


在這個查詢中，子查詢先求出OrderStatus='Active' or  OrderStatus='InActive的行，然後外部查詢用not exists過濾子查詢的結果，將剩下的行顯示在最終結果集中。



SELECT *
FROM orders AS o1
WHERE NOT EXISTS( 
                  SELECT *
                  FROM orders AS o2
                  WHERE o1.OrderStatus = o2.OrderStatus
                    AND ( o2.OrderStatus = 'Active'
                       OR o2.OrderStatus = 'InActive'
                        ));



 


image


 


為了方便理解，我們將子查詢改寫成自表連接的方式，



select * from orders as o2 where  o1.OrderStatus=o2.OrderStatus and (o2.OrderStatus='Active' or  o2.OrderStatus='InActive' ))



改寫成：



SELECT *
FROM orders AS o2
     INNER JOIN orders o1 ON o1.OrderStatus = o2.OrderStatus
                         AND ( o2.OrderStatus = 'Active'
                            OR o2.OrderStatus = 'InActive'
                             );



返回的結果集為：


image


然後我們再看外層查詢，


外部查詢期望使用not exists返回orders表中不包含子查詢結果集的行，也就是說，只要orders表沒有子查詢結果集中的行就返回true，否則返回false（只有存在和不存在，沒有unknown的說法）。


按照這個邏輯，orderID為3和4的行不在子查詢的結果集中，因此not exists判斷為true，而orderID為1和2的行已包含在子查詢的結果集中，所以not exists判斷為false。最後根據where篩選“接受true，拒絕false和null”的原則，最終只有orderID為3和4的行顯示在結果集中。


image


說明：exists與null的關係與此同理。


 


3.3Not  in和Not exists的區別


not in實際上是對一個對象的比較運算，而比較存在true|false|unknow三種邏輯值。


not exsits判斷某個對象存在或者不存在，它只有這兩種狀態，沒有unknown的說法。因此相比not in而言，not exists只會有true和false這兩種邏輯值。


 


總結：


上文介紹了null在不同場景中的含義，考慮到SQL不同的語言元素對null的不同處理方式，平常我們在寫SQL語句的時候應該清晰思考自己編寫的每個查詢對null或三值邏輯的處理，避免出現邏輯錯誤。



              
	   




	

	
您的分享是我們最大的動力!




			 	

	
	

    


-Advertisement-
Play Games
     


	


    

    

    




	       	  

              
更多相關文章  

              

                   
    


				
  • 
				
    
			    	
    
				    	Mysql 主從複製
			    	
    
					
    mysql 主從 複製
    
				
    
		    
  • 
				
    
			    	
    
				    	MS Sql事務
			    	
    
					
    1、以下是一個簡單事務存儲過程，其中Users表ID為主鍵 CREATE PROCEDURE SP_TRANSACTIONAS BEGIN BEGIN TRANSACTION INSERT INTO USERS(ID,NAME)VALUES(1000,'TEST1000'); IF @@ERROR<
    
				
    
		    
  • 
				
    
			    	
    
				    	oracle授權grant
			    	
    
					
    GRANT privilege [, ...] ON object [ ( column [, ...] ) ] [, ...] TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ] 這些欄位與 Postgres 實現是相容的，除了下麵一些例外
    
				
    
		    
  • 
				
    
			    	
    
				    	oracle sql 語句優化
			    	
    
					
    （1）選擇最有效率的表名順序(只在基於規則的優化器中有效)：Oracle的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查
    
				
    
		    
  • 
				
    
			    	
    
				    	安裝 logstash 2.2.0 和 elasticsearch 2.2.0
			    	
    
					
    本文內容 Elasticsearch logstash 本文介紹安裝 logstash 2.2.0 和 elasticsearch 2.2.0，操作系統環境版本是 CentOS/Linux 2.6.32-504.23.4.el6.x86_64。 安裝 JDK 是必須的，一般操作系統都會有，只是版本的...
    
				
    
		    
  • 
				
    
			    	
    
				    	關係型資料庫
			    	
    
					
    關係型資料庫的定義及設計思路
    
				
    
		    
  • 
				
    
			    	
    
				    	說下查詢動作 Pivot
			    	
    
					
    上一篇說了一下查詢5步走~然後就幾天_(:з」∠)_ ~今天繼續說一下其中 表裡面操作符裡面的 Pivot ~ Pivot 在實現行轉列的時候灰常有用。通常一個例子 CREATE TABLE #Tbl (Emp VARCHAR(50), [WeekDay] VARCHAR(50),LoginTime
    
				
    
		    
  • 
				
    
			    	
    
				    	SQL語句分組排序，多表關聯排序
			    	
    
					
    SQL語句分組排序，多表關聯排序總結幾種常見的方法： 案例一： 在查詢結果中按人數降序排列,若人數相同,則按課程號升序排列？ 分析：單個表內的多個欄位排序，一般可以直接用逗號分割實現。 select * from tableA order by col1 desc,col2 asc; -- 先按co
    
				
    
		    
    • 
				 
		  
  
                  

              

          



		
        

        
        

            
 







    
一周排行

    

         
    

 
    -Advertisement-
    Play Games
    	   
	
  • 
			        	
    
			    	        
    
				    	        移動開發（一）：使用.NET MAUI開發第一個安卓APP
			            	
    
				        	
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf ToggleButton選中效果和一個登錄界面
			            	
    
				        	
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        彈幕樹洞項目功能新增篇
			            	
    
				        	
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第27篇 sqlserver2022詳細安裝步驟
			            	
    
				        	
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 開源高性能 MQTT 類庫
			            	
    
				        	
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
			            	
    
				        	
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
			            	
    
				        	
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
			            	
    
				        	
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
			            	
    
				        	
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第28篇 如何.net中實現高效可靠數據同步api
			            	
    
				        	
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類