SQL IN 条款 1000 项限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4722220/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL IN Clause 1000 item limit
提问by Jeune
It is possible to put more than 1000 items in the SQL IN clause? We have been getting issues with our Oracle database not being able to handle it.
可以在 SQL IN 子句中放置 1000 多个项目吗？我们一直遇到 Oracle 数据库无法处理的问题。
IF yes, how do we put more than 1000 items in the SQL IN clause?
如果是，我们如何在 SQL IN 子句中放置 1000 多个项目？
IF not, what else can I do?
如果没有，我还能做什么？
回答by gordy
There's another workaround for this that isn't mentioned in any of the other answers (or other answered questions):
其他任何答案（或其他已回答的问题）中都没有提到另一种解决方法：
Any in statement like x in (1,2,3)can be rewritten as (1,x) in ((1,1), (1,2), (1,3))and the 1000 element limit will no longer apply. I've tested with an index on xand explain plan still reports that Oracle is using an access predicate and range scan.
任何 in 语句x in (1,2,3)都可以重写为(1,x) in ((1,1), (1,2), (1,3))，并且 1000 个元素的限制将不再适用。我已经使用索引进行了测试，x并且解释计划仍然报告 Oracle 正在使用访问谓词和范围扫描。
回答by Jonathan
You should transform the IN clauses to INNER JOIN clauses.
您应该将 IN 子句转换为 INNER JOIN 子句。
You can transform a query like this one
您可以转换这样的查询
SELECT foo
FROM bar
WHERE bar.stuff IN
(SELECT stuff FROM asdf)
in a query like this other one.
在像另一个这样的查询中。
SELECT b.foo
FROM (
SELECT DISTINCT stuff
FROM asdf ) a
JOIN bar b
ON b.stuff = a.stuff
You will also gain a lot of performance
您还将获得很多性能
回答by Pratik Agarwal
We can have more than one "IN" statement for the same variable.
对于同一个变量，我们可以有多个“IN”语句。
For ex:
例如：
select val
from table
where val in (1,2,3,...)
or
val in (7,8,9,....)
回答by Andrew
Another way:
其它的办法：
SELECT COL1, COL2, COL3 FROM YOUR_TABLE
WHERE 1=1
AND COL2 IN (
SELECT VAL1 as FAKE FROM DUAL
UNION
SELECT VAL2 as FAKE FROM DUAL
UNION
SELECT VAL3 as FAKE FROM DUAL
--...
)
