Description:
Recently, I have encountered a client who uses an AJAX interface to perform e-mail list management. The interface generates relatively complicated queries which often involve the union and intersection of millions of users based on multiple criteria, such as language, domain, subscription status and dozens of campaign specific fields. Occasionally, the interface generates a query plan which executes slowly and invariably, this involves self LEFT JOINs. However, with some adaptation to the implementation of UNION and UNION ALL it should be relatively trivial to implement the SQL standard EXCEPT keyword which is much closer to the intended query and avoids potential problems with large self joins.
For compatibility with other implementations of this feature, it may be neccesary to also implement an alias MINUS. EXCLUDE and INTERSECT are also logical aliases.
How to repeat:
mysql> (SELECT * FROM `host` WHERE `name` LIKE '%foo%') EXCLUDE (SELECT * FROM `host` WHERE `name` LIKE '%foobar%');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCLUDE (SELECT * FROM `host` WHERE `name` LIKE '%foobar%')' at line 1
Suggested fix:
Extend UNION and UNION ALL implementation to implement EXCEPT. It is not neccesary to implement a reverse EXCEPT.
