Returning values in two rows that aren't equal

Question

I'm trying to return the results of a query where two columns columns are not equal to each other.

However, when I run the query the results don't seem to be consistent with the actual data.

As you can see below, I want to return rows where the RatePlanIDs are different.

RatePlans_Supplemental.PIDs could actually be null, but I never see null results. It also returns duplicates.

I've tried this with muliple queries and they all seem to do the same thing.

I've actually verified it's returning wrong data. It's like the query is inserting values if there's a null. Any idea why?

Update Billing_UnitUsage
Set SuppRateSuccess = 0
FROM         Billing_UnitUsage INNER JOIN
                      RatePlans_Supplemental ON Billing_UnitUsage.SupplementalRateID = RatePlans_Supplemental.UDC_ID AND 
                      NOT(Billing_UnitUsage.RatePlanID = RatePlans_Supplemental.PIDs)

Answer 1

Null is "unknown". Any function against unknown (including NOT) returns unknown. In order to get a row to return, your criteria must return TRUE.

Use IS NULL and IS NOT NULL to test for NULL.

Here's my analogy.

If there are two strangers in the room, and you ask, are their names the same? The answer is unknown.

If you ask, are their names not the same? The answer is unknown.

Without knowing their names, the answer, unfortunately, is always unknown.

Answer 2

I added the table aliases just to improve readability.

If you don't want to update SuppRateSuccess when PIDs is NULL:

Update bu
Set SuppRateSuccess = 0
    FROM Billing_UnitUsage bu
        INNER JOIN RatePlans_Supplemental rs
            ON bu.SupplementalRateID = rs.UDC_ID 
    WHERE bu.RatePlanID <> ISNULL(rs.PIDs, bu.RatePlanID)

If you do want to update SuppRateSuccess when PIDs is NULL (assumes RatePlanID<>0):

Update bu
Set SuppRateSuccess = 0
    FROM Billing_UnitUsage bu
        INNER JOIN RatePlans_Supplemental rs
            ON bu.SupplementalRateID = rs.UDC_ID 
    WHERE bu.RatePlanID <> ISNULL(rs.PIDs, -bu.RatePlanID)