VS相交中的SQL [英] SQL in vs intersection

问题描述

我想从表格中选择结果

USER HOBBY
John Sport
Ann  Piano
Lee  Reading
Ann  Sport
Lee  Piano

我想搜索有多个共同爱好的人.哪个查询的性能更好，

And I want to search people who have more than one common hobbies. Which query has better performance,

select user from table where hobby = "sport"
intersect 
select user from table where hobby = "piano"

或

select user from table where user in (select user from table where hobby = "piano") and where hobby = "sport"?

推荐答案

在Oracle中，假设(USER, HOBBY)是唯一的，则可以使用GROUP BY查询:

In Oracle, assuming (USER, HOBBY) is unique, you could use a GROUP BY query:

SELECT user
  FROM user_hobby
 WHERE hobby IN ('piano', 'sport')
 GROUP BY user
HAVING COUNT(*) = 2

这将使Oracle最多对数据执行一次传递，而INTERSECT将分别处理每个查询，因此需要两次传递.

This will make Oracle perform at most a single pass on data whereas the INTERSECT would treat each query separately and would therefore need two passes.

这篇关于VS相交中的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！