| Author |
Topic |
|
Peter Dutch
Posting Yak Master
127 Posts |
 Posted - 2002-10-10 : 08:50:12
|
| I often use something like this:...WHERE SomeField = ISNULL(@theParameter,SomeField)to simulate optional parameters. (if the @theParameter is not passed, it compares the comlumns to itself).This works great, BUT not if the SomeField contains NULL.If SomeField contains NULL and @theParameter contains NULL it doesn't returns the record!!! I used the SET ANSI_NULLS ON but that didn't help. In my opinion it should return the record. Any ideas? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-10 : 08:54:51
|
| where isnull(somefield,'somefield is null') = coalesce(@theParameter,somefiled,'somefield is null')Jay White{0} |
 |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2002-10-10 : 08:57:46
|
| Yeah, thought of that, but thanks for your reply!I was hoping for a different solution, and/or an explanation on why this happens.NULL equals NULL if you ask me... |
|
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-10-10 : 09:03:48
|
| NULL means unknown hence if you try and compare unknown to unknown the result mut be unknownIf you really do want to match NULL with NULL then you could use..WHERE ISNULL(SomeField,'') = ISNULL(@theParameter,ISNULL(SomeField,''))ie. replace NULLs with an empty string which does match another empty string============================Chairman of The NULL Appreciation Society"Keep NULLs as NULL"Edited by - davidpardoe on 10/10/2002 09:04:15 |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2002-10-10 : 09:10:39
|
| Well the strange thing is, when you do (assuming SomeField contains NULL):SELECT * FROM MyTableWHERE SomeField IS NULLIt returns the record, but when you do (assuming Somefield and @Param contain NULL)SELECT * FROM MyTableWHERE SomeField = ISNULL(@Param,SomeField)it doesn't returns the record. That doesn't make sense to me. Why would it handle NULLs differently?? |
|
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-10-10 : 09:13:42
|
| When you use the following..SELECT * FROM MyTable WHERE SomeField IS NULL it is specifically checking if the field is null hence the condition is true.If you use..SELECT * FROM MyTable WHERE SomeField = ISNULL(@Param,SomeField) then your condition is effectively NULL = NULL which is FALSE due to my previous post (ie. it always unknown if unknown equals unknown!)============================Chairman of The NULL Appreciation Society"Keep NULLs as NULL" |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2002-10-10 : 09:16:57
|
| Ok, thanks for your explanation. Makes sense now.Guess I'll have to live with it..Thanks aigain! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 09:20:06
|
Don't know if this helps, it was an example in a SQL book, maybe one of Joe Celko's:A table holding data about people, name, height, weight, hair color. Two people, Joe and Ginny, have Null in the hair color column (unknown hair color) Does that mean they have the SAME color hair? How can you prove it?Basically, that's why a Null cannot equal another Null.  |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-10 : 09:24:18
|
| because null = null evaluates to null which is handled as false as does null = 1 and 1 = null.try these - the sort of thing you should try whenever you change version or service pack.I suspect the 'in' handling has changed at some point.select 1 where null = nullselect 1 where null <> nullselect 1 where not null = nullselect 1 where not null <> nullselect 1 where 1 = nullselect 1 where 1 <> nullselect 1 where not 1 = nullselect 1 where not 1 <> nullselect 1 where 1 in (1,null)select 1 where 1 not in (1,null)select 1 where 1 not in (2,null)select 1 where null in (1,null)select 1 where null not in (1,null)select 1 where null not in (2,null)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
ISNULL is not null??