Re: [sqlite] "where not exists (union-select)" fails on 2nd where

[Markus Weissmann]

On 24.08.2005, at 03:21, Kurt Welgehausen wrote:

SELECT * FROM PRIM AS P
WHERE NOT EXISTS
(
SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID
UNION
SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID
);

I looks like correct SQL according to the SQLite docs,
but I don't understand why you coded the select that
way. You should get the same result from

   select * from prim as p
   where not exists (select refid from ref1
                     where refid=p.id) and
         not exists (select refid from ref2
                     where refid=p.id)

I would guess that this form would be more efficient
because if the first test fails, the second sub-select
should not be executed, perhaps saving a complete scan
of ref2.

perfect - thanks a lot!


-Markus

---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/