Re: Missing numbers

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Wed, 2005-06-01 at 00:27 -0400, Alvaro Herrera wrote:
>>> SELECT g.num
>>> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>>> (SELECT max(doc_numero) FROM bdocs)) AS g(num)
>>> LEFT JOIN bdocs ON bdocs.doc_numero = g.num
>>> WHERE bdocs.doc_numero IS NULL

> I wonder what the SQL spec should happen in this case? It depends upon
> whether the NOT NULLs are excluded before or after the join takes
> place.

The spec says that WHERE is logically applied after the join. In some
cases it is possible to push the condition down to occur before the join
without changing the results ... but not in the above case.

> Either way, I still prefer my phrasing of the SQL, which seems clearer,
> but I would say that wouldn't I?

Maybe so. The outer-join-and-test-for-null is a pretty common idiom
though, so I'd expect experienced SQL programmers to recognize it on
sight.

regards, tom lane