0

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?

JNevill
  • 46,980
  • 4
  • 38
  • 63
Data Engineer
  • 795
  • 16
  • 41

1 Answers1

2

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:

Community
  • 1
  • 1
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • To have no NULL rows being returned with ANSI_NULL set to ON I need to use the Equals operator (= NULL), and not IS NULL. Thanks for the reference to the other post. It clarified a lot. – Data Engineer Feb 19 '16 at 19:51