anoldmaninthesea
What is the point of point/objective of MySQL not having an Intersect or Except operator?
Also, what are the most common ways to simulate/substitute those operators in MySQL?
<>https://dbfiddle.uk/?rdbms=mysql_8.0&sample=sakila&fiddle=28df4594918e89177e376b12833d38f0
Top Answer
Truilus
MySQL has long tradition of not supporting modern (or advanced) SQL features. So not supporting EXCEPT or INTERSECT doesn't really surprise me.
However, you can switch to MariaDB which does support them (and in general seems to have more development momentum than MySQL) or go straight for PostgreSQL directly.
Answer #2
Jack Douglas
I can't find any stated reason why they are not supported — but I'd guess there is a reasonable chance a future version will add support.
We don't know for sure because Oracle does not provide a public roadmap — however the latest release (MySQL 8) did include some long-awaited features like subquery factoring.
> Also, what are the most common ways to simulate/substitute those operators in MySQL?
One common option is to use an outer join, and then filter:
<>https://dbfiddle.uk/?rdbms=mysql_8.0&sample=sakila&fiddle=e56630314c3ae7a250da3fd9c2e0a8b6