Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itsmarkdavies
Starting Member
14 Posts |
 Posted - 2007-02-28 : 05:47:25
|
| Field1 in Table Contractors can contain either 'A', 'I' or NULL.The following SQL does NOT return NULL values :-SELECT C.Field1, M.Field2FROM Contractors C INNER JOIN Meters M ON C.ID = M.IDWHERE M.FeedStatus <> 'A'But this does :-SELECT C.Field1, M.Field2FROM Contractors C INNER JOIN Meters M ON C.ID = M.IDWHERE (M.FeedStatus <> 'A' OR M.FeedStatus IS NULL)My queation is, why does the 1st SQL not return NULL values, and do you always have to check for NULL when using "<>".Sorry if i'm being a bit thick, and thanks in anticipation of your advice. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-28 : 06:03:33
|
| "why does the 1st SQL not return NULL values"Because NULL can not be compared with anything using normal relational operators. NULL <> 'A' will always yield NULL, not false.So the query wont be successful in retrieving records for NULL status.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-28 : 06:05:10
|
See on from Books OnLinequote:
<> (Not Equal To)Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.
KH |
|
|
|
itsmarkdavies
Starting Member
14 Posts |
Posted - 2007-02-28 : 06:08:22
|
| Thanks to both "harsh_athalye" and "khtan" for your replies - i learn something every day !. |
|
|
|
|
|
|
|
|
Using <> with NULL's in Column