EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial ANY in MySQL

ANY in MySQL

By Aanchal SharmaAanchal Sharma

Any in MySQL

Introduction to ANY in MySQL

ANY is a MySQL operator that returns a true value if any of the Subquery condition in MySQL is fulfilled when the SQL query is executed. However, ALL SQL operator works related to ANY operator but it returns true if all the Subquery values is satisfied by the condition in MySQL. Basically, MySQL introduces some special characters or words that help to perform a specified operation or more. We can use the SQL operators together with operands to generate a SQL statement code to contain a certain condition or execute specific operations. Hence, like other operators the ANY operator in MySQL is also used to govern a provisional expression in a SQL statement query to fulfill the benchmarks and provide a result set with true values.

Syntax

  • Following is the elementary syntax of the ANY operator used in MySQL:

SELECT Column1 [Column2,..] FROM TableA WHERE Column2 Operator ANY (SELECT Column2 FROM TableB WHERE condition);

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • We can use the All SQL operator with the below query:

SELECT Column1 [Column2,..] FROM TableA WHERE Column2 Operator ALL (SELECT Column2 FROM TableB WHERE condition);

  • Remember that the SQL operator used here should be a typical one of comparison operator that is commonly used in MySQL such as <, >, =, !=, <>, <=, >=.

How does ANY Operator work in MySQL?

Generally, in MySQL operation with ANY and comparison operator we have the following syntax:

Operand ComparisonOperator ANY (Subquery);

  • Since ANY operator should return true if the condition is true so, the ANY SQL keyword should track a comparison operator and if a comparison is valid for ANY of the values provided in the column by the result of the subquery.
  • For illustration, let us consider the succeeding statement:

SELECT K1 FROM Q1 WHERE K1 > ANY (SELECT K1 FROM Q2);

  • Suppose, there exists a field row in the Q1 table with value (11), then the ANY operator returns TRUE from the expression if there exist values in table Q2 at least one less than 11, for example (30, 12, 8). Here, the condition will be fulfilled because, in Q2, a value 8 is available which is a smaller amount than 11 in Q1 so the ANY keyword will result in TRUE value.
  • But supposing Q2 comprises (30,12,31), then no values of subquery will be less than Q1 value and thus the ANY operator will show FALSE as the conditional expression is not satisfied. Also, if Q2 holds NULL values like (NULL, NULL, NULL) then, the expression will be unknown.
  • The keyword IN in MySQL works as a code-named (alias) for = ANY when used with a Subquery in the query statement. Hence, we can say that the below two SQL statements are identical in MySQL;

Code:

SELECT K1 FROM Q1 WHERE K1 = ANY (SELECT K1 FROM Q2);
SELECT K1 FROM Q1 WHERE K1 IN (SELECT K1 FROM Q2);

  • But we cannot say that IN SQL operator and = ANY operator keyword are synonyms when we use a list of expressions because IN takes the list of expressions and = ANY cannot.
  • Also, NOT IN cannot be an alias or designated name for <> ANY operator but can be used for <> ALL.
  • The keyword SOME in MySQL is an alias given to ANY keyword and therefore the two declarations of SQL code are equivalent:

Code:

SELECT K1 FROM Q1 WHERE K1 <> ANY (SELECT K1 FROM Q2);
SELECT K1 FROM Q1 WHERE K1 <> SOME (SELECT K1 FROM Q2);

  • We can use table query in a scalar IN, ANY, SOME subquery condition is given that the table contains only one column. Understand if Q2 in the above table has only one column, then the above SQL statements can be re-written as follows:

Code:

SELECT K1 FROM Q1 WHERE K1 > ANY (Table Q2);

// (SELECT K1 FROM Q2) is substituted with Table Q2

Code:

SELECT K1 FROM Q1 WHERE K1 = ANY (Table Q2);
SELECT K1 FROM Q1 WHERE K1 IN (Table Q2);
SELECT K1 FROM Q1 WHERE K1 <> ANY (Table Q2);
SELECT K1 FROM Q1 WHERE K1 <> SOME (Table Q2);

  • Remember <> operator defines the true meaning of SQL syntax indicating that there are nearly some values that are not equal to a condition but not all values.

Examples to Implement ANY in MySQL

Let us show some examples and take some tables named ‘Customers’ and ‘Orders’ to execute SQL queries using ANY keyword:

1. Customers Table

ANY in MYSQL - 1

2. Orders Table

ANY in MYSQL - 2

  1. ANY with = operator

Code:

SELECT Name, City FROM Customers WHERE CustomerNum = ANY (SELECT CustomerNum FROM Orders WHERE STATUS = 'Success');

Output:

= operator

  1. ANY with > operator

Code:

SELECT Name, City, CustomerNum FROM Customers WHERE CustomerNum = ANY (SELECT CustomerNum FROM Orders WHERE Budget>8000);

Output:

> operator

Advantages of using ANY operator in MySQL

  • ANY is a type of logical operator in MySQL which returns the Boolean value as a result of the SQL query. It is used to select any or some tuples of the SELECT statement.
  • The ANY operator allows comparing the value of a table to each value in the result list or rows provided by the subquery condition. After this, the ANY keyword if find any value that matches at least one value or row of the inner query then, it gives TRUE as a result.
  • Thus, in MySQL the ANY operator should be preceded by comparison operators. So, the ANY operator returns TRUE if any of the inner queries execute to satisfy the condition.
  • The ANY keyword is useful to provide the result which is a distinct column value from a table that matches or has any record in another table.
  • You can perform many different types of comparison using ANY operator with SELECT and WHERE keywords and out a condition to match and provide the required result from a combination of two tables.
  • It is helpful to get the result if there exists any value in one table that is satisfying a certain condition that shows the value that matches.

Conclusion

The ANY operator in MySQL is used to filter the result set from SQL syntax only if any of the values meet the condition otherwise it shows false. Like other different operators in MySQL, the ANY keyword is also an operator used along with comparison operators with a distinct result by comparing subquery results and operands in the SQL expression. I hope you like the article and learned some new points.

Recommended Articles

This is a guide to ANY in MySQL. Here we discuss Syntax, how does it work with examples to implement with proper codes and outputs and advantages to use. You can also go through our other related articles to learn more –

  1. MySQL Outer Join
  2. ORDER BY in MySQL
  3. MySQL Subquery | Types and Examples
  4. MySQL Timestamp | How to Works?
  5. Guide to IF Statement in MySQL