Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.



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