SQL 效率 - [=] vs [in] vs [like] vs [matches] [英] SQL efficiency - [=] vs [in] vs [like] vs [matches]

问题描述

出于好奇，我想知道使用 [=] 与 [in] 与 [like] 与 [matches](只有 1 个值)的 sql 语法.

Just out of curiosity, I was wondering if there are any speed/efficiency differences in using [=] versus [in] versus [like] versus [matches] (for only 1 value) syntax for sql.

select field from table where field = value;

对比

select field from table where field in (value);

对比

select field from table where field like value;

对比

select field from table where field matches value;

推荐答案

我将添加也存在和子查询.

但性能取决于给定 SQL 引擎的优化器.

But the performance depends on the optimizer of the given SQL engine.

在 oracle 中，IN 和 EXISTS 之间有很多区别，但在 SQL Server 中不一定.

In oracle you have a lot of differences between IN and EXISTS, but not necessarily in SQL Server.

您必须考虑的另一件事是您使用的列的选择性.一些案例表明IN更好.

The other thing that you have to consider is the selectivity of the column that you use. Some cases show that IN is better.

但您必须记住 IN 是 non-sargable(不可搜索参数)，因此它不会使用索引来解析查询，LIKE 和 = sargable 并支持索引

But you have to remember that IN is non-sargable (non search argument able) so it will not use the index to resolve the query, the LIKE and = are sargable and support the index

最好的?您应该花一些时间在您的环境中对其进行测试

The best ? You should spend some time to test it in your environment

这篇关于SQL 效率 - [=] vs [in] vs [like] vs [matches]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！