This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.
SET ANSI_NULLS ON : When on then It don’t show the Null Values and return 0
SET ANSI_NULLS OFF: When Off It it shows the null values
Example : We are taking below table example.
empid | empname | phone |
1 | rohit | NULL |
2 | rohit2 | NULL |
3 | karan | NULL |
5 | test | NULL |
8 | test | UnknownNumber |
Example:
SET ANSI_NULLS ON
SELECT empname FROM emp1 WHERE phone=NULL
Explanation:
It will return nothing because SET ANSI_NULLS is ON.
Example:
SET ANSI_NULLS Off
SELECT empname FROM emp1 WHERE phone=NULL
Explanation:
It will return first four where phone is null because SET ANSI_NULLS is Off.
If we will run below update query then it will not effect any thing, because SET ANSI_NULLS is ON and it is returning 0 comparison in result. So It will show 0 rows effected.
SET ANSI_NULLS ON
update emp1 set phone='myphonenumber' where phone=Null