SQL ANY Vs IN Operator

In this discussion, we'll go over the definitions of IN and ANY , as well as the differences between the two operators.

Only the values supplied in the 'IN' clause should be selected by the IN query.

ANY denotes that it should be bigger or lesser than any of the listed values.

SQL ANY Operator

If any of the subquery values fit the requirement, ANY returns true. A logical operator called the ANY compares a value to a collection of values returned by a subquery.

The ANY operator must be followed by a subquery and preceded by a comparison operator >>, >=,,<=, =, >.

The condition evaluates to false if the subquery produces no rows. Assuming the subquery does not return zero rows, the following example shows how to utilise the ANY operator with each comparison operator:

• x = ANY (…) - To evaluate to true, the values in column c must match one or more values in the set.
• x > ANY (…) - To evaluate to true, the values in column c must be bigger than the smallest value in the set.
• x < ANY (…) - To evaluate to true, the values in column c must be smaller than the largest value in the set.
• x >= ANY (…) - To evaluate to true, the values in column c must be larger than or equal to the smallest value in the set.
• x <= ANY (…) - To evaluate to true, the values in column c must be less than or equal to the largest value in the set.

Example: Make a list of all items from Book table that have sold for below 200.

Mysql,Sql-server

SELECT Name,Price
FROM Book
WHERE Price = ANY (SELECT Price FROM Book WHERE Price < 200);

In above query we select Name of book and Price of book Then we get which book has price value below 200 using ANY operator

output: The output will be

SQL IN Operator

The IN operator is used to replace a collection of inputs that are joined with an OR in a SELECT, UPDATE , or DELETE statement using the = operator.

It can make code more readable and understandable.

In most cases, it will have no effect on performance.

Example: The following SQL query can be used to see if the search value 20 is present inside the specified range:

Mysql,Sqlite,Ms access,Sql-server

SELECT 20 IN (4,30,20,45,67,69);

If selected value is not inside value then we will get an output will be 0 otherwise we get 1

Output The output of in query is

SQL Difference Between ANY And IN Operator

We will discuss the major contrasts between ANY Vs ALL operator