Add the correct condition in WHERE statement using CASE

Question

It's been months since I explore SQL Server but I am still new to this field. I need to output rows that will return ISBLANK = 0 or ISBLANK IS NULL. I am using CASE statement.

Expected output:

My code:

SELECT UD.FID
    , UD.UID
    , CBH.BID
    , FBA.ISBLANK
FROM TEMP_UD UD
INNER JOIN TBL_CBH CBH
    ON UD.FID = CBH.FID
INNER JOIN TBL_UDF UDF
    ON UDF.FID = UDF.FID
LEFT JOIN TBL_FBA FBA
    ON FBA.FID = CBH.FID
    AND FBA.BID = CBH.BID
WHERE
    (
        CASE 
            WHEN UDF.COND = 10 AND UDF.UID = 12 AND FBA.ISBLANK != 1
                THEN 0
        END = FBA.ISBLANK
    )

Current output:

FID UID BID ISBLANK
-------------------
1   12  1   0
1   12  1   0

Sample code:

https://onecompiler.com/mysql/3y29djktg

Answer 1

Try:

CASE WHEN UDF.COND = 10
       AND UDF.UID = 12
       AND FBA.ISBLANK = 1
      THEN 1
      ELSE 0
      END = 1

This will/should return the rows where ISBLANK 1 1 does not apply.

I am wondering why you are not using the conditions directly in the WHERE, like this:

WHERE UDF.COND = 10
AND UDF.UID = 12
AND ( FBA.ISBLANK IS NULL OR FBA.ISBLANK = 0 )

If any optimizations can be made based on the columns that are used, they cannot be made if they are used in a CASE expression.