2009-09-08

Finding overlapping ranges

The problem of finding overlaped ranges in table(id,low,upper) looks trivial. I tried to find elegant solution, so I decided to describe non-overlapping set and then invert logical expression.

                 ------
          -----          -----
NOT(t1.low >= t2.upper  or t1.upper <= t2.low)

Then I want to exclude equal ranges

NOT(t1.low >= t2.upper  or t1.upper <= t2.low or (t1.low = t2.low and t1.upper = t2.upper  )

We don't want to do extra matches, so

NOT(t1.low >= t2.upper  or t1.upper <= t2.low or (t1.low = t2.low and t1.upper = t2.upper  ) and t1.id > t2.id