NULL

• DuruG

  SSCrazy

  Points: 2089

  More actions

  April 26, 2002 at 12:39 pm

  #78135

  Hi,

  Does anybody knows what is the difference between

  Status <> null and

  Status is not null

  Apparently they behave different?

  Thanks,

  Durug

• Eve

  SSCrazy

  Points: 2480

  More actions

  April 26, 2002 at 1:18 pm

  #427967

  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.

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  April 26, 2002 at 2:04 pm

  #427970

  Exactly. Good practice is to us is null or is not null. Saves a lot of headaches.

  Andy

  Andy
  Connect with me on LinkedIn