使用INNER JOIN或EXISTS在m2m关系中查找属于几个的更好吗? [英] Is it better to use INNER JOIN or EXISTS to find belonging to several in m2m relation?

问题描述

给出m2m关系:项类别我有三个表:

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

• 项目，
• 类别和
• items_categories ，其中包含对这两者的引用

• 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 ...

我可以考虑通过两种方法在mySQL中完成此操作.

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

选项A:内部联接:

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
...;

选项B:存在:

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
...;

两个选项均有效.问题是:哪个是大型项目表中最快/最合适的?还是我想念的OPTION C?

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?

推荐答案

选项A

JOIN具有优于EXIST的优点，因为它将更有效地使用索引，尤其是在大型表的情况下

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

这篇关于使用INNER JOIN或EXISTS在m2m关系中查找属于几个的更好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！