'where'in with duplicates或'select distinct' - 表现 - 编程

I would like to return some results basing on subquery results and I was wondering what would be faster - having subquery results be distinct or where in clause having duplicates?
Example

我想基于子查询结果返回一些结果，我想知道什么会更快 - 子查询结果是不同的还是在子句中有重复？例

select * 
from some_table 
where my_column in (select /* distinct? */ my_column from some_table_2)

如果

-- subquery
select /*distinct?*/ my_column from some_table_2;

This would return something like:

这会返回类似于：

  | MY_COLUMN
1 | 25
2 | 25
3 | 54
...

This example may not make sense but it presents my point.

这个例子可能没有意义，但它提出了我的观点。

1 个解决方案

#1

There is no benefit to putting distinct in the subquery for in. An in does what is called a "semi-join". This stops at the first matching row.

将子查询中的distinct放入in中没有任何好处。执行所谓的“半连接”。这将停在第一个匹配的行。

Oracle probably ignores the select distinct. Other databases might actually implement it.

Oracle可能会忽略select distinct。其他数据库实际上可能实现它。

If you care about performance, try both these versions:

如果您关心性能，请尝试以下两个版本：

select t.* 
from some_table t
where my_column in (select my_column from some_table_2)

and

和

select t.*
from some_table t
where exists (select 1 from some_table_2 t2 where t2.my_column = t.my_column);

This version can take advantage of an index on some_table_2(my_column).

此版本可以利用some_table_2（my_column）上的索引。

'where'in with duplicates或'select distinct' - 表现

1 个解决方案

#1

更多相关文章

随机推荐