内部联接与执行where in子句 [英] Inner join versus doing a where in clause

问题描述

假设我从查询中返回了大约10-30行：

  select * from location = 10的用户； 
  

以下各项之间是否有区别：

 选择*来自用户的
 u 
 u.location上的内部联接位置l = l.id 
其中location = 10; 
  

对比：

 从location = 10的用户中选择*； ＃说这返回1,2,3,4,5 
 select *从id IN（1,2,3,4,5）
   
 
 
基本上我想知道在执行内部联接与执行WHERE IN子句之间是否存在性能差异。
解决方案
发出一个查询和发出两个查询之间有区别吗？好吧，我当然希望如此。 SQL引擎正在工作，并且它从两个角度执行（从某种角度而言）是两个查询的两倍。
 
 
 
通常，解析单个查询将是比解析一个查询，返回中间结果集然后将其反馈给另一个查询更快。 
 
 
 
对于此查询：
  select * 
来自用户u内部联接
位置l 
在u.location = l.id 
其中u.location = 10; 
  
您想要在个用户（位置）上建立索引和 location（id）。
 
 
 
我确实想指出其他内容。查询不等效。真正的比较查询是：
 选择l。* 
从位置l 
，其中l.id = 10; 
  
您在所在的位置使用同一列和上的。因此，这将是最有效的版本，并且您希望在 location（id）上建立索引。
 
Say I have about 10-30 rows returned from my query:
select * from users where location=10;
Is there any difference between the following:
  select * 
  from users u 
      inner join location l on u.location = l.id 
  where location=10; 
versus:
select * from users where location=10;  # say this returns 1,2,3,4,5
select * from location where id IN (1,2,3,4,5)
Basically I want to know if there are any performance differences between doing an inner join versus doing a WHERE IN clause.
解决方案
Is there a difference between issuing one query and issuing two queries?  Well, I certainly hope so.  The SQL engine is doing work, and it does twice as much work (from a certain perspective) for two queries.

In general, parsing a single query is going to be faster than parsing one query, returning an intermediate result set, and then feeding it back to another query.  There is overhead in query compilation and in passing data back and forth.

For this query:
select * 
from users u inner join
     location l
     on u.location = l.id 
where u.location = 10; 
You want an index on users(location) and location(id).

I do want to point something else out.  The queries are not equivalent.  The real comparison query is:
select l.*
from location l
where l.id = 10;
You are using the same column for the where and the on.  Hence, this would be the most efficient version and you want an index on location(id).

                        
这篇关于内部联接与执行where in子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！