Diff’in Oracle via SQL

I was working on querying different data rows contained in two tables in an Oracle databse. null value comparisons tend to bite me in the ass now and again and today was one of those days.

Let’s say we have two tables, A and B, they both have a mandatory ID, and a column x, which allows for null values. I originally had the following query:

select *
from A, B
where
A.ID = B.ID and
A.x != B.x

What I really needed:

select *
from A, B
where
A.ID = B.ID and
( A.x != B.x or
( A.x is null and B.x is not null ) or
( A.x is not null and B.x is null ) )

I’m not entirely satisfied with this. It seems like there should be a better way, but I haven’t discovered a more desirable one yet. If you add more columns into the mix it gets even more complex.

First post from my phone!

Leave a Reply

Your email address will not be published. Required fields are marked *