>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> SELECT ... FROM
> "Case" "C"
> LEFT OUTER JOIN "CaseDispo" "CD"
> ON ("CD"."caseNo" = "C"."caseNo") AND ("CD"."countyNo" =
"C"."countyNo")
> AND (NOT (EXISTS (SELECT 1 FROM "CaseDispo" "CD2"
> WHERE ("CD2"."caseNo" = "CD"."caseNo")
> AND ("CD2"."countyNo" = "CD"."countyNo")
> AND ("CD2"."dispoDate" >
"CD"."dispoDate"))))
> WHERE some-clause-that-selects-just-a-few-C-rows
>
> that is, the EXISTS clause is part of the ON condition of an outer
join.
> If it referred to any variables of the left side of the upper join
> (ie, "C" here) then we couldn't convert it to a separate join at
all.
> I wondered if anyone had any comments
The only thing that comes to mind for me that seems possibly helpful
is that we have typically considered it obvious that in the context of
the NOT EXISTS clause we have already established that ("CD"."caseNo"
= "C"."caseNo") AND ("CD"."countyNo" = "C"."countyNo") and have not
been at all consistent about whether we used C or CD to compare to
CD2. Our operating assumption has been that it didn't matter in that
context.
-Kevin