SQL:多对多表AND查询

我假设你想找到那些在 任何 关于部门,这是一个容易得多的查询。

SELECT EmpID
FROM mytable t1
JOIN mytable t2 ON t1.EmpID = t2.EmpID AND t2.DeptID = 2
JOIN mytable t3 ON t2.EmpID = t3.EmpID AND t3.DeptID = 3
WHERE DeptID = 1

我将抢先提出使用聚合的不可避免的建议:

SELECT EmpID
FROM mytable
WHERE DeptID IN (1,2,3)
GROUP BY EmpID
HAVING COUNT(1) = 3

抵制这种诱惑。它是 显著地 更慢的。类似的情况出现在 SQL Statement - “Join” Vs “Group By and Having” 而第二个版本,在那一刻,大约是 慢二十倍 .

我还建议你看看 Database Development Mistakes Made by AppDevelopers .

SELECT EmpID, COUNT(*) AS NumDepts
FROM thetable
WHERE DeptID IN (1, 2, 3)
GROUP BY EmpId
HAVING COUNT(*) == 3

(2,3,4,5,6,7) 应该是6)。这是表达“与所有这些部门都有联系的员工”的一种自然方式。

编辑:我在另一个关于性能问题的答案中看到了一个注释——我在SQLite和PostgreSQL中尝试过这种方法,使用了适当的索引,看起来它的性能很好,并且正确使用了所有这些索引;在MySQL 5.0中,我不得不承认性能没有那么好。

我怀疑(没有机会在无数其他引擎上进行基准测试;-)其他真正优秀的SQL引擎(如SQL Server 2008、Oracle、IBM DB2、新的开源Ingres……)也会很好地优化这个查询,而其他平庸的引擎(想不出任何一个在MySQL附近流行的引擎)则不会。

所以,毫无疑问,你最喜欢的答案将取决于你真正关心的引擎(这让我回到了十多年前,当时我的职责包括管理维护一个组件的团队,该组件本应在六个不同的引擎上提供性能良好的查询——谈论噩梦般的工作……!-)。