| Field1 |
| (null) |
| abc |
| xyz |
Here's a test for you. What will the following query return?
select * from table1 where field1 like 'a%'
If you answered abc you are correct - congratulations!
Ok now here is the same query slightly modified - what will it return:
select * from table1 where not (field1 like 'a%')
Surprisingly (to me) it doesn't return (null) and xyz, it only returns xyz. If anyone knows why can you please explain in the comments?
2 comments:
I don't profess to be an expert "SQL guy", but I would guess it's got something to do with the fact that null never "equals" anything. The only way to compare a null value meaningfully is with the is null/is not null operators.
Try this:
select 1 where null = null;
followed by this:
select 1 where null is null;
to see what I mean.
Because it uses ternary logic (see http://en.wikipedia.org/wiki/Three-valued_logic)
Post a Comment