Re: Optomizing left outer joins

Lorraine,

> Perhaps I should have said that not only is the column optional, the entire
> row is optional.  I am trying to join a master table (contains insurance
claim
> information) to an error table.  If the claim in the master table doesn't
have
> any errors, there won't be a row for it in the error table.  When I select
> columns from both tables, I only get the errored claims.  That's why I was
> trying to use the outer join.

Well, an outer join is appropriate in that case.   However, a single outer
join shouldn't be killing your queries -- in the example I gave, I eliminated
19 outer joins to boost the client's performance.

I think the lack of indexes is your real albatross.

> I like your advice about telling the customer about the workaround.  I'll
> definitely do that!  The workaround is pretty awkward: two select statements
> joined by "union all," then some subselects to pick out the unmatched
records.
>  It returns the correct data and it's faster than the left join, but it
takes
> a lot more code.  Anyway, I guess I'm stuck with it.

You probably are.   But make sure you tell the client that the lack of indexes
is killing performance; even if it doesn't get fixed, your keister is
covered.

--
-Josh BerkusAglio Database SolutionsSan Francisco