Emulating intersection in MySQL with self-join

As you may know, MySQL does not support INTERSECT operation. Hence, to get the intersection between two result sets in the same table, we need to use self-join. In this tutorial, I cover the emulating intersection in MySQL with self-join.

But what the heck is self-join?

Essentially, self-join is a simple join where both sides of the JOIN is the same table, here.

To understand it better, let’s go through a simple example. Assume we have a table called item_rating which holds ratings of users to items. Like below,

Description of columns:

user_id: Id of a user in the user table.
item_id: Id of an item in the item table.
rating: Rating that a user given to an item. 1 means thumbs up. 0 means thumbs down, and -1 means the user has not given any rating.

Let’s fill the table with some dummy data as follows,

And if we do a select query we get this results,

Assume we want to find a set of common items that rated by two users which essentially is finding the intersection of ratings on items between two users. In other words, we want to find a list of items that are rated by both user user_id=1, user user_id=2.

To achieve that in Microsoft SQL Server, one can use INTERSECT. But MySQL does not support it. Hence, instead we should rely on JOIN and construct a query like this:

As you can see we join table item_rating to itself but under two different WHERE condition. And as a result, we can get the intersection between user 1 and user 2.

Important note: even though MS SQL Server supports intersection, try to avoid it at all cost because of its inefficiency. Why? The intersection operates in such a way that at first two queries execute separately and results will be pulled from DB and then the intersection between two result sets will be calculated by the database engine. This is not much of concern for small tables, but when having gigantic result set, it will be super slow.

More database tutorials: https://geekyhacker.com/database/