Simon Riggs <simon@xxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
