Understanding SQL ANY and ALL Operators

Hello, folks! Hope you all are doing well. In this article, we will be discussing about SQL ANY and ALL Operators.

As we know, Operators acts as a medium or catalyst to perform any operation on some data values. Various kinds of Operators include Bitwise, Arithmetic, Logical operators, etc.

SQL ANY and ALL Operators fall under the category of Logical Operators i.e. they evaluate to true or false upon the fulfilment of the conditions.

Let us now understand in detail about SQL ANY and ALL Operators.

What is SQL ANY Operator?

SQL ANY operator, as mentioned above, is a logical operator that evaluates to true upon the fulfillment of the below condition.

• Any record/data value that meets the condition mentioned in the sub-query.

Thus, we can say that ANY operator returns to TRUE, if any of the data values pass the sub-query condition.

Let us now understand the structure of ANY operator in the upcoming section.

Syntax of SQL ANY Operator

SELECT columns
FROM table
WHERE column-name operator ANY
(sub-query);

• sub-query: This query could be any standard SQL query.
• operator: SQL comparison operator to compare data values.

SQL ANY Operator evaluates to TRUE. And thus returns the selected columns or performs the appropriate operation/query.

Having understood the working of SQL ANY operator, let us now implement the same through examples in the upcoming section.

Implementing SQL ANY Operator through examples

In the below example, we have implemented SQL ANY operator with SELECT statement and an assignment operator(=).

SELECT Cost,City
FROM Info
WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost = 100);

Here, we have displayed the data values of the columns ‘Cost’ and ‘city’ whose at least one ‘cost’ value is 100.

NOTE: We can use the SQL ANY operator to match columns and data from other tables in a similar manner.

Output:

Now, we have selected all the data values of the table for which any one data value of the column ‘cost’ is greater than 50.

SELECT *
FROM Info
WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost > 50);

Output:

Further, we have selected all the data values of the table where any one ‘cost’ value is not equal to 100.

SELECT *
FROM Info
WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost <> 100);

Output:

In the below piece of code, we have displayed all the data values for which any value of the column ‘cost’ is greater than or equal to 50.

SELECT *
FROM Info
WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost >= 50);

Output:

What is SQL ALL Operator?

SQL ALL operator evaluates to TRUE, only if all the data values of the table pass the condition.

We won’t get the desired results if any of the data values violate the conditions mentioned in the query.

Let us now understand the syntax of SQL ALL operator in the below section.

Syntax of SQL ALL Operator

SQL ALL operator results to TRUE when all the data values pass the test condition mentioned in the query.

SELECT columns
FROM table
WHERE column-name operator ALL
(sub-query);

Note: SQL ANY and ALL operators can be used with SQL WHERE, GROUPBY as well as HAVING clauses.

Implementing SQL ALL Operator through examples

Now, let us apply the syntax of SQL ALL operator and go through the below examples.

SELECT ALL city 
FROM Info
WHERE TRUE;

In the above example, we have displayed all the data values of the column ‘city’ where the value is not NULL i.e. there is a value specified for every row of the column city.

Output:

Now, we have selected all the data values from the table, for which every data value of the column ‘cost’ is greater than 100. While we have passed a subquery selecting all the data values less than 100 to pass to the ALL operator.

SELECT *
FROM Info
WHERE Cost > All (SELECT Cost FROM Info WHERE Cost <100);

Output:

Here, we have used SQL GROUP BY and SQL HAVING clause along with SQL ALL Operator.

Now, we have selected all the data columns of the table and grouped it using ‘city’ values. Further, we have selected all the data values in such a manner that the minimum ‘cost’ of every data value selected, should be less than the AVERAGE of the column ‘cost’.

SELECT id,city ,Cost
FROM Info 
GROUP BY city 
HAVING MIN(Cost) < ALL (SELECT AVG(Cost) FROM Info);

Output:

Conclusion

By this, we have come to the end of this topic. Please feel free to comment below in case, you come across any doubt!

For more such posts related to SQL, do visit SQL JournalDev.

References

• SQL ANY ALL — StackOverFlow