comparing tuples in SQL Server with ternary boolean logic

I've a question regarding ternary boolean logic that affects how to implement comparison between polymorphic entities in our LINQ provider.

In SQL, if you join the Region table using a foreign key to Country :

SELECT * From Region r1, Region r2
WHERE r1.Country == r2.Country 

(note: the results are identical whether you use JOIN or WHERE synthax)

It will return the values where the condition is true, and not then the condition is false, or unknown (because some of the keys are null). So if we negate the condition:

SELECT * From Region r1, Region r2
WHERE r1.Country != r2.Country 

We get the values where the condition is true (now different keys) and we will skip the ones that have same keys, or the ones that have some null value because the condition return unknown again. Even if w开发者_运维百科e write it like this:

SELECT * From Region r1, Region r2
WHERE not(r1.Country == r2.Country) 

The unknown will be propagated so the nulls never appear for this simple condition. So far so good.

Now let's imagine that a Region can have a Country (for small European countries) or a State (for US, Russia, China...). If the region has a State it will have Country null and the other way around.

How could we join the pair [Country,State] so it have the same properties than before?:

SELECT * From Region r1, Region r2
WHERE r1.Country == r2.Country OR r1.State == r2.State

This expression will return true if it joins, unknown otherwise. We would like that it returns unknown only in the case that all the fields are null, otherwise if we negate:

SELECT * From Region r1, Region r2
WHERE not(r1.Country == r2.Country OR r1.State == r2.State)

It returns no rows!. If we try a more convoluted expression:

SELECT * From Region r1, Region r2
WHERE (r1.Country == r2.Country AND r1.Country IS NOT NULL AND r2.Country IS NOT NULL)  
   OR (r1.State   == r2.State   AND r1.State   IS NOT NULL AND r2.State   IS NOT NULL) 

Then it will return true when the pair matches, false otherwise, and never nothing. Then if we negate, it will return the values where all the rows are null, behaving differently than in the firs example.

So wich expression for comparing this pair will behave like an SQL equality?

• True when matches
• False when doesn't match
• Unknown when some operand is null.

Create a computed column:

Location AS COALESCE(Country, State)

, index it and compare as usual:

SELECT  *
FROM    Region r1
JOIN    Region r2
ON      r2.Location = r1.Location

Location will be NULL if both Country and State are NULL.

Update:

If Country and State can be inter-comparable, create an additional column showing which one is used:

LocationType AS CASE WHEN Country IS NOT NULL THEN 1 WHEN State IS NOT NULL THEN 2 END,
LocationId AS COALESCE(Country, State)

, index the two and use them in comparison:

SELECT  *
FROM    Region r1
JOIN    Region r2
ON      r2.LocationType = r1.LocationType
        AND r2.LocationID = r1.LocationID

Can you try this:

SELECT *
FROM Region r1
  JOIN Region r2
    ON ( r1.Country = r2.Country 
         AND r1.State IS NULL 
         AND r2.State IS NULL
       )
    OR ( r1.State = r2.State 
         AND r1.Country IS NULL 
         AND r2.Country IS NULL
       )