SQL Dangerous Misunderstanding value = null VS value is null

There should never have value = null statement

This is wrong. The correct comparison is is null

You can see it clearly in the example below

select 'foo' where null is null
-- Got 'foo' as query result

select 'foo' where null = null
-- Got 0 record
-- null = null is falsy

Why value = null is not work

Null is not a value so it cannot be compared to anything