是一个长IN子句的代码气味吗？ [英] Is a long IN clause a code smell?

问题描述

简单的问题。想知道长的IN子句是否是代码气味？我真的不知道如何证明它。

 选择
名称， 
 code，
 capital，
 population，
 flower，
 bird 
 from us_states 
其中
代码在
 （'NJ'，'NY'，'PA'，'CA'，'AL'，'AK'，'AZ'，
'IL'，'IN'，'KY'，'KS' DC'，'MD'，'MA'）
  

数据库通常如何实现这样的查找？是否创建并加入了临时表？

感觉应该是一个连接...

我不是说所有IN子句都是坏的。有时候你不能帮助它。但有一些情况（特别是它们得到的时间更长），其中匹配的元素集实际上来自某处。

这是值得创建（通过应用程序级别）一个临时表，其中包含您要搜索的所有元素，然后

 选择u。* 
来自us_states u 
 
加入#chosen_states t 
 on u.code = t.code 
  

解决方案

我认为这是一个代码气味。一方面，数据库对于 IN 子句中允许的元素数量有限制，如果你的SQL是动态生成的，你最终可能会碰到这些限制。 / p>

当列表开始变为long-ish时，我将转换为使用带有临时表的存储过程，以避免任何错误。

我怀疑性能是一个主要问题，但 IN 子句非常快，因为它们可能短路，不像 NOT IN 子句。

Simple question. Wondering if a long IN clause is a code smell? I don't really know how to justify it. I can't put my finger on why it smells other than that I think it does.

select
  name,
  code,
  capital,
  population,
  flower,
  bird
from us_states
where
  code in
    ('NJ', 'NY', 'PA', 'CA', 'AL', 'AK', 'AZ',
    'IL', 'IN', 'KY', 'KS', 'DC', 'MD', 'MA')

How does a database typically implement such a lookup? Is a temporary table made and joined to? Or is it just expanded into a series of logical ORs?

It feels like it should have been a join...

I'm not saying all IN clauses are bad. Sometimes you can't help it. But there are some cases (particularly the longer they get) where the set of elements you're matching against actually comes from somewhere. And shouldn't that be joined on instead?

Is it worth creating (via the application level) a temporary table that has all the elements you want to search against and then doing a real join against that?

select u.*
from us_states u

join #chosen_states t
on u.code = t.code

解决方案

I think it is a code smell. For one thing, databases have limits as to the number of elements allowed in an IN clause, and if your SQL is generated dynamically, you may eventually bump up against those limits.

When the list starts to become long-ish, I would convert to using a stored procedure with a temporary table, to avoid any chance of errors.

I doubt performance is a major concern though, IN clauses are very fast, as they can short-circuit, unlike NOT IN clauses.

这篇关于是一个长IN子句的代码气味吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！