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 replies on “Intersection and Set-Difference in MySQL (A workaround for EXCEPT)”

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 😀

LikeLike

Hi Gaby,Thanks for visiting my blog, I’m glad my post was helpful to atleast someone out there…:)

LikeLike

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.

LikeLike

hey thanks alot for this, just what i was looking for 😛

LikeLike

Too long query for big data 😦

LikeLike