How to JOIN ON when both tables contain NULL

join, mysql, null, sql

Solution

You could use the MySQL null-safe comparison operator `<=>`:

SELECT    t1.column1, t1.column2, t2.column1, t2.column2 
FROM      table1 t1
LEFT JOIN table2 t2 
       ON t1.column1 = t2.column1 AND t1.column2 <=> t2.column2

Problem

I am doing an outer join of 2 tables on 2 columns. The join should happen if table1.column1=table2.column1 and table1.column2=table2.column2. Since column2 is allowed to be contain null, the join fails whenever the value is null, since null is not equal to null (only a computer scientist could love that). The workaround I came up with is: ``` select table1.column1,table1.colunn1,table2.column1,table2.column2 from table1 left join table2 on table1.column1=table2.column1 and if(table1.column2 is null,table2.column2 is null, table1.column2=table2.column2) ``` This works correctly, but there must be a better way?

Original source