Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jun 16, 1999

Using NULL in a Query's Where Clause

While comparing a field to NULL, a SQL query should use the IS operator in place of the "=" operator. The reason is that if the ANSI_NULLS option is On for a connection, expressions being compared to a NULL value will not return a true or false value but rather they will return an unknown value. To make it simpler, if a table xxx has a column by name col1 that contains two NULL values then this query would result in fetching zero records:
Select *  from xxx where col1 = NULL
Meanwhile, this query would result in fetching two records:
Select *  from xxx where col1 IS  NULL
However, if you set ANSI_NULLS off then both of these queries would return two records. Similarly, it is better to use IS NOT in place of operator "<>" while comparing against NULL.
KulBhushan Sharma
Thanks for your registration, follow us on our social networks to keep up-to-date