Support EXCEPT and INTERSECT

[Former user]

SQLAlchemy currently just supports UNION as a compound-query.

Adding naïve support for EXCEPT and INTERSECT is done in the attached patch, but you'll probably not get the results you expect if you start nesting the compound-queries as no parenthesises are added. (This is not a problem as long as just UNION is supported)

Some simple (non-exhaustive) testing suggests using parens=True works in PostgreSQL, but SQLite will just give you syntax errors. (Because in PostgreSQL you can just slap paranthesises around queries if you feel like it -- for instance, (select 1) works in PostgreSQL, but you need select (select 1) in SQLite. Since parens isn't documented at all, I guess you're not really supposed to do that, though. :-)

~Alex Brasetvik alex.brasetvik@com

[Former user]

Support for EXCEPT (ALL) and INTERSECT (ALL).

[Mike Bayer]

this is implemented in changeset:2111. you have two options for a DB-independent nesting pattern that works in both pg and sqlite:

using parens=True. the outer alias makes sqlite happy.

   intersect(
       union(x, y, z, parens=True),
       q
   ).alias('foo').select()

using nested select()s (which could normally be like union(x,y,z).select()), but also adding an alias to make postgres happy.

   intersect(
       union(x, y, z).alias('foo').select()
       q
   )

[Mike Bayer]

make that:

   intersect(
       union(x, y, z).alias('foo').select(),
       q
   )

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)