MINUS and INTERSECT in MySQL
MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:
Sample query with the MINUS operator:
|
1 2 3 |
SELECT x, y FROM table_a MINUS SELECT x, y FROM table_b; |
In MySQL:
|
1 2 3 4 |
SELECT a.x, a.y FROM table_a a LEFT JOIN table_b b ON a.x = b.x AND a.y = b.y WHERE b.x IS NULL; |