CODEWITHSUNDEEP
sqlsql-serveruniqueidentifiernull-uniqueidentifier
user2444499
user2444499

Reputation: 787

TSQL Comparing 2 uniqueidentifier values not working as expected

I'm trying to compare 2 uniqueidentifier values as shown in the query below. However, if one value is null and one value isn't, the result is 'same'?! I'm sure that both values are uniqueidentifiers, and have also tried casting both values to uniqueidentifier to make absolutely sure. The 2 values being compared are coming from different databases with different collations. Does the collation make any difference? Any ideas would be appreciated.

select [result] = case when 
    [target].StaffID <> [source].StaffID then 'different' 
    else 'same' 
    end
from
    ...

If I replace the <> with an = the query then thinks that 2 null values don't match.

EDIT:

I used:

declare @empty uniqueidentifier
set @empty = '00000000-0000-0000-0000-000000000000'
... isnull(somevalue, @emtpy) <> isnull(othervalue, @empty) ...

Upvotes: 1

Views: 8392

Answers (2)

Chris
Chris

Reputation: 1539

null is more of an unknown, it's not really a value. Unfortunately SQL will tell you that null = null is false.

Basically you have to cast nulls to empty strings than you can compare. We use IFNULL(value, replacement) to do that...

http://msdn.microsoft.com/en-us/library/ms184325.aspx

Hope this helps.

select case when null = null then 'equal' else 'not equal' end

Above will be "not equal"

select case when ISNULL(null, '') = ISNULL(null, '') then 'equal' else 'not equal' end

This one will be "equal"

And finally your case...

select [result] = case when 
    ISNULL([target].StaffID, '') <> ISNULL([source].StaffID, '') then 'different' 
    else 'same' 
    end
from

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

NULL is neither equal to something nor equal to nothing. Generally you'd check for null values by comparing with IS NULL. For example,

somefield IS NULL

You could look into using COALESCE for what you're trying to do -- just make sure you use the same data types (in this case UniqueIdentifier):

...
  case 
    when coalesce(t.StaffID,'00000000-0000-0000-0000-000000000000') <> 
         coalesce(t2.StaffID,'00000000-0000-0000-0000-000000000000')
    then 'different' 
    else 'same' 
  end
...

http://sqlfiddle.com/#!3/181e9d/1

Upvotes: 2

Related Questions