Glad you're working on this. I was unaware of the nvl "gotcha",
because in our previous porting work the aD folk hadn't used it in
this way.
Subqueries in from will be included in PG 7.1, which is in "quiet
beta" at the moment, with a more visible public beta following soon.
As far as outer joins and foreign key references go, "NULL" is allowed
unless "not null" is specified. So the outer join may be necessary.
PG 7.1 will have outer joins ... ORDER BY on UNION queries don't work
correctly in PG 7.0 so when you expand the outer join query you need
to be aware of this.
It may be that photodb never inserts a row with a NULL value rather
than a key in the referenced table in the particular case you're
looking at, if true you can then drop the outer join entirely, as you
suggest doing.
