SQL IN, SOME, ANY, ALL Operators

SQL IN operator

The SQL IN operator allows testing a list of values in the where clause. The values are separated using a comma(,). It signifies multiple OR conditions in a SELECT, INSERT, UPDATE or DELETE statement in the SQL Query.

Syntax

The comma-separated values are compared and tested by the WHERE clause. If any of the values satisfy the conditions, the IN statement will be true else false.

OR

The subquery is a SELECT statement whose result will be compared and tested by the WHERE clause. If any of the conditions is true, the IN statement will be true else false.

Examples:

In this example, we will take a table named companies with the following set of data:

SQL Query with IN Operator with a String Value

The IN statement can have values of any data type like char, string or int. Let's have a look at the IN condition with multiple string values.

The * in the SELECT statement shows all the fields from the companies table in the output. The WHERE statement checks the IN condition and returns all the rows whose company name is Microsoft, Oracle, or PayPal.

The equivalent SQL statement with OR condition looks like:

It is clear from the statement that the IN condition makes the code more readable, shorter, and less repetitive than OR conditions.

SQL Query with IN Operator with a Numeric Value

Have you ever wondered how to search a numeric value using the IN operator in a SELECT statement?

Let's look at the IN condition with integer values using an example. In this example, we consider the table used in the previous examples.

The output of the following code is:

The * in the SELECT statement shows all the fields from the companies table in the output. The WHERE statement checks the IN condition and returns all the rows whose company id is either 1, 3, or 6.

The equivalent SQL statement with OR condition looks like:

Let's create another table named Vacancy which has the following set of data:

The IN condition is used with a SELECT statement returning a record from a single column. It cannot return values from more than one column in the SELECT statement.

Let's see it with an example.

In the above SQL Query, the subquery SELECT company_id from Vacancy WHERE role like '%Developer' returns four company_id, 3, 6, 10, and 18. So, now the previous SQL Query will look like SELECT * FROM companies WHERE company_id IN(3,6,10,18);

The above SQL Query will give the following result:

Using IN condition with NOT Operator

Let's see how to use the IN condition with the NOT operator. We know that the NOT operator is used to reverse the condition. The same happens when we use the IN condition with the NOT operator to create the NOT IN condition. It returns a true value if the expression does not match any of the values in the list.

Let's see it with an example.

The above SQL query would return all rows from the company table where the company_name is not present in the given list that is Microsoft, Oracle, and Paypal. This method is more efficient at times when the list of values you want is larger than the list of values you don't want.

The result of the following SQL query would be:

Its equivalent SQL statement is written using AND conditions and not by OR condition as the IN condition is negated.

The SQL ALL Operator

To perform a comparison between a single value and a range of values in an SQL query, the ALL and ANY operators are used.

ALL operator is used to return all records of the SELECT statement. The result of the ALL operator is true only if the condition satisfies all values in the range. It returns a boolean value which is true when the subquery does not return any row.

Greater than (>) ALL means greater than the maximum value. For example, greater than ALL (10, 60, 100) means larger than 100 as the numbers greater than it will be greater than others. Less than (<) ALL means less than the minimum value. For example, Less than (<) ALL (20,56,13,5,74) means less than the minimum value that is 5 as the numbers less than the minimum are less than others. It would return false if no such data is possible.

The ALL operator is used with SELECT, WHERE and HAVING statements in SQL Queries.

ALL Syntax With SELECT

ALL Syntax With WHERE or HAVING

Only standard comparison operator(=, <>, !=, >, >=, <, or <=) are used here.

Examples:

Let's us consider the following "Products" table as the sample database:

The selection of values will be done from the following "OrderDetails" table:

Example

This is an SQL query to get the name of all products from the Products table.

The output contains the names of all the products as the condition is always true.

Example 2

In the above SQL Query, we get the product names of all the products from the Product table using the SELECT statement whose quantity is equal to 15.

The output of the above SQL query is:

The subquery returns the ProductID of the products from the OrderDetails table whose quantity is equal to 15. The outer query returns the Product Name from the Product table whose ProductId is equal to those returned by the subquery.

Example 3

