Bug #26477 EXCEPT Keyword To Complement UNION
Submitted: 19 Feb 2007 14:51 Modified: 1 Mar 2007 7:31
Reporter: Dean Swift Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.x OS:Any (all)
Assigned to: CPU Architecture:Any
Tags: bfsm_2007_03_01, IN, left join, query plan, UNION

[19 Feb 2007 14:51] Dean Swift
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.
[20 Feb 2007 7:16] Valeriy Kravchuk
Thank you for a reasonable feature request.