SQL sql中in和any运算符的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3699356/
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
Difference between in and any operators in sql
提问by Jagan
What is the difference between INand ANYoperators in SQL ?
SQL 中的IN和ANY运算符有什么区别？
采纳答案by Pranay Rana
SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>
SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.
SQL> -- 您必须在 ANY 之前放置 =、<>、<、>、<= 或 >= 运算符。
SQL> SELECT *
2 FROM employee
3 WHERE salary > ANY (2000, 3000, 4000);
For In Operator
对于 In 运算符
SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
2 FROM employee
3 WHERE salary IN (2000, 3000, 4000);
But with the IN operator you cannot use =, <>, <, >, <=, or >=
但是对于 IN 运算符，您不能使用 =、<>、<、>、<= 或 >=
回答by Tejas Patel
IN->Equal to Any One in the List.
IN->等于列表中的任何一个。
ANY->Compares Value to Each Value Returned by the Sub Query.
ANY->将值与子查询返回的每个值进行比较。
ALL->Compares Value To Every Value Returned by the Sub Query.
ALL-> 将值与子查询返回的每个值进行比较。
For Example:
例如：
IN: (Q):Display the Details of all the Employees Whose Salaries are Matching with Least Investments of Departments?
IN: (Q): 显示工资与部门投资最少的所有员工的详细信息？
(A): SQL>Select Ename Sal Deptno from Emp Where Sal IN(Select Min(Sal) From Emp Group By Deptno);
(A): SQL>Select Ename Sal Deptno from Emp Where Sal IN(Select Min(Sal) From Emp Group By Deptno);
ANY:
任何：
Meaans Less Than The Maximum Value in the List.
表示小于列表中的最大值。
(Q):Get The Details of All Employees Who are Earning Less Than The Highest Earning Employee Controling Other Emp?
（问）：获取所有收入低于最高收入员工控制其他 Emp 的员工的详细信息？
(A): SQL>Select Empno Ename Job Sl From Emp Where Sal
(A): SQL>Select Empno Ename Job Sl From Emp Where Sal
ANY:->Meaans More Than The Minimum Value in the List.
ANY:-> 表示超过列表中的最小值。
(Q):Get The Details Of All Emps Who are Earning more than the least paid of Department 10?
（问）：获取所有收入超过第 10 部最低收入者的详细信息？
(A): SQL>Select Empno Ename Job Sal From Emp Where Sal>Any(Select Min(Sal) From Emp Where Deptno 10);
(A): SQL>Select Empno Ename Job Sal From Emp Where Sal>Any(Select Min(Sal) From Emp Where Deptno 10)；
ANY:->It's Equivalent to In Operator.
ANY:->它等同于 In 运算符。
Note: 'Some' is also used insted of ANY.
注意：'Some' 也用于代替 ANY。
回答by Wernfried Domscheit
Maybe for better understanding, these two conditions are equivalent. It's a matter of taste which one you use (provided the RDBMS supports both of them)
也许为了更好的理解，这两个条件是等价的。使用哪个是一个品味问题（前提是 RDBMS 支持它们）
... WHERE x IN (SELECT Y FROM THE_TABLE)
... WHERE x =ANY (SELECT Y FROM THE_TABLE)
and these also
而这些也
... WHERE x NOT IN (SELECT Y FROM THE_TABLE)
... WHERE x <>ALL (SELECT Y FROM THE_TABLE)
Actually my personal habit is to use INfor list expression (like WHERE x IN (2,4,6,8)and =ANY, resp. <>ALLfor sub-queries.
实际上我个人的习惯是使用IN列表表达式（例如WHERE x IN (2,4,6,8)和=ANY，分别<>ALL用于子查询。
回答by Mansi Raval
While using all
在使用所有
SELECT empno, sal FROM emp WHERE sal > ALL (2000, 3000, 4000);
SELECT empno, sal FROM emp WHERE sal > ALL (2000, 3000, 4000);
EMPNO SAL
7839 5000
It will return result equivalent to query:
它将返回等效于查询的结果：
SELECT empno, sal FROM emp WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
SELECT empno, sal FROM emp WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
While using any
在使用任何
SELECT empno, sal FROM emp WHERE sal > ANY (2000, 3000, 4000);
SELECT empno, sal FROM emp WHERE sal > ANY (2000, 3000, 4000);
EMPNO SAL
7566 2975
7698 2850
7782 2450
7788 3000
7839 5000
7902 3000
Returns a result same as
返回相同的结果
SELECT empno, sal FROM emp WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
SELECT empno, sal FROM emp WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
回答by Vinoth_S
ANY and ALL OPERATOR IN SQL SERVER 2008R2.
SQL SERVER 2008R2 中的任何和所有运算符。
Using the > comparison operator as an example, >ALL means greater than every value--in other words, greater than the maximum value. For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.
以> 比较运算符为例，>ALL 表示大于每个值——换句话说，大于最大值。例如，>ALL (1, 2, 3) 表示大于 3。 >ANY 表示大于至少一个值，即大于最小值。所以 >ANY (1, 2, 3) 表示大于 1。
Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.
同样，>ANY 表示对于满足外部查询中指定条件的行，引入子查询的列中的值必须至少大于子查询返回的值列表中的值之一。
回答by Thomas Mueller
With ANY, you need an operator:
使用 ANY，您需要一个运算符：
WHERE X > ANY (SELECT Y FROM Z)
With IN, you can't. It's always testing for equality.
使用 IN，你不能。它总是在测试是否平等。
回答by gady RajinikanthB
The ANY and ALL operators are used with a WHERE or HAVING clause.
ANY 和 ALL 运算符与 WHERE 或 HAVING 子句一起使用。
The ANY operator returns true if any of the subquery values meet the condition.
如果任何子查询值满足条件，则 ANY 运算符返回 true。
The ALL operator returns true if all of the subquery values meet the condition.
如果所有子查询值都满足条件，则 ALL 运算符返回 true。
回答by Lipsa Chhotray
When we are comparing any column value using "IN" some set say {value1,value2 ...}then the column value must be present in the set but in case of ANY we compare like this:
当我们使用“IN”比较任何列值时，一些集合说{value1,value2 ...}列值必须存在于集合中，但在 ANY 的情况下，我们像这样比较：
col.value > ANY ({value1,value2,...})
then the value must be greater than any one of the set value.
那么该值必须大于任何一个设定值。
in case of "ALL"
在“全部”的情况下
col.value> ALL({value1,value2,...})
the value must be greater than all the values in the set.
该值必须大于集合中的所有值。
Refer to the following images for better understanding:
请参阅以下图像以更好地理解：
回答by Baboo Lal
(in) is a special kind of operator which is use to pick value one by one from list of values which we have specified.while (any) is use with where clause
(in) 是一种特殊的运算符，用于从我们指定的值列表中一个一个地选择值。而 (any) 与 where 子句一起使用
回答by Sugat Shivsharan
IN - It is easy to understand. The query should select only those values which are specified in 'IN' clause. Now, let us understand 'ANY' with a query. ANY means it should be greater or less than any of the values in the list.
IN - 很容易理解。查询应仅选择在“IN”子句中指定的那些值。现在，让我们通过查询来理解“ANY”。ANY 意味着它应该大于或小于列表中的任何值。
Assume a Orders table which has OrderID from 1 to 10
假设 Orders 表的 OrderID 从 1 到 10
Observer the below query:
select OrderID from Orders
where OrderID < ANY (3,5,7)
观察以下查询：
从 Orders
where OrderID < ANY (3,5,7) 中 选择 OrderID
The answer to above query is :
OrderID
1,2,3,4,5,6
上述查询的答案是：
OrderID
1,2,3,4,5,6
Explanation :The query says find OrderIDs which are less than ANY of the specified values. So the database searches and includes OrderID as follows:
Is 1<3- Yes hence OrderID 1 is included
Is 2<3- Yes hence OrderID 2 is included
Is 3<3- No, is 3<5 -Yes (as 5 is specified value), hence OrderID 3 is included
Is 4<3- No, is 4<5 -Yes, hence OrderID 4 is included
Is 5<3- No, is 5<5 -No, is 5<7(as 5 is specified value)-Yes hence OrderID 5 is included
Is 6<3- No, is 6<5 -No, is 6<7-Yes hence OrderID 6 is included
Is 7<3- No, is 7<5 -No, is 7<7-No hence OrderID 7 is NOT included as no more values in specified list to compare
Is 8<3- No, is 8<5 -No, is 8<7-No hence OrderID 8 is NOT included as no more values in specified list to compare
Is 9<3- No, is 9<5 -No, is 9<7-No hence OrderID 9 is NOT included as no more values in specified list to compare
Is 9<3- No, is 9<5 -No, is 9<7-No hence OrderID 9 is NOT included as no more values in specified list to compare
解释：查询说找到小于任何指定值的 OrderID。因此，数据库搜索并包含 OrderID 如下：
Is 1<3- Yes 因此包含 OrderID 1
Is 2<3- Yes 因此包含 OrderID 2
Is 3<3- No, is 3<5 - Yes（因为指定了 5值），因此包含 OrderID 3
Is 4<3- No, is 4<5 -Yes, 因此包含 OrderID 4
Is 5<3- No, is 5<5 -No, is 5<7（因为指定了 5 value)-Yes 因此包含 OrderID 5
Is 6<3- No, is 6<5 -No, is 6<7-Yes 因此包含 OrderID 6
Is 7<3- No, is 7<5 -No, is 7 <7-No 因此 OrderID 7 不包括在指定列表中没有更多值进行比较
Is 8<3- No, is 8<5 -No, is 8<7-No 因此 OrderID 8 不包括在内，因为指定列表中没有更多值进行比较
Is 9<3- No, is 9<5 -No, is 9<7-No 因此 OrderID 9 不包括在指定列表中作为没有更多的值进行比较
是 9<3- No, is 9<5 -No, is 9<7-No 因此 OrderID 9 不包括作为没有更多值在指定列表中进行比较
Apply the same logic for greater than
select OrderID from Orders
where OrderID > ANY (3,5,7)
对
OrderID > ANY (3,5,7) 的
Orders
中的 大于select OrderID应用相同的逻辑
The answer to above query is :
OrderID
4,5,6,7,8,9,10
上述查询的答案是：
OrderID
4,5,6,7,8,9,10