This SQL Query is used to obtain the OrderID of the products such that the maximum quantity among all products of that OrderId is greater than the average quantity of all OrderID. The output of the following SQL query is:

The subquery returns the average of the quantity of all OrderID. The outer query returns the OrderID whose maximum quantity is greater than the maximum of the values returned by the subquery.

Using the NOT ALL Operator

Syntax

Example

The subquery in the SQL statement returns the ProductID from the OrderDetails table whose quantity is equal to 15. The above SQL Query returns the records of the products whose ProductID is not equal to any of the values returned by the subquery.

The result of the SQL Query is as following:

The SQL ANY Operator

The result of the ANY operator is true if the expression or condition matches any of the values in the range. The result of ANY operator is always a boolean value.

Greater than (>) ANY means greater than at least one value or the minimum value. For example, greater than (>) ANY (10, 60, 100) means greater than the minimum value that is 10. Less than (<) ANY means less than at least one value or the maximum value. For example, Less than (<) ANY (20,56,13,5,74) means less than the maximum value which is 74. It would return false if no such data is possible.

Syntax

Only standard comparison operator(=, <>, !=, >, >=, <, or <=) are used here.

Examples:

Let's look at a SQL Query to find the Distinct CategoryID of the products which have any record in the OrderDetails table.

In the above SQL Query, the subquery returns all the ProductID from the OrderDetails table. The SELECT statement gives the distinct CategoryID for the products from the Products table whose ProductID matches the result of the subquery.

The output of the above SQL Query is the following:

Let's look at a SQL query to obtain any records from the OrderDetails table such that Quantity = 9.

In the above SQL Query, the subquery returns all the ProductID from the OrderDetails table whose quantity is equal to 9. The SELECT statement gives the names of the products from the Products table whose ProductID matches any of the results of the subquery.

The output of the above SQL Query is the following:

Let's look at a SQL query to obtain any records from the OrderDetails table such that Quantity is greater than 99.

In the above SQL Query, the subquery returns all the ProductID from the OrderDetails table whose quantity is greater than 99. The SELECT statement gives the names of the products from the Products table whose ProductID matches any of the results of the subquery. Since there are no such products available, the query returns a value and no record is shown in the output.

The output of the above SQL Query is the following:

Using the NOT ANY Operator

Syntax

Example

The subquery in the SQL statement returns the ProductID from the OrderDetails table whose quantity is equal to 15. The above SQL Query returns the records of the products whose ProductID is not equal to all of the values returned by the subquery.

The result of the SQL Query is as following:

SQL Some Operator

The SOME operator in SQL is used to compare a value with a set of records of a column returned by a subquery. The result of a SQL Query with the SOME operator is true when the condition matches at least one value in the subquery preceded by a comparison operator.

Greater than (>) SOME means greater than at least one value or the minimum value. For example, greater than (>) SOME (10, 60, 100) means greater than the minimum value that is 10. Less than (<) SOME means less than at least one value or the maximum value. For example, Less than (<) SOME (20,56,13,5,74) means less than the maximum value which is 74. It would return false if no such data is possible.

Generally, the SOME operator is used with the WHERE clause to check whether the values returned by the subquery match with the given column values.

Syntax

The above SQL Query returns values only when the values in column1 match with the values returned by column1 in the subquery.

Examples:

Let's consider the table "Employees" with the following data:

Let's have a look at a few examples of SQL queries using the SOME operator.

The subquery in the given SQL statement returns the salary of the employees that is greater than 25000.

The above SQL query will return the details of employees whose salary matches with the result of the subquery that is the salary greater than 25000. The result of the above SQL query is as given below.

Let's see another example of SOME with the Greater than the operator.

This SQL Query returns the details of the employees whose salary is greater than at least one of the values or the minimum value returned by the subquery. The subquery in the above SQL Query returns the salary of the employees that are greater than 25000.

The above statement transforms to

The result of the above SQL query is as given below:

The above SQL Query returns the salary that is greater than at least one of the values returned by the subquery. The subquery returns the salary of the employees having a location as Chennai. The salary of an employee with a Chennai location is 20000 and 65000. The above statement transforms to

Thus the final result returns the details of the employees having salary greater than 20000.