codejeff.com

Archive for March, 2010

Diff’in Oracle via SQL

Tuesday, March 30th, 2010

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!