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.