not in and null values

I was joining some tables with a versioning table and needed a way to filter out some specific versions. So I was thinking of the NOT IN check with the versions that shouldn’t show up. But not everything is present in versioning so also needed to make it an OUTER JOIN. I quickly set up a query and discovered id didn’t show the unversioned objects.

SELECT A.a, A.b, A.c, B.a, B.b, B.c
  FROM A
    LEFT OUTER JOIN versioning V ON V.obj_id=A.obj_id
    INNER JOIN B ON A.obj_id=B.obj_id
  WHERE V.version_id NOT IN (1,2)

I thought my OUTER JOIN was not working. After stripping down the query I realised the JOIN was fine, only the NOT IN doesn’t include NULL values :D. This is the fix:

SELECT A.a, A.b, A.c, B.a, B.b, B.c
  FROM A
    LEFT OUTER JOIN versioning V ON V.obj_id=A.obj_id
    INNER JOIN B ON A.obj_id=B.obj_id
  WHERE V.version_id NOT IN (1,2) OR V.version_id IS NULL

use columnames

Also I now saw in real how much faster a query is when you provide columnames instead of using the * symbol. It took me a join of 5 tables to realise 🙂

oracle dialect for outer join

When joining in oracle you can also use the (+) symbol to indicate an outer join. An example:

SELECT A.a, A.b, B.a
  FROM A, B
  WHERE A.id=B.id (+)

In this example the B.id can be null.