Comparison of two columns when they can both be null

user_579208 0
Viewed 37

I want to compare two columns. They must be equal or they both must be null.

(t1.id is null and t2.id is null) or (t1.id = t2.id)

They are both indexed but MySQL does not use this index probably due to null values. Is there a way to do this where we can benefit from the indexes?

mysql
1 Answers

The SQL Standard (SQL:1999 - Section 8.13) defines the operator IS [NOT] DISTINCT FROM for this purpose. It's implemented in MySQL as <=>.

For example:

with
t as (select 123 as a, 123 as b
      union all select 123, null
      union all select null, 123
      union all select null, null
)
select *, a <=> b from t;

Result:

 a    b    a <=> b 
 ---- ---- ------- 
 123  123  1       
 123  null 0       
 null 123  0       
 null null 1
user_64361910
Related