How to JOIN ON when both tables contain NULL

Question

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?

Answer 1

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