Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jun 15, 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
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date