What will be the result of the query below? Explain your answer and provide a version that behaves correctly.

Question

select case when null = null then 'Yup' else 'Nope' end as Result;

Answer 1

This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.

Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows:

select case when null is null then 'Yup' else 'Nope' end as Result;