where null <> 'string' not retrieving, why? 5

[despierto]

Hope that title comes in.

I am comparing two fields: FieldA and FieldB. If my SQL looks like this:

SELECT pkKey, FieldA, FieldB
FROM Table1
WHERE FieldA <> FieldB

it only returns rows if FieldA and FieldB are not null. If one or the other is null, the record is not returned. Now, using ISNULL I modified the query to look like this:

SELECT pkKey, FieldA, FieldB
FROM Table1
WHERE ISNULL(FieldA, '') <> ISNULL(FieldB, '')

and the query does what I want. My question is: Why? Is this something to do with the collation I'm using? Is it a SQL Server default/bug/feature? Is there something I can do so that I don't have to use ISNULL? (Now that I've found it, I have a LOT of queries to rework)

 

[hneal98]

No, it is the Null. SQL treats nulls differently than everthing else. You can do a direct comparison like that with nulls. You second query is the intended version. Good job.

 

[SQLBill]

NULL does not mean Empty or zero. It means UNKNOWN.

So, lets say FieldA = 1

NULL might be equal to 1, but it also might not be equal to 1 - SQL Server just doesn't know since it's an UNKNOWN value.

This is one of the big confusions about NULL. A lot of people tend to think that NULL is the same as blank (an empty value). But it's not - it's easier to replace NULL with Unknown (or I don't know) in your mind.

Hope that helped explain NULL.

-SQLBill

 

[despierto]

Cool. Thanks guys, that does help it make more sense.

 

[hneal98]

Great explanation SQLBill.

 

[ESquared]

I realize that you want nulls to compare directly to nulls, but I thought it worth mentioning this alternate construction, which could be of use to someone at some time:

SELECT pkKey, FieldA, FieldB
FROM Table1
WHERE NOT (FieldA = FieldB)

This will show all records where FieldA is different from FieldB, or where either is null.

It is equivalent to the following syntax:

SELECT pkKey, FieldA, FieldB
FROM Table1
WHERE FieldA <> FieldB
   OR FieldA IS NULL
   OR FieldB IS NULL

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[hneal98]

That is true becuase you are negating the equality and therefore the null is included in that instance.

Good points.

 

[TheDrider]

But won't that last statement also return records where BOTH are NULL? (Functionally equal, but technically NULL does not equal NULL)

In order to exclude records with both sides NULL, you would still have to use IsNULL.

 

[ESquared]

thedrider,

Yes, it will return fields where either field is null, which includes cases where both fields are null. However, I wasn't suggesting it as a replacement for the functionality despierto was looking for.

But it just occurs to me that you could use this construction:

SELECT pkKey, FieldA, FieldB
FROM Table1
WHERE NOT (FieldA = FieldB)
   AND NOT (FieldA IS NULL AND FieldB IS NULL)

This would be equivalent to the ISNULL() form and could conceivably be faster as it uses direct comparisons and not any function such as ISNULL. It would take testing to find out. Maybe because it's slightly more complex it would be worse.



-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[despierto]

Never imagined this would go this far, but here we go:

pk Field1 Field2
 1      1 <null>
 2 <null>      1 
 3 <null> <null>
 4      1      1

SELECT pk FROM Table1
WHERE (Field1=Field2)
returns: 4

SELECT pk FROM Table1
WHERE NOT (Field1=Field2)
returns: no records

as thedrider said, null doesn't equal null. The other suggestion by ESquared,

SELECT pk, Field1, Field2
FROM Table1
WHERE Field1 <> Field2
   OR Field1 IS NULL 
   OR Field2 IS NULL

doesn't work because it returns row 3. To accomplish what I want, basically we have to modify that to:

SELECT pk, Field1, Field2
FROM Table1
WHERE Field1 <> Field2
   OR Field1 IS NULL AND Field2 IS NOT NULL
   OR Field2 IS NULL AND Field1 IS NOT NULL

or use what I wrote in the first post... Becomes a matter of opinion and preference I guess. But at least we really learned a lot about nothing today!