SQL 甲骨文：'= ANY()' vs. 'IN()'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2298550/
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
Oracle: '= ANY()' vs. 'IN ()'
提问by eidylon
I just stumbled upon something in ORACLE SQL (not sure if it's in others), that I am curious about. I am asking here as a wiki, since it's hard to try to search symbols in google...
我只是在 ORACLE SQL 中偶然发现了一些我很好奇的东西（不确定它是否在其他人中）。我在这里作为维基询问，因为很难尝试在谷歌中搜索符号......
I just found that when checking a value against a set of values you can do
我刚刚发现，当根据一组值检查一个值时，你可以做
WHERE x = ANY (a, b, c)
As opposed to the usual
与通常的相反
WHERE x IN (a, b, c)
So I'm curious, what is the reasoning for these two syntaxes? Is one standardand one some oddball Oracle syntax? Or are they both standard? And is there a preference of one over the other for performance reasons, or ?
所以我很好奇，这两种语法的推理是什么？是一种标准还是一种奇怪的 Oracle 语法？还是两者都是标准的？出于性能原因，是否有一种偏好，或者？
Just curious what anyone can tell me about that '= ANY' syntax. CheerZ!
只是好奇任何人都可以告诉我关于“= ANY”的语法。加油！
采纳答案by Quassnoi
ANY(or its synonym SOME) is a syntax sugar for EXISTSwith a simple correlation:
ANY（或其同义词SOME）是EXISTS具有简单相关性的语法糖：
SELECT *
FROM mytable
WHERE x <= ANY
(
SELECT y
FROM othertable
)
is the same as:
是相同的：
SELECT *
FROM mytable m
WHERE EXISTS
(
SELECT NULL
FROM othertable o
WHERE m.x <= o.y
)
With the equality condition on a not-nullable field, it becomes similar to IN.
对于不可为空的字段上的相等条件，它变得类似于IN。
All major databases, including SQL Server, MySQLand PostgreSQL, support this keyword.
所有主要的数据库，包括SQL Server、MySQL和PostgreSQL，都支持这个关键字。
回答by noorin fatima
IN- Equal to any member in the list
ANY- Compare value to **each** value returned by the subquery
ALL- Compare value to **EVERY** value returned by the subquery
<ANY() - less than maximum
>ANY() - more than minimum
=ANY() - equivalent to IN
>ALL() - more than the maximum
<ALL() - less than the minimum
eg:
例如：
Find the employees who earn the same salary as the minimum salary for each department:
找出每个部门的工资与最低工资相同的员工：
SELECT last_name, salary,department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Employees who are not IT Programmers and whose salary is less than that of any IT programmer:
非 IT 程序员且工资低于任何 IT 程序员的员工：
SELECT employee_id, last_name, salary, job_id
FROM employees
WHERE salary <ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
Employees whose salary is less than the salary ofall employees with a job ID of IT_PROG and whose job is not IT_PROG:
工资低于所有工作 ID 为 IT_PROG 且工作不是 IT_PROG 的员工的工资的员工：
SELECT employee_id,last_name, salary,job_id
FROM employees
WHERE salary <ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
....................
………………
Hope it helps. -Noorin Fatima
希望能帮助到你。- 诺林法蒂玛
回答by CraigH
To put it simply and quoting from O'Reilly's "Mastering Oracle SQL":
简单地说，引用 O'Reilly 的“掌握 Oracle SQL”：
"Using IN with a subquery is functionally equivalent to using ANY, and returns TRUE if a match is found in the set returned by the subquery."
"We think you will agree that IN is more intuitive than ANY, which is why IN is almost always used in such situations."
“在子查询中使用 IN 在功能上等同于使用 ANY，如果在子查询返回的集合中找到匹配项，则返回 TRUE。”
“我们认为你会同意 IN 比 ANY 更直观，这就是为什么 IN 几乎总是在这种情况下使用的原因。”
Hope that clears up your question about ANY vs IN.
希望能解决您关于 ANY 与 IN 的问题。
回答by Sean Vieira
I believethat what you are looking for is this:
我相信您正在寻找的是：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005298(Link found on Eddie Awad's Blog) To sum it up here:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005298（在Eddie Awad 的博客上找到链接）总结一下：
last_name IN ('SMITH', 'KING', 'JONES')is transformed into
last_name = 'SMITH' OR last_name = 'KING' OR last_name = 'JONES'
last_name IN ('SMITH', 'KING', 'JONES')被转化为
last_name = 'SMITH' OR last_name = 'KING' OR last_name = 'JONES'
while
尽管
salary > ANY (:first_sal, :second_sal)is transformed into
salary > :first_sal OR salary > :second_salThe optimizer transforms a condition that uses the ANY or SOME operator followed by a subquery into a condition containing the EXISTS operator and a correlated subquery
salary > ANY (:first_sal, :second_sal)被转化为
salary > :first_sal OR salary > :second_sal优化器将使用 ANY 或 SOME 运算符后跟子查询的条件转换为包含 EXISTS 运算符和相关子查询的条件
回答by erikkallen
The ANY syntax allows you to write things like
ANY 语法允许您编写诸如
WHERE x > ANY(a, b, c)
or event
或事件
WHERE x > ANY(SELECT ... FROM ...)
Not sure whether there actually is anyone on the planet who uses ANY (and its brother ALL).
不确定这个星球上是否真的有人使用 ANY（及其兄弟 ALL）。
回答by Hogan
A quick google found this http://theopensourcery.com/sqlanysomeall.htm
一个快速的谷歌发现这个http://theopensourcery.com/sqlanysomeall.htm
Any allows you to use an operator other than = , in most other respect (special cases for nulls) it acts like IN. You can think of IN as ANY with the = operator.
Any 允许您使用 = 以外的运算符，在大多数其他方面（空值的特殊情况），它的作用类似于 IN。您可以使用 = 运算符将 IN 视为 ANY。
回答by Lukas Eder
This is a standard. The SQL 1992standard states
这是一个标准。在SQL 1992年标准规定
8.4 <in predicate>
[...]
<in predicate> ::= <row value constructor> [ NOT ] IN <in predicate value>[...]
2) Let RVC be the <row value constructor> and let IPV be the <in predicate value>.
[...]
4) The expression
RVC IN IPVis equivalent to
RVC = ANY IPV
8.4 <在谓词中>
[...]
<in predicate> ::= <row value constructor> [ NOT ] IN <in predicate value>[...]
2) 让 RVC 成为 <row value constructor>，让 IPV 成为 <in predicate value>。
[...]
4）表达式
RVC IN IPV相当于
RVC = ANY IPV
So in fact, the <in predicate>behaviour definition is based on the 8.7 <quantified comparison predicate>. In Other words, Oracle correctly implements the SQL standard here
所以实际上，<in predicate>行为定义是基于8.7 <quantified comparison predicate>. 换句话说，Oracle 在这里正确地实现了 SQL 标准
回答by Dave
Perhaps one of the linked articles points this out, but isn't it true that when looking for a match (=) the two return the same thing. However, if looking for a range of answers (>, <, etc) you cannot use "IN" and would have to use "ANY"...
也许其中一篇链接文章指出了这一点，但在查找匹配项 (=) 时，两者返回相同的内容，这难道不是真的。但是，如果要查找一系列答案（>、< 等），则不能使用“IN”，而必须使用“ANY”...
I'm a newb, forgive me if I've missed something obvious...
我是新手，如果我错过了一些明显的东西，请原谅我......
回答by Robert Rocha
MySql clears up ANY in it's documentation pretty well:
MySql 很好地清除了它的文档中的任何内容：
The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.” For example:
ANY 关键字必须跟在比较运算符之后，表示“如果子查询返回的列中的任何值的比较为 TRUE，则返回 TRUE”。例如：
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).
假设表 t1 中有一行包含 (10)。如果表 t2 包含 (21,14,7)，则表达式为 TRUE，因为 t2 中的值 7 小于 10。如果表 t2 包含 (20,10)，或者表 t2 为空，则表达式为 FALSE。如果表 t2 包含 (NULL,NULL,NULL)，则表达式未知（即 NULL）。
https://dev.mysql.com/doc/refman/5.5/en/any-in-some-subqueries.html
https://dev.mysql.com/doc/refman/5.5/en/any-in-some-subqueries.html
Also Learning SQL by Alan Beaulieu states the following:
Alan Beaulieu 的《Learning SQL》还指出以下内容：
Although most people prefer to use IN, using = ANY is equivalent to using the IN operator.
尽管大多数人更喜欢使用 IN，但使用 = ANY 等同于使用 IN 运算符。
