CODEWITHSUNDEEP
sqloracle-databasenull
Alana Storm
Alana Storm

Reputation: 166106

Oracle Equality that Includes Null

Is there an Oracle equality operator that includes NULL values? Or am I stuck doing a two part "(a=b) OR (a is null and b is null).

I'm trying to self join a table in Oracle

SELECT t1.column, t2.column FROM TABLE t1
LEFT JOIN TABLE2 t2
ON t1.foo = t2.foo

My query works except in cases where t1 and t2 are null. And there's lots of these cases. I can fix this with the following

SELECT t1.column, t2.column FROM TABLE t1
LEFT JOIN TABLE2 t2
ON ( (t1.foo = t2.foo) OR (t1.foo is null AND t2. is null) )

That is, I explicitly check for the double null case in addition to the equality case.

This makes me feel like it's 1997. Is there a better way to do this? A special equality operator or function that tests for nulls as well?

Upvotes: 2

Views: 208

Answers (1)

sgeddes
sgeddes

Reputation: 62851

You can use coalesce in your join:

SELECT t1.column, t2.column 
FROM TABLE t1
    LEFT JOIN TABLE2 t2
        ON coalesce(t1.foo,0) = coalesce(t2.foo,0)

The second value of the coalesce depends on your datatype. Choose a value which won't already exist in the database.

Upvotes: 3

Related Questions