mysql

Khóa học miễn phí MySQL – ANY Operator nhận dự án làm có lương

Posted on by user

MySQL – ANY Operator

Table of content

The operators in MySQL have the same meaning as that of operators in mathematics. They are keywords that are used in MySQL statements for performing comparisons or logical operations.

ANY Operator in MySQL

The MySQL ANY keyword can be used with a comparison operator (such as =, <, >, <=, >=, <>) to compare a value with a set of values returned by the subquery.

  • This operator will return true if the given condition is satisfied for any of the values in the set.

  • This operator will return false if none of the values in the specified set satisfy the given condition.

The ANY operator must be preceded by a standard comparison operator i.e. >, >=, , !=, and followed by a subquery.

Syntax

Following is the syntax of the ANY operator in MySQL −

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name operator ANY (subquery);

Where,

  • column_name is the name of a column to be compared with a subquery.

  • operator is a comparison operator such as =, <, >, <=, >=, or <>.

  • subquery is a SELECT statement that returns a single column of values.

Example

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above-created MySQL table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the below query to retrieve all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

ANY with “>” Operator

The MySQL ANY operator can be used with the comparison operator “>” (greater than) to verify whether a particular column value is greater than the column value of any of the other records returned by the subquery.

Example

In the following query, we are selecting all records from the CUSTOMERS table where the SALARY column is greater than any of the salaries associated with customers whose age is 22.

SELECT * FROM CUSTOMERS
WHERE SALARY > ANY 
(SELECT SALARY FROM CUSTOMERS WHERE AGE = 22);

Output

The salary of customer with age 22 is 4500. The following are the customers whose salaries are greater than 4500 (age=22).

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

ANY with “<” Operator

We can use the comparison operator “<“ (less than) with the MySQL ANY operator to verify whether a particular column value is less than the column value of any of the records returned by the subquery.

Example

In this query, we are selecting all records from the CUSTOMERS table where the SALARY column is less than any of the salaries associated with customers whose age is 32.

SELECT * FROM CUSTOMERS
WHERE SALARY < ANY 
(SELECT SALARY FROM CUSTOMERS WHERE AGE = 32);

Output

The salary of 32 aged customer is 2000. The only customer with salary less than 2000 is ”Khilan” −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00

ANY with “=” operator

We can use the MySQL ANY operator with the comparison operator “=” (equal to) to fetch the records from a table where a column value is equal to any value returned by a subquery.

Example

Here, we are trying to select all records from the CUSTOMERS table where the AGE column matches any of the AGE associated with customers named “Khilan”.

SELECT * FROM CUSTOMERS
WHERE AGE = ANY 
(SELECT AGE FROM CUSTOMERS WHERE NAME = "Khilan");

Output

The age of ”khilan” is 25. Another customer whose age is equal to 25 is ”Chaitali”.

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00

ANY with “<>” Operator

We can use the MySQL ANY operator with “<>” (not equal to) comparison operator to fetch the records from a table where a column value is not equal to any value returned by a subquery.

Example

In this query, we are selecting all records from the CUSTOMERS table where the ADDRESS column does not match any of the addresses associated with customers named “Ramesh”.

SELECT * FROM CUSTOMERS
WHERE ADDRESS <> ANY 
(SELECT ADDRESS FROM CUSTOMERS WHERE NAME = "Ramesh");

Output

The address of ”Ramesh” is Ahmedabad. Following are the customers whose address is not equal to Ahmedabad.

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

ANY with “<=” Operator

The MySQL ANY operator returns true if a value is less than or equal to any value in a specified set when used with the “<=” comparison operator.

Example

Here, we are selecting all records from the CUSTOMERS table where the AGE column is less than or equal to any age value in the AGE column where the SALARY is equal to 10000.

SELECT * FROM CUSTOMERS
WHERE AGE <= ANY (SELECT AGE FROM CUSTOMERS WHERE SALARY = 10000);

output

The age of customer whose salary is 10000 is 24. So, the following are the customers whose age is less than 24 −

ID NAME AGE ADDRESS SALARY
3 Kaushik 23 Kota 2000.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

ANY with “>=” Operator

The MySQL ANY operator returns true if a value is greater than or equal to any value in a specified set when used with the “>=” comparison operator.

Example

In this query, we are selecting all records from the CUSTOMERS table where the ”AGE” is greater than or equal to any value in the result set obtained by selecting ”AGE” from ”CUSTOMERS” where ”SALARY” is equal to 10,000.

SELECT * FROM CUSTOMERS
WHERE AGE >= ANY 
(SELECT AGE FROM CUSTOMERS WHERE SALARY = 10000);

Output

The output for the program above is produced as given below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

ANY Operator Using a Client Program

Besides using MySQL queries to perform the ANY operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the ANY Operator on MySQL table through a PHP program, we need to execute SELECT statement with ANY operator using the mysqli function query() as follows −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME 
WHERE COLUMN_NAME OPERATOR ANY (SUBQUERY)";
$mysqli->query($sql);

To perform the ANY Operator on MySQL table through a Node.js program, we need to execute SELECT statement with ANY operator using the query() function of the mysql2 library as follows −

sql= " SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name operator ANY (subquery)";  
con.query(sql);

To perform the ANY Operator on MySQL table through a Java program, we need to execute SELECT statement with ANY operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name operator ANY (subquery)";
statement.executeQuery(sql);

To perform the ANY Operator on MySQL table through a Python program, we need to execute SELECT statement with ANY operator using the execute() function of the MySQL Connector/Python as follows −

any_query = SELECT column1, column2, ... FROM table_name 
WHERE column_name comparison_operator ANY (subquery);
cursorObj.execute(any_query);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM tutorials_tbl WHERE tutorial_author = ANY (SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_id > 3)";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",   
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("n");         
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 5, Title: Learn MySQL, Author: Abdul S, S_date 2023-07-26
Id 6, Title: Learn MySQL, Author: Mahesh, S_date 2023-07-26    

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

  //Connecting to MySQL
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");

   //Creating a Database
   sql = "create database TUTORIALS"
   con.query(sql);

   //Select database
   sql = "USE TUTORIALS"
   con.query(sql);

   //Creating table
   sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25) NOT NULL,SALARY DECIMAL(18, 2),PRIMARY KEY(ID));"
   con.query(sql);

   //Inserting Records
   sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',4000.00),(2,''Khilan'', 25, ''Kerala'', 8000.00),(3,''kaushik'', 23, ''Hyderabad'', 11000.00),(4,''Chaital'', 25, ''Mumbai'', 1200.00),(5,''Hardik'', 27, ''Vishakapatnam'', 10000.00),(6, ''Komal'',29, ''Vishakapatnam'', 7000.00),(7, ''Muffy'',24, ''Delhi'', 10000.00);"
   con.query(sql);

   //Using ANY Operator
   sql = "SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 29);"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Kerala'',
    SALARY: ''8000.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''11000.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''10000.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Delhi'',
    SALARY: ''10000.00''
  }
]      

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AnyOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 30)";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: Indore, Salary: 10000.00           

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
cursorObj = connection.cursor()
subquery = """SELECT SALARY FROM CUSTOMERS WHERE AGE = 29"""
any_query = f"""SELECT * FROM CUSTOMERS
WHERE SALARY > ANY ({subquery})"""
cursorObj.execute(any_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
   print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user