Oracle SQL CASE WHEN IS NULL

Question

I am learning Oracle SQL :)

1.

CASE s.COURSE_SCHEDULED_ID
    WHEN IS NULL THEN 'false'
    ELSE 'true'
END AS "Is scheduled?"

CASE 
    WHEN s.COURSE_SCHEDULED_ID IS NULL THEN 'false'
    ELSE 'true'
END AS "Is scheduled?"

Why 1 is not working?

Answer 1

Your first CASE expression won't even compile, but if we make a small change then it will:

CASE s.COURSE_SCHEDULED_ID
    WHEN NULL THEN 'false'
    ELSE 'true'
END AS "Is scheduled?"

This will be evaluated as this:

CASE WHEN s.COURSE_SCHEDULED_ID = NULL
     THEN 'false'
     ELSE 'true'
END AS "Is scheduled?"

Note very carefully that the COURSE_SCHEDULED_ID is being compared to NULL using the equality operator. This won't work as expected with NULL. Instead, for NULL checks the second verbose version always must be used as it allows IS NULL and IS NOT NULL checks.