[SOLVED] SET ANSI_NULL is ON but NULL rows are still being returned

SET ANSI_NULL is ON but NULL rows are still being returned

I have the following table with SET ANSI_NULL set to ON

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblStandard5columnCustomerDetails]
(
    [Id] [int] NOT NULL,
    [FName] [varchar](60) NULL,
    [Address] [varchar](50) NULL,
    [PhoneNumber] [varchar](11) NULL,
    [LName] [varchar](60) NULL
) ON [PRIMARY]

GO

And inserted the following one record:

Insert Into [dbo].[tblStandard5columnCustomerDetails] 
VALUES
(
1,
NULL,
NULL,
NULL,
NULL
);

According to Microsoft article I should not be returning any rows when using Select statement with WHERE column_name = NULL

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

But when is execute this query

select FNAME From [dbo].[tblStandard5columnCustomerDetails] WHERE FName IS NULL

I'm returning one row with a value of NULL

Why?

Solution

You basically answered your own question.

With "SET ANSI_NULLS ON":

the "relational operators" +, <, >, <=, >= will never evaluate to "true" or "false" if one of the operands is null.
IS NULL, on the other hand, will always evaluate to "true" (if null), or "false" (if not null).

Look here for more details: