SQL Tricks: The Not-Equals Join [Field Notes]

The Problem

I needed to pull back a list of dates in a certain range for each employee, but didn’t have a common join criteria, as I wanted all the dates but they did not come from a table.

The Solution

I knew that the dates would never equal the employee name, so in this case, I could actually use a “not-equal” join to connect the two. I always forget about this one, but it’s handy.

I did something along the following (edited for brevity):

select * from (...) ActiveLabor
    left outer join (...)DateRange
    on (activelabor.laborcode != to_char(DateRange.DateItem))

This allows the left outer join only if the items are not equal (which we happen to know will always be the case).

The end result? As I expected – the full list of dates showed up for each employee, just as I’d hoped.

A Note: Because I was comparing a date to a string, I had to cast the date as a varchar2 in the join statement. The date value itself still comes back as a date, but during the comparison we need to make sure we’re giving Oracle an apples-to-apples comparison.