--創(chuàng)建一張允許NULL值的表
CREATE TABLE Test_NULL (
  num INT NOT NULL PRIMARY KEY
  ,fname NVARCHAR(50) NULL
  ,lname NVARCHAR(50) NULL
)

--對表插入4條數(shù)據(jù)：最后2條記錄含有NULL值
INSERT INTO Test_NULL (num,fname,lname) VALUES(1, 'Tom','Jane')
INSERT INTO Test_NULL (num,fname,lname) VALUES(2, 'Dave','')
INSERT INTO Test_NULL (num,fname) VALUES(3, 'Aaron')
INSERT INTO Test_NULL (num,fname) VALUES(4, 'Betty')

SELECT
  *
FROM Test_NULL tn 
LEFT JOIN Test_NULL g
  ON tn.num = g.num
WHERE tn.lname = g.lname
------------------------------------------
1  Tom Jane  1  Tom Jane
2  Dave    2  Dave  

--查詢lname為''的記錄，即驗證NULL不等于''
SELECT
  *
FROM Test_NULL tn
WHERE tn.lname = ''
------------------------------------------
2  Dave  

--通過IS NULL查詢含有NULL的記錄
SELECT
  *
FROM Test_NULL tn
WHERE tn.lname IS NULL
------------------------------------------
3  Aaron  NULL
4  Betty  NULL

--NULL不等于任何值，甚至NULL不等于NULL
--默認(rèn)不能使用<>或=匹配NULL
SELECT
  *
FROM Test_NULL tn
WHERE tn.lname <> NULL OR tn.lname = NULL
------------------------------------------

--設(shè)置ANSI_NULLS為OFF，并使用=NULL查詢記錄
SET ANSI_NULLS OFF
SELECT
  *
FROM Test_NULL tn
WHERE tn.lname = NULL
------------------------------------------
3  Aaron  NULL
4  Betty  NULL

--插入1條含有NULL的新記錄
INSERT INTO Test_NULL (num,fname,lname) VALUES(5, 'Serena', NULL)

--更新某條記錄的字段值為NULL
UPDATE Test_NULL SET fname = NULL
WHERE num = 2