What is SET ANSI_NULL ?

’ANSI_NULL’ setting is related to the behavior of comparison operators (=, <>) with’ NULL’ values in a query. Working with ‘NULL’ values in query is very tricky, because 'NULL' is unknown value. 

Now see how I come to know about ANSI_NULL,It is very interesting when I progresses through one by one.

One day I was brushing my basic sql queries  And I come to situation which I am describing below.

I have created a table called employee having columns Id,Name and salary.

Id is Primary key and Identity as true.

Name is varchar(100)  null,

Salary is float null

Here is the query for table creation:

CREATE TABLE [dbo].[Employee]

(

          [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

          [name] [varchar](100) NULL,

          [salary] [float] NULL

)

After creation of table I have inserted seven rows in employee table-

   INSERT INTO Employee values('Steve',10000)

   INSERT INTO Employee values('Martin',30000)

   INSERT INTO Employee values('Jane',50000)

   INSERT INTO Employee values('Suresh',NULL)

   INSERT INTO Employee values('Pavan',50000)

   INSERT INTO Employee values('kamal',NULL)

   INSERT INTO Employee values('Julian',100000)

Selecting all rows from employee table.

SELECT * FROM employee

It will return 7 rows.

Now , If I do select query on the employee table using comparison operator.Ideally first query should return two rows and second should five rows. But these query will not return any rows at all.

  

 SELECT * FROM employee WHERE Salary=NULL

 SELECT * FROM employee WHERE Salary<>NULL

I wondered that how it is not working.Then I started learning how ‘null’ works.

I come to know than ‘NULL’ value is unknown.

Now , see below queries.By using  ‘IS NULL’ and ‘IS NOT NULL’ we can get desired result.

   SELECT * FROM employee WHERE Salary IS NULL

   SELECT * FROM employee WHERE Salary IS NOT NULL

These two queries will return two and five rows.

So now you are thinking why I am discussing these things in stead of discussing ANSI_NULL.

Here is explanation,

By Default SET ANSI_NULLS is ON. So comparison operator (=, <>) will not work when we are using NULL values to compare.

See below queries I have SET ANSI_NULLS OFF And running same query which I have run earlier.This time I got the desired result.

 

SET ANSI_NULLS OFF

  

 SELECT * FROM employee WHERE Salary=NULL

 SELECT * FROM employee WHERE Salary<>NULL