Intersect and Except
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.
Table of Contents
Solution with Joins
There is already a built-in join between these tables, on the department number. INTERSECT may be expressed here as an all inclusive join on both sides (our left image below): give me numbers where they are All in common (i.e they exist in both tables). To limit it to all unique numbers, click also on Distinct.
To EXCLUDE all departments with employees, Query Xtractor adds an interesting flavor: we have a None from emp join type choice that will do just that. The interesting fact is this translates not only in a required SQL outer join, but adds also a transparent required check for a NULL value. This is an exclusive join not directly supported by any platform. You see its notation, with an empty little circle, on the right image below:
Solution with Lists
INTERSECT means also that dept.DEPTNO values are found as well within the emp.DEPTNO values. And this could be easily expressed as a Where condition with the Is within field values operator (left image below).
EXCEPT is just the opposite (right image): we want dept.DEPTNO values that are not found within any of the emp.DEPTNO values. Output SQL generated an inline subquery, but you didn’t have to worry about this implementation detail:
Solution with All/Any
We reformulate again… INTERSECT is when each dept.DEPTNO value matches (i.e. equality condition!) ANY emp.DEPTNO value (left image below). EXCEPT (right image) is when each dept.DEPTNO value has no match (inequality condition!) within ALL emp.DEPTNO values.
Be aware that All and Any do not appear in the list of Aggregates unless you specify a comparison operator and choose a field first! Once again, output SQL generated an inline subquery, but you didn’t have to worry about this implementation detail:
Solution with … Intersect/Except!
Have we said MySQL (as many other platforms) does not support INTERSECT and EXCEPT? Not in our backyard. What it is not natively implemented, we try to emulate. All our databases support Intersect and Except, emulated or not! That’s transparent for you, if you don’t mind looking at the generated SQL query.
For our problems here, it simply looks trivial and that’s another way to solve them. Generated SQL may emulate this with IN or EXISTS operators, but you don’t have to care about it:
Conclusion
Query Xtractor offers more than one way to solve a problem. While our generic and vendor-independent user interface may look simple, what’s generated as SQL could be sometimes pretty complex and not obvious at all. When there is no native support already for a feature, we try to emulate it with SQL expressions and different constructs.
