Not all database platforms support INTERSECT and EXCEPT (MINUS in Oracle), and MySQL is one of them. Let’s try to solve two simple related problems:
- We want department numbers that appear in both Departments and Employees tables. In other words, we’re looking for all departments with at least one employee. This is a great candidate for INTERSECT, because we want numbers that exist in both tables.
- We also want all department numbers that do not appear in Employees. In other words, we’re looking for departments with no employees. This is a great candidate for EXCEPT, because we want numbers that exist in only one table.