When i started learing SQL Server one of the mistakes I did a few times was something like the following.
1: IF OBJECT_ID('Invoice') IS NOT NULL DROP TABLE Customers
2: GO
3: CREATE TABLE Invoice(
4: InvoiceId int,
5: InvoiceNo VARCHAR(10),
6: InvoiceStatus)
7: GO
8:
9: INSERT INTO Invoice(InvoiceId , InvoiceNo , InvoiceStatus) SELECT 1,'Jacob','Active'
10: INSERT INTO Invoice(InvoiceId , InvoiceNo , InvoiceStatus) SELECT 2,'Mike','Inactive'
11: INSERT INTO Invoice(InvoiceId , InvoiceNo , InvoiceStatus) SELECT 3,'Steve', NULL
12:
13: SELECT *
14: FROM Invoice
15: WHERE InvoiceStatus = NULL
Well, my queries never returned a row, even though there is a row in the table with a NULL value in InvoiceStatus column. Then I tried the opposite.
1: SELECT *
2: FROM Invoice
3: WHERE InvoiceStatus <> NULL
This did not return anything as well.
I realized that the result of a comparison operation that involves a NULL value is UNKNOWN. That is the reason why the above queries did not return any row. The correct way to write the queries is:
1: SELECT InvoiceNo,InvoiceStatus
2: FROM Invoice
3: WHERE InvoiceStatus IS NULL
4: /*
5: InvoiceNo InvoiceStatus
6: -------------------- --------------
7: Steve NULL
8: */
9: SELECT InvoiceNo,InvoiceStatus
10: FROM Invoice
11: WHERE InvoiceStatus IS NOT NULL
12: /*
13: InvoiceNo InvoiceStatus
14: -------------------- --------------
15: Jacob Active
16: Mike Inactive
17: */
So where does SET ANSI_NULLS fit in this story?
Well,the behavior of comparison operations (= and <>) when they are used with NULL values depends on the setting of this option. The default value of SET ANSI_NULLS is ON. This setting causes the behavior explained in the previous examples.
The two examples we saw at the beginning of this post will work correctly if we set ANSI_NULLS to OFF. The following example demonstrates that.
1: SET ANSI_NULLS OFF
2: SELECT InvoiceNo,InvoiceStatus FROM Invoice WHERE InvoiceStatus = NULL
3:
4: /*
5:
6: InvoiceNo InvoiceStatus
7: ---------- -------------
8: Steve NULL
9:
10: */
11:
12: SELECT InvoiceNo,InvoiceStatus FROM Invoice WHERE InvoiceStatus <> NULL
13:
14: /*
15:
16: InvoiceNo InvoiceStatus
17: ---------- -------------
18: Jacob Active
19: Mike Inactive
20:
21: */
Very helpful!
Simply Good to have an idea on ANSI_NULLS.
Keep it up.