Alexandre Courbot wrote:
Hi everybody,
Here is a simplified version of the statement I try to run (let a, b,
and m be tables with only one column named c containing integers):
SELECT * FROM m WHERE
c IN (SELECT * FROM a) OR
c IN (SELECT * FROM b)
AND (NOT c IN (SELECT * FROM a)
OR c IN (SELECT * FROM b));
I know this could be done more easily, but I have to keep this
structure (i.e. cannot JOIN for instance). My question is, how could I
avoid repeating the subqueries after the AND NOT, since they will
return the same set as the previous ones? In case I do not replace
them, would SQLite be able to optimize and only run them once?
Thanks,
Alex.
The SQL "WITH" or "common table expressions" feature is useful here; with that
you can factor out subqueries as named common expressions, and say something
like this (from my head, exact syntax may be wrong):
WITH (SELECT * FROM a) AS sfa,
(SELECT * FROM b) AS sfb :
SELECT * FROM m WHERE
c IN sfa OR
c IN sfb
AND (NOT c IN sfa
OR c IN sfb));
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
