Thank you for your prompt reply, I appreciate your insight on this.
> So the COALESCE has to be evaluated below the outer join, which means
> that the view can't be "flattened" into the upper query.
> ...
> So the long and the short of it is that the COALESCE acts as an
> optimization fence in the presence of outer joins. We've seen this
> before and there are some rough ideas about fixing it.
You may already have this rough idea somewhere, but it seems to me that
the view could be flattened into the upper query as long as the join
predicates don't depend on coalesced columns. In the examples I sent,
even if the COALESCE is evaluated at the very end of the query, the
merge join (on the id columns) would still be correct.
--Chris