MySQL - Check if two values are equal or both NULL?

Question

If I want to compare two values, I can write:

SELECT * FROM table1
JOIN table2 ON table1.col = table2.col;

I've noticed that this does not work if both table1.col and table2.col are NULL. So my corrected query looks like this:

SELECT * FROM table1
JOIN table2 ON 
  (table1.col = table2.col)
  OR
  (table1.col IS NULL AND table2.col IS NULL);

Is this the correct way to compare two values? Is there a way to say two values are equal if they are both NULL?

Answer 1

The comparison NULL = NULL will return NULL, which is not true so the join won't succeed. You could use the NULL safe operator <=>

SELECT * FROM table1
JOIN table2 ON table1.col <=> table2.col;