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.

Published by Nimal

I am a grounded nomad. I love a bit of art and science. #thappillai

Join the Conversation

5 Comments

  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 comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.