Using IS NULL in CASE Expression in WHERE Clause [closed]

Question

I have a WHERE clause that I want to use a CASE expression in. However, my CASE expression needs to check if a field IS NULL.

If the @UserRole variable value = 'Analyst', then the SupervisorApprovedBy column value must be NULL. Otherwise, I am saying return all data i.e. SupervisorApprovedBy = SupervisorApprovedBy.

What do I need to change to the below?

WHERE SupervisorApprovedBy = 
CASE 
    WHEN @UserRole = 'Analyst' THEN NULL
    ELSE SupervisorApprovedBy
END

I cannot use = for NULL. Tested and does not work.

I would want all rows returned including those where SupervisorApprovedBy IS NOT NULL.

Answer 1

I think you are making it harder than it should be.

If @UserRole is 'Analyst' then also SupervisorApprovedBy should be null? Else return everything?

WHERE (@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL ) 
OR (ISNULL(@UserRole, '') <> 'Analyst')

Answer 2

The problem is likely the comparison to NULL, as explained in David Spillett's answer above. When @UserRole = 'Analyst', the comparison SupervisorApprovedBy = NULL will give UNKNOWN (and the row won't pass the WHERE test).

You can rewrite with nested CASE expressions:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' THEN 
        CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
    WHEN SupervisorApprovedBy IS NOT NULL THEN 1
  END

Or a complicated CASE expression:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
    WHEN @UserRole = 'Analyst' THEN 0
    WHEN SupervisorApprovedBy IS NOT NULL THEN 1
  END

or with a bit more easy to understand AND / OR:

WHERE 
    @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
 OR @UserRole <> 'Analyst' AND SupervisorApprovedBy IS NOT NULL 
 OR @UserRole IS NULL      AND SupervisorApprovedBy IS NOT NULL 

---

Another issue is that SupervisorApprovedBy = SupervisorApprovedBy (and the equivalent SupervisorApprovedBy IS NOT NULL I used above) will not give you "all data". The rows where SupervisorApprovedBy is null will not be returned. If you do want them all, the conditions should be all adjusted:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' THEN 
        CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
    ELSE 1
  END


WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
    WHEN @UserRole = 'Analyst' THEN 0
    ELSE 1
  END


WHERE 
    @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
 OR @UserRole <> 'Analyst' 
 OR @UserRole IS NULL       

Answer 3

Simply WHEN <FieldOrVariable> IS NULL THEN <output>.

Or for the inverse: WHEN <FieldOrVariable> IS NOT NULL THEN <output>.

^{("WHEN NOT FieldOrVariable IS NULL THEN " would also work but the above reads better so is less likely to be misunderstood later)}

The same is true for other conditional clauses like those relating to IF and ON.

You can check if a field or variable is equal to NULL because all comparisons to NULL return NULL (which in a CASE or IF predicate is taken as meaning false), so WHEN <FieldOrVariable> = NULL THEN <output> and WHEN <FieldOrVariable> <> NULL THEN <output> will never match. NULL is essentially "unknown" so if you ask IF @value = NULL you are asking "is @value equal to something unknown" to which the only answer is "don't know". Even IF NULL = NULL PRINT 'NULL is NULL' will not work because it is asking "is something unknown equal to something else unknown", which is why we have the extra syntax IS NULL for dealing with circumstances where we need to know if a value is currently unknown.

Answer 4

How about this. Obviously replace 'X' with whatever value wouldn't ever exist.

WHERE ISNULL(SupervisorApprovedBy,'X') = 
CASE 
    WHEN @UserRole = 'Analyst' THEN 'X'
    ELSE SupervisorApprovedBy
END

If you want it to return NULL's all the time.

WHERE ISNULL(SupervisorApprovedBy,'X') IN ( 
CASE 
    WHEN @UserRole = 'Analyst' THEN 'X'
    ELSE SupervisorApprovedBy
END, 'X')

Or as @ypercubeᵀᴹ suggested, as another ISNULL

WHERE ISNULL(SupervisorApprovedBy,'X') = 
CASE 
    WHEN @UserRole = 'Analyst' THEN 'X'
    ELSE ISNULL(SupervisorApprovedBy,'X')
END