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

many-to-many, mysql, performance, sql

Solution

OPTION A

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

Problem

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?

Original source