CODEWITHSUNDEEP
sqljoinnullcase
igorjrr
igorjrr

Reputation: 892

SQL: mix join with case and NULL

Pretty simple, what's the best way to fix the NULL filtering below, since the = operand doesn't work with NULL?

The Key2, when Data1=-1, is trying to do a Key2=NULL which is not selecting NULL values.

LEFT JOIN myReferenceTable
    on myReferenceTable.Key1 = myDataTable.FKey1
    and myReferenceTable.Key2 =
    CASE
        WHEN myDataTable.Data1 = -1 THEN NULL
        ELSE myDataTable.Data3 - myDataTable.Data4 
END

Thanks!

Upvotes: 0

Views: 66

Answers (3)

Andy_in_Van
Andy_in_Van

Reputation: 331

My understanding is that you are trying to join the ReferenceTable to the DataTable on Key1 & Key2, (Key1 is a direct match, and Key2 matches to the difference between Data3 and Data4) but you want to explicitly filter out rows where Data1 = -1. It seems that you may be wanting to also return a NULL if Data1 = -1, but what gets returned will depend on what's in your SELECT statement. We're talking about the Join here, and you're doing a Left Join which tells me you want all rows from myRerenceTable returned, whether of not there are matches in myDataTable. Having said that, I would write the Join as:

    LEFT JOIN myReferenceTable
        on myReferenceTable.Key1 = myDataTable.FKey1
        and myDataTable.Data1 <> -1
        AND myReferenceTable.Key2 = myDataTable.Data3 - myDataTable.Data4

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

If you're using MySQL, you need to use the "null-safe equal" operator.

From the manual:

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

Upvotes: 0

Becuzz
Becuzz

Reputation: 6866

Swap out your case statement using ANDs / ORs

LEFT JOIN myReferenceTable
    on myReferenceTable.Key1 = myDataTable.FKey1
    and ((myDataTable.Data1 = -1 AND myReferenceTable.Key2 IS NULL) 
           OR (myDataTable.Data1 != -1 AND myReferenceTable.Key2 = myDataTable.Data3 - myDataTable.Data4))

Upvotes: 2

Related Questions