Is it better to use INNER JOIN or EXISTS to find belonging to several in m2m relation?

Question

Given m2m relation: items-categories I have three tables:

• items,
• categories and
• items_categories that hold references to both

I want to find an item belonging to all given category sets:

Find Item  belonging to a category in [1,3,6]  and belonging to a category in [7,8,4]  and belonging to a category in [12,66,42] and ... 

There are two ways I can think of to accomplish this in mySQL.

OPTION A: INNER JOIN:

SELECT id from items  INNER JOIN category c1 ON (item.id = c1.item_id) INNER JOIN category c2 ON (item.id = c2.item_id) INNER JOIN category c3 ON (item.id = c3.item_id) ... WHERE c1.category_id IN [1,3,6] AND c2.category_id IN [7,8,4] AND c3.category_id IN [12,66,42] AND ...; 

OPTION B: EXISTS:

SELECT id from items WHERE EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [1,3,6] AND EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [7,8,4] AND EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [12,66,42] AND ...; 

Both options work. The question is: Which is the fastest / most optimal for large item table? Or is there an OPTION C I am missing?

Answer 1

OPTION A

JOIN has an advantage over EXIST , because it will more efficiently use the indices, especially in case of large tables

Answer 2

A JOIN is more efficient, generally speaking.

However, one thing to be aware of is that joins can produce duplicate rows in your output. For example, if item id was in category 1 and 3, the first JOIN would result in two rows for id 123. If item id 999 was in categories 1,3,7,8,12, and 66, you would get eight rows for 999 in your results (2*2*2).

Duplicate rows are something you need to be aware of and handle. In this case, you could just use select distinct id.... Eliminating duplicates can get more complicated with a complex query, though.