SQL & PL/SQL

Dear all,

I have been trying to debug a problematic query containing a left join on tables with NULL values in them (and yes, I do know that NULL values are not equal or unequal to anything;-). Situation is as follows:

There are two tables containing two similar columns (apart from other columns) which are named "resources" and "rawtriples". The first column in each of the tables is of type INTEGER, the other column is of type character varying(255). Example contents are:

resources:

namespace | localname
----------+----------
1 | 'type'
2 | 'domain'
2 | NULL

rawtriples:

predns | predLname
----------+----------
1 | 'type'
8 | NULL
9 | 'foo'

The character columns in these tables contain NULL values when empty strings are inserted.

Now I use the following query to retrieve all combinations of predns-predLname that are not yet present in the resources table:

SELECT DISTINCT rt.predNs, rt.predLname
FROM rawtriples rt
LEFT JOIN resources r
ON rt.predNs = r.namespace AND
(rt.predLname IS NULL AND r.localname IS NULL
OR rt.predLname = r.localname)
WHERE r.namespace IS NULL

I would expect this query to not return the first row of the rawtriples table, but this is not the case. All three rows are returned in the result set!

I would really appreciate some help with this issue. I've been wresting with it for far too long now and it's starting to give me headaches:-(

Thanks,

Arjohn Kampman