April 26, 2002 at 12:39 pm
Hi,
Does anybody knows what is the difference between
Status <> null and
Status is not null
Apparently they behave different?
Thanks,
Durug
April 26, 2002 at 1:18 pm
From BOL:
*************************************************************
SET ANSI_NULLS (T-SQL)
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.
Syntax
SET ANSI_NULLS {ON | OFF}
Remarks
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.
When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name.
A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard.
A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.
*******************************************
IS NULL is not affected by the SET ANSI_NULLS setting.
April 26, 2002 at 2:04 pm
Exactly. Good practice is to us is null or is not null. Saves a lot of headaches.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply