What happens if I compare a column with itself and it is NULL ? Is this similar to floating point values where x == x only is false if the value is a NaN ?

CodePudding user response：

It depends on the comparison you do with itself.

If you do

WHERE Col = Col

any rows where Col IS NULL will have the WHERE clause evaluate to UNKNOWN (rather than TRUE or FALSE) and the row will not be returned.

So WHERE Col = Col is equivalent to WHERE Col IS NOT NULL

If you do (not available in all RDBMS but standard SQL)

WHERE Col IS NOT DISTINCT FROM Col

Then this will evaluate to true

CodePudding user response：

The comparsion with = is not NULL safe, which means the result is UNKNOWN

So you should check your database for NULL safe comparisons

On MySQL it is <=>

Postgres uses col1 IS DISTINCT FROM col2

SQL Server hasn't one till Version 2022, you can uses the last option in query. Since Version 2022 it also supports col1 IS DISTINCT FROM col2

SELECT col1 = col2,col1 <=> col2, col1 = col2 OR (col1 IS NULL AND col2 IS NULL) FROM tab1

fiddle