How to join ON when both tables contain NULL

Question

I am doing an outer join of two tables by 2 columns. A join should occur if table1.column1 = table2.column1 and table1.column2 = table2.column2. Since column2 is allowed to contain null, the union fails when the value is null, since null is not null (only a computer scientist can love it).

The workaround I came across 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 should there be a better way?

Answer 1

You can use MySQL null 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 

Answer 2

I would do LEFT JOIN table2 ON table1.column1 = table2.column1 OR (table1.column1 IS NULL AND table2.column1 IS NULL) . I don’t know for sure whether this will work or not.

(By the way, nulls are not values.)