具有特定基数的映射（联结）表之间的联接

我有一个关于执行特定联接的最有效方法的简单问题。

就拿这三张表来说，为了保护无辜，已经改了实名：

表：动物

animal_id   name   ...
======================
1           bunny
2           bear
3           cat
4           mouse
  

表：标签

tag_id     tag
==================
1          fluffy
2          brown
3          cute
4          small
  

映射表：animal_tag

animal_id   tag_id
==================
1           1
1           2
1           3
2           2
3           4
4           2
  

我想找到所有标记为“的动物”fluffy', 'brown', and 'cute'。也就是说动物必须被标记与所有三个。实际上，所需标签的数量可能会有所不同，但与本讨论无关。这是我提出的查询：

SELECT * FROM animal
JOIN (
      SELECT at.animal_id FROM animal_tag at
      WHERE at.tag_id IN (
                          SELECT tg.tag_id FROM tag tg
                          WHERE tg.tag='fluffy' OR tg.tag='brown' OR tg.tag='cute'
                          )
      GROUP BY at.animal_id HAVING COUNT(at.tag_id)=3
      ) AS jt
ON animal.animal_id=jt.animal_id

在包含数千个“动物”和数百个“标签”的表上，此查询的执行时间相当......数十毫秒。然而，当我查看查询计划（Apache Derby 是数据库）时，优化器的估计成本相当高（9945.12）并且计划相当广泛。对于这种“简单”的查询，我通常尝试获取估计成本为一位数或两位数的查询计划。

所以我的问题是，有没有更好的方法来执行这个查询？看起来像是一个简单的查询，但我一直很难想出更好的方法。

您可以使用创建临时表声明全局临时表 http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj33.html然后进行 INNER JOIN 以消除“W​​HERE IN”。使用基于集合的联接通常比必须对每一行进行评估的Where语句更有效。