Re: replacing a subquery with an outer join?

Chris Curvey <chris(at)chriscurvey(dot)com> writes:
> in an earlier thread, the following query was submitted:
>> SELECT COUNT(*) FROM q
>> WHERE NOT EXISTS (SELECT 1
>> FROM t AS t
>> WHERE t.mid = q.mid);

> and as part of his answer, David Johnson responded
> 3. Also, you are using a correlated sub-query instead of a LEFT OUTER JOIN

> This interests me. How would you rewrite the correlated subquery above
> using the outer join?

That NOT EXISTS idiom is a way of writing an "anti join", ie, select all
the q rows that lack joining rows in t. In SQL you can get the same
effect with

SELECT COUNT(*) FROM q LEFT JOIN t ON (t.mid = q.mid)
WHERE t.mid IS NULL;

as long as you assume the "=" operator is strict (that is, it can't
succeed for any null input). Then, if t.mid appears as null in some
row produced by the left join, you know that that wasn't a *real*
matching row for which the ON condition succeeded. Instead, it has
to be a null-extended row added by the LEFT JOIN operator for a q row
that had no matches. Therefore, this query counts all and only the
unmatched q rows, which is the same result as for the NOT EXISTS
formulation.

Since about 8.4, the Postgres planner recognizes either of these
idioms as anti-joins and will produce the same plan either way.
David's advice is applicable to pre-8.4 versions, in which the
plan produced for the NOT EXISTS formulation was often worse than
the one for the LEFT JOIN way.

regards, tom lane