On 12-11-2011 19:13, Darren Duncan wrote: > 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
It is not on the list of unsupported features: (http://www.sqlite.org/omitted.html) But SQlite does not have 'WITH' implemented -- Luuk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
