Intersection and Set-Difference in MySQL (A workaround for EXCEPT)

The INTERSECT and EXCEPT operators of SQL are not supported in MySQL. So there there seems a workaround, you use a join with all corresponding attributes equated in place of an intersection.

For instance, to get the intersection of R(a,b) and S(a,b), write:
    SELECT DISTINCT *
    FROM R
    WHERE EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b);

To get the set difference, here is a similar approach using a sub query:
    SELECT DISTINCT *
    FROM R
    WHERE NOT EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b);

Note that both these expressions eliminate duplicates, but that is in accordance with the SQL standard.

So for example for something like this using EXCEPT,
    SELECT T.data_id from table T
    WHERE T.keyword = 'google'
    EXCEPT
    SELECT T2.data_id from table T2
    WHERE T2.keyword = 'yahoo'

Would become,
    SELECT T.data_id
    FROM table T
    WHERE T.keyword = 'google '
    AND NOT EXISTS (
        SELECT T2.data_id
        FROM table T2
        WHERE T2.keyword = 'yahoo '
        AND T2.data_id = T1.data_id
    )

This is just a small workaround that works for me, and I had to spend sometime finding this solution. So this is just for a reference of mine sometime later, but if its useful to anyone of you that would be great.

5 thoughts on “Intersection and Set-Difference in MySQL (A workaround for EXCEPT)”

  1. Thanks so much for posting this! I’m new to relational database terms (union, intersect, etc) so I’ve been trying out new things with MySQL (which I’ve used a few years now) but I couldn’t work out how to do intersection & difference, but this worked straight off. Thanks again 😀

  2. This doesn’t work properly if the column values are both null. In this case the records would not match. Should use the operator instead of =. This is a null safe equals. Example:

    AND T2.data_id T1.data_id

    Then if both column data are null it will return true.

Leave a Reply