MySQL NOT Operator

MySQL NOT is a logical operator that allows us to exclude specific conditions or expressions from a WHERE clause. This operator is often used when we need to specify what NOT to include in the result table rather than what to include.

Suppose we take the example of the Indian voting system, where people under 18 are not allowed to vote. In such a scenario, we can use the NOT operator to filter out minors while retrieving information about all eligible voters. This helps us create an exception for minors and only display details of those who are eligible to vote.

The NOT operator is always used in a WHERE clause so its scope within the clause is not always clear. Hence, a safer option to exactly execute the query is by enclosing the Boolean expression or a subquery in parentheses.

Syntax

Following is the syntax of the NOT operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

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 uses INSERT statement to insert 7 records into the above-created 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 following query to fetch all the records from CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

In the following query, We are selecting all the records from the CUSTOMERS table where the ADDRESS is NOT “Hyderabad”.

SELECT * FROM Customers
WHERE NOT ADDRESS = ''Hyderabad

Output

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

NOT with IN Operator

We can use the MySQL logical NOT operator along with the IN keyword to eliminate the rows that match any value in a given list.

Example

Using the following query, we are fetching all the records from the CUSTOMERS table where NAME is NOT “Khilan”, “Chaital”, and “Muffy”.

SELECT * FROM CUSTOMERS
WHERE NAME NOT IN ("Khilan", "Chaital", "Muffy");

Output

If we execute the above query, the result is produced as follows −

NOT with IS NULL Operator

We can use the MySQL logical NOT operator along with the IS NULL keyword to select rows in a specified column that do not have a NULL value.

Example

In this query, we are selecting all the records from the CUSTOMERS table where the ADDRESS column is not null.

SELECT * FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;

Output

The output will be displayed as −

NOT with LIKE Operator

We can use the MySQL logical NOT operator along with the LIKE keyword to select the rows that do not match a given pattern.

Example

In the query below, we are fetching all the records from the CUSTOMERS table where the NAME column does not start with the letter K.

SELECT * FROM CUSTOMERS
WHERE NAME NOT LIKE ''K%

Output

The output will be displayed as −

NOT with BETWEEN Operator

MySQL”s NOT operator can be used with the BETWEEN keyword to return rows outside a specified range or interval of time.

Example

In the following example, we are selecting all the records from the CUSTOMERS table where the AGE is not between 25 and 30.

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 25 AND 30;

Output

When we execute the query above, the output is obtained as follows −

NOT with UPDATE Statement

The UPDATE statement in MySQL can be used along with the NOT operator in the WHERE clause to update rows that do not meet a specific condition.

Syntax

Following is the syntax of the NOT operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE NOT condition ... ;

Example

In the following query, we are updating the SALARY of the CUSTOMERS to a value of 12000 where the AGE is not between 25 and 30.

UPDATE CUSTOMERS
SET SALARY = 12000
WHERE AGE NOT BETWEEN 25 AND 30;

Output

The output will be displayed as −

Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Verification

Using the below query, we can verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

Output

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

NOT with DELETE Statement

The DELETE statement in MySQL can be used along with the NOT operator in the WHERE clause to delete rows that do not meet a specific condition.

Syntax

Following is the syntax of NOT operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE NOT condition ... ;

Example

In the following query, we are deleting records from the CUSTOMERS table where the SALARY is not between 10000 and 15000.

DELETE FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 10000 AND 15000;

Output

Query OK, 3 rows affected (0.01 sec)

Verification

Using the below query, we can verify whether the above operation is successful or not −

SELECT * FROM CUSTOMERS;

Output

On executing the given query, the output is displayed as follows −

NOT Operator Using a Client Program

Besides using MySQL queries to perform the NOT 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 −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE NOT condition";
$mysqli->query($sql);

sql=" SELECT column1, column2, ... FROM table_name
WHERE NOT condition";
con.query(sql);

String sql = "SELECT column1, column2, ... FROM table_name
WHERE NOT condition";
statement.executeQuery(sql);

not_query = SELECT column1, column2, ... FROM table_name
WHERE NOT condition"
cursorObj.execute(not_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 Customers WHERE NOT ADDRESS = ''Hyderabad";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Id 2, Name: Khilan, Age: 25, Address Kerala, Salary 8000.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 10000.000000
Id 6, Name: Komal, Age: 29, Address Vishakapatnam, Salary 7000.000000
Id 7, Name: Muffy, Age: 24, Address Delhi, Salary 10000.000000

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 CUSTOMERS table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), 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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using NOT Operator
  sql = "SELECT * FROM Customers WHERE NOT ADDRESS = ''Hyderabad"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotOperator {
  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 NOT ADDRESS = ''Hyderabad''";
            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();
    }
  }
}

Table records:
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.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()
not_query = f"""
SELECT * FROM Customers
WHERE NOT ADDRESS = ''Hyderabad
"""
cursorObj.execute(not_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))

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 −

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).

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” −

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”.

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.

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 −

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 −

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 −

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

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

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

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();

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)
   });
});

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();
    }
  }
}

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()

(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''))

MySQL AND Operator

In MySQL, there isn”t a built-in Boolean type. Rather, the Boolean values are represented using numeric data types, where zero is considered false and any non-zero value is considered true.

The MySQL AND operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL:

A AND B

Here, A and B are operands.

• The AND operator will return true (1) only if both A and B are non-zero and not Null.

• If either A or B is false, the AND operator will return false (0).

• If either A or B is NULL, the AND operator will return NULL.

The following table below demonstrates the possible outcomes of using the AND operator to combine true, false, and null values:

Example

The logical AND operator returns 1 if both A and B are non-zero and NOT NULL −

SELECT 1 AND 1;

Output

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

Example

The logical AND operator returns 0 if either A or B is zero, or if both A and B are zero.

SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL;

Output

When we execute the above query, the output is obtained as follows −

Example

The logical AND operator returns NULL if at least one operand is non-zero or both operands are NULL −

SELECT 1 AND NULL, NULL AND NULL;

Output

On executing the given query, the output is displayed as follows −

AND Operator with WHERE

The MySQL AND operator can be used with the WHERE clause to retrieve only the rows that meet all the specified conditions. When the AND operator is used, both conditions must be true for a row to be included in the result set. Else, it returns an empty set.

Syntax

Following is the syntax of the AND operator with WHERE clause in MySQL −

SELECT column1, column2, ..., columnN
FROM table_name
[WHERE condition1 AND condition2 AND condition3 ...;]

Example

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

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

The following query inserts 7 rows into the above-created 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 following query to fetch all the records from the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us select all the columns from the CUSTOMERS table where the ADDRESS is ”Hyderabad” and AGE is 22.

SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Hyderabad" AND AGE = 22;

Output

On executing the given query, the output is displayed as follows −

Example

The logical AND operator returns the records only if all the conditions separated by AND are true.

In the following query, we are providing a false value to one of the AND operands.

SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Kerala" AND AGE = 27;

Output

As the ADDRESS column in the CUSTOMERS table doesn”t contain the value ”Kerala”, it returns an empty set as an output.

Empty set (0.00 sec)

Multiple AND Operators

In MySQL, we can use multiple AND operators in a query to combine multiple conditions or expressions together. Conditions combined with these multiple ”AND” operators are evaluated from left to right. If any of the conditions evaluate to false, the entire condition will be false and the record will not be included in the result set.

Example

In the following query, we are selecting all records from the CUSTOMERS table where the NAME starts with “k”, AGE is greater than or equal to 22, and SALARY is less than 3742.

SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''k%'' AND AGE >= 22 AND SALARY < 3742;

Output

When we execute the program above, the output is obtained as follows −

AND with UPDATE statement

In MySQL, we can use the AND operator in an UPDATE statement to update records from a table based on provided multiple conditions.

Syntax

Following is the syntax of the AND operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 AND condition2 AND ...;

Example

In the following query, we are updating the SALARY of CUSTOMERS whose ID is 5 and ADDRESS is ”Hyderabad” −

UPDATE CUSTOMERS
SET SALARY = 15000
WHERE ID = 6 AND ADDRESS = "Hyderabad";

Output

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

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Using the below query, we can verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

As we can see the CUSTOMERS table below, the salary of customer with ID 5 has updated −

AND with DELETE Statement

In MySQL, we can use the AND operator in a DELETE statement to remove records from a table based on multiple conditions.

Syntax

Following is the syntax of AND operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE condition1 AND condition2 AND condition3 ...

Example

In this query, we are deleting records from the CUSTOMERS table where the NAME is equal to ”Khilan” and ADDRESS is equal to ”Delhi” −

DELETE FROM CUSTOMERS
WHERE NAME = "Khilan" AND ADDRESS = "Delhi";

Output

Query OK, 1 row affected (0.01 sec)

Verification

Using the below query, we can verify whether the above operation is successful or not −

SELECT * FROM CUSTOMERS;

Output

As we can see the output below, the customer name with ”khilan” and address ”delhi” has been deleted successfully −

AND Operator Using a Client Program

Besides using MySQL queries to perform the AND 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 −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE CONDITION1 AND CONDITION2 AND CONDITION3...";
$mysqli->query($sql);

sql= " SELECT column1, column2, ..., columnN FROM table_name 3
[WHERE condition1 AND condition2 AND condition3 ...]";
con.query(sql);

String sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
[WHERE CONDITION1 AND CONDITION2 AND CONDITION3...]";
statement.executeQuery(sql);

and_query = "SELECT column1, column2, ... FROM table_name
WHERE condition1 AND condition2 AND ..."
cursorObj.execute(and_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_id > 2 AND tutorial_id 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();

Table records:
Id 3, Title: JAVA Tutorial, Author: Sanjay, S_date 2007-05-21
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

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), 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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT * FROM CUSTOMERS WHERE ADDRESS = ''Vishakapatnam'' AND AGE = 27;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Andoperator {
  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 ADDRESS = "Vishakapatnam" AND AGE = 27";
            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 + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records:
Id: 5, Name: Hardik, Age: 27, Address: Vishakapatnam, Salary: 8500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Query to retrieve tutorials
and_query = f"""SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Vishakapatnam" AND AGE = 27"""
cursorObj.execute(and_query)
# Fetching all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Print the filtered rows
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))

MySQL OR Operator

MySQL does not have a built-in Boolean data type. Instead, Boolean values are represented using numeric data types, where zero is used as false and any non-zero value is used as true.

The MySQL OR operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL:

A AND B

Here, A and B are operands.

• The OR operator will return true (1) only if either A or B, or both, is non-zero and not Null.

• If both A and B are false, the OR operator will return false (0).

• If either A or B is NULL, the OR operator will return NULL.

The following table below demonstrates the possible outcomes of using the OR operator to combine true (1), false (0), and null values:

Example

The logical OR operator will return true (1) if both A and B are not NULL, and if either A or B is non-zero.

SELECT 1 OR 1, 1 OR 0, 0 OR 1;

Output

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

Example

The OR operator returns false (0) if both A and B are false (0).

SELECT 0 OR 0;

Output

When we execute the above query, the output is obtained as follows −

Example

If A is true (1) and B is NULL, the OR operator will return 1.

If A is false (0) and B is NULL, the OR operator will return NULL.

If both A and B are NULL, the OR operator will return NULL.

SELECT 1 OR NULL, 0 OR NULL, NULL or NULL;

Output

On executing the given program, the output is displayed as follows −

OR operator with WHERE

MySQL”s logical OR operator can be used along with the WHERE clause to return the rows that meet any of the specified conditions.

When the OR operator is used, at least one of the conditions must be true for a row to be included in the result set. If none of the conditions are true, an empty set is returned.

Syntax

Following is the syntax of the OR operator with WHERE clause in MySQL −

SELECT column1, column2, ..., columnN
FROM table_name
[WHERE condition1 OR condition2 OR condition3 ...;]

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 INSERT INTO statement adds 7 records into the above-created 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 following query to retrieve all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are selecting all the columns from the CUSTOMERS table where SALARY is greater than 5000 or ADDRESS = “Hyderabad”.

SELECT * FROM CUSTOMERS
WHERE SALARY > 5000
OR ADDRESS = "Hyderabad";

Output

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

Example

The logical OR operator returns the records only if either of the conditions separated by OR is true.

In the following query, we are providing false values to both operands of the OR operator.

SELECT * FROM CUSTOMERS
WHERE NAME = "Mahesh" OR AGE = 42;

Output

As there are no records present in the CUSTOMERS table with NAME “Mahesh” or AGE is 42, it returns an empty set as an output.

Empty set (0.00 sec)

Multiple OR Operators

We can use MySQL”s logical OR operator multiple times to combine multiple conditions. By using multiple OR operators, any rows that meet at least one of the conditions will be included in the result set.

Example

In the following query, we are returning all the records from the CUSTOMERS table where the NAME of the customer ends with ”k”, or SALARY is greater than 5000, or AGE is less than 25.

SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''%k'' OR SALARY > 5000 OR AGE < 25;

Output

On executing the given query, the output is displayed as follows −

OR with UPDATE statement

The MySQL”s logical OR operator can be used along with the UPDATE statement to update records of a table based on multiple conditions.

Syntax

Following is the syntax of the OR operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 OR condition2 OR ...;

Example

In the following query, we are updating the SALARY of CUSTOMERS whose ADDRESS is ”Hyderabad” or whose age is greater than 26

UPDATE CUSTOMERS
SET SALARY = 15000
WHERE ADDRESS = "Hyderabad" OR AGE > 26;

Output

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

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

Execute the below query to verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

Output

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

OR with DELETE Statement

The MySQL”s logical OR operator can be used along with the DELETE statement to remove records from a table based on multiple conditions.

Syntax

Following is the syntax of OR operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE condition1 OR condition2 OR condition3 ...

Example

In the following query, we are trying to DELETE records from the CUSTOMERS table where the age is less than 25 or the SALARY is less than or equal to 10000.

DELETE FROM CUSTOMERS
WHERE AGE < 25 OR SALARY <= 10000;

Output

Query OK, 5 rows affected (0.01 sec)

Verification

Execute the following query to verify whether the above operation is successful or not −

OR Operator Using a Client Program

Besides using MySQL queries to perform the OR 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 −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE CONDITION1 OR CONDITION2 OR CONDITION3...";
$mysqli->query($sql);

sql= " SELECT column1, column2, ..., columnN FROM table_name 3
[WHERE condition1 OR condition2 OR condition3 ...]";
con.query(sql);

String sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
[WHERE CONDITION1 OR CONDITION2 OR CONDITION3...]";
statement.executeQuery(sql);

or_query = "SELECT column1, column2, ... FROM table_name
WHERE condition1 OR condition2 OR ..."
cursorObj.execute(or_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_id = 2 OR tutorial_id = 4 OR tutorial_id = 6
$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();

Table records:
Id 2, Title: PHP Tut, Author: New Author, S_date 2023-08-12
Id 4, Title: Learn PHP, Author: John Poul, 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), 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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using OR Operator
  sql = "SELECT * FROM CUSTOMERS WHERE SALARY > 5000 OR ADDRESS = ''Vishakapatnam"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrOperator {
  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 > 5000 OR ADDRESS = "Vishakapatnam"";
            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 + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records:
Id: 4, Name: Chaital, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Query to retrieve tutorials
or_query = f"""SELECT * FROM CUSTOMERS
WHERE SALARY > 5000 OR ADDRESS = "Vishakapatnam""""
cursorObj.execute(or_query)
# Fetch all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Print the filtered rows
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

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

MySQL In Operator

The IN operator in MySQL is a logical operator that allows us to check whether the values in a database are present in a list of values specified in the SQL statement.

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. It returns all rows in which the specified column value matches any one of the values in the list.

The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions.

In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. You can use IN clause to replace many OR conditions

Syntax

Following is the basic syntax of IN operator −

WHERE COLUMN_NAME IN (value1, value2, value3,....);

Example

To understand IN clause, let us first create a table named CUSTOMERS, using the following CREATE TABLE statement −

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

Now, insert the following records using the INSERT statement −

INSERT INTO CUSTOMERS 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);

The table will be created as follows −

Using the following query, retrieve the records with the names ‘Khilan’, ‘Hardik’, ‘Muffy’, from the CUSTOMERS table −

SELECT * FROM CUSTOMERS
WHERE NAME IN (''Khilan'', ''Hardik'', ''Muffy'');

Output

The output obtained is as follows −

The IN Operator in UPDATE statement

The MySQL UPDATE statement is used to modify existing data in a database table. So, we can also use the IN operator in an UPDATE statement (as a filter) to update existing rows.

Example

In this example, let us update the records of the customers with age ‘25’ or ‘27’ by setting their value to ‘30’ −

UPDATE CUSTOMERS
SET AGE = 30 WHERE AGE IN (25, 27);

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Use the following query to display the updated records in the CUSTOMERS table −

MySQL NOT IN operator

To negate a condition, we use the NOT operator. The MySQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause.

In other words, the absence of a list from an expression will be checked.

Syntax

Following is the basic syntax of NOT IN operator −

WHERE column_name NOT IN (value1, value2,...);

Example

Now, we are trying to display all the records from the CUSTOMERS table, where the AGE is NOT equal to ”25”, ”23” and ”22” −

SELECT * FROM CUSTOMERS
WHERE AGE NOT IN (25, 23, 22);

Output

The output is obtained as −

Comparing Values Using IN Operator

We can also use the IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.

Example

In the below query, we are trying to select the rows with the values containing SALARY column −

SELECT * FROM CUSTOMERS
WHERE 2000 IN (SALARY);

Output

The following output is obtained −

MySQL Subquery with IN operator

We can use a subquery with the IN operator to return records from a single column. This means that more than one column in the SELECT column list cannot be included in the subquery specified.

Syntax

The basic syntax of the IN operator to specify a query is as follows −

WHERE column_name IN (subquery);

Example

In the query given below we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −

SELECT * FROM CUSTOMERS
WHERE NAME IN (
   SELECT NAME FROM CUSTOMERS
   WHERE SALARY > 2000
);

Output

The following output is obtained −

In Operator Using Client Program

We can execute IN operator using a client program, in addition to executing it directly in the MySQL server.

Syntax

Following are the syntaxes of the IN Operator using various programming languages −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME
WHERE COLUMN_NAME IN (VALUE1, VALUE2, ...)";
$mysqli->query($sql);

sql= "SELECT columns FROM table_name
WHERE column_name IN (value1, value2, value3, ...)";
con.query(sql);

String sql = "SELECT COLUMN(S) FROM TABLE_NAME
WHERE COLUMN_NAME IN(VALUE1, VALUE2, VALUE3,...)";
statement.executeQuery(sql);

in_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name IN (value1, value2, value3, ...)"
cursorObj.execute(in_query);

Example

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

$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 IN(''John'', ''Sanjay'', ''Mahesh'')";
$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();

Table records:
Id 3, Title: JAVA Tutorial, Author: Sanjay, S_date 2007-05-21
Id 4, Title: Learn PHP, Author: John Poul, 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 IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using IN Operator
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages IN ( 250, 220, 170 );"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    id: 1,
    name: ''John'',
    work_date: 2007-01-23T18:30:00.000Z,
    daily_typing_pages: 250
  },
  {
    id: 2,
    name: ''Ram'',
    work_date: 2007-05-26T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 3,
    name: ''Jack'',
    work_date: 2007-05-05T18:30:00.000Z,
    daily_typing_pages: 170
  },
  {
    id: 4,
    name: ''Jill'',
    work_date: 2007-04-05T18:30:00.000Z,
    daily_typing_pages: 220
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InOperator {
    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 = "CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID))";
            st.execute(sql);
            System.out.println("Table created successfully...!");
            //now lets insert some records
            String sql1 = "INSERT INTO CUSTOMERS 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, ''MP'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //display records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records before update: ");
            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);
            }
            //let use IN operator with where clause to update records
            String sql3 = "UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27)";
            st.executeUpdate(sql3);
            //display table records after update
            String sql4 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("Table records before update: ");
            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();
        }
    }
}

Table created successfully...!
Records inserted successfully....!
Table records before update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, 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
Table records before update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.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''
)
# Creating a cursor object
cursorObj = connection.cursor()
in_query = f"""SELECT * FROM customers
WHERE id IN (2, 4, 6)"""
cursorObj.execute(in_query)
# Fetching all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))

MySQL GROUP BY Clause

The GROUP BY clause in MySQL is used to arrange identical data in a table into groups.

For example, let us suppose you have a table of sales data of an organization consisting of date, product, and sales amount. To calculate the total sales in a particular year, the GROUP BY clause can be used to group the sales of products made in that year. Similarly, you can group the data by date to calculate the total sales for each day, or by a combination of product and date to calculate the total sales for each product on each day.

This GROUP BY clause follows the WHERE clause in an SQL statement and precedes the ORDER BY or HAVING clause (if they exist). You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. You can use COUNT, SUM, AVG, etc., functions on the grouped column.

Syntax

Following is the basic syntax to use GROUP BY with SELECT statement −

SELECT column_name(s) FROM table_name
GROUP BY [condition | column_name(s)];

Example

This example demonstrates how to use aggregate functions with GROUP BY clause.

First of all, create a table named CUSTOMERS, using the following CREATE TABLE query −

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

Now, insert the following records into the CUSTOMERS table using the following INSERT statement −

INSERT INTO CUSTOMERS 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);

The table is created as follows −

Now, use the following GROUP BY query to group the customers based on their age −

SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

Output

Following is the result produced −

MySQL GROUP BY on Single Column

When we use the GROUP BY clause on a single column, all common values in that column will be added together making it a single record.

Example

In this example, let us group the customers by their age and calculate the average salary for each age using the following query −

SELECT AGE, AVG(SALARY) AS AVG_SALARY
FROM CUSTOMERS
GROUP BY AGE;

Output

This would produce the following result −

SELECT CONCAT(AGE, '' - '', SALARY) AS SALARY_AGEWISE
FROM CUSTOMERS
GROUP BY AGE, SALARY;

SELECT column1, column2, ..., aggregate_function(columnX) AS alias
FROM table
GROUP BY column1, column2, ...
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

SELECT AGE, MAX(salary) AS MAX_SALARY
FROM CUSTOMERS
GROUP BY AGE
ORDER BY MAX(salary) DESC;

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

SELECT AGE, AVG(SALARY) AS AVG_SALARY
FROM CUSTOMERS
GROUP BY AGE HAVING AVG(salary) > 8000;

$sql = "SELECT EXPRESSION1, EXPRESSION2, ... EXPRESSION_N,
   AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME
   [WHERE CONDITION] GROUP BY EXPRESSION1, EXPRESSION2..";
$mysqli->query($sql,$resultmode)

sql= " SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)];"
Con.query(sql);

String sql = "SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)]";
statement.executeQuery(sql);

group_by_clause_query = "SELECT column_name(s) aggregate_function(column)
   FROM table_name GROUP BY column_name(s)"
cursorObj.execute(group_by_clause_query)

$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 tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 2 GROUP BY tutorial_title
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Title: %s, Count: %d",
         $row["tutorial_title"],
         $row["tot_count"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Title: JAVA Tutorial, Count: 1
Title: Learn PHP, Count: 1
Title: Learn MySQL, Count: 2

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 IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using GROUP BY Clause
  sql = "SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  { name: ''John'', ''COUNT(*)'': 1 },
  { name: ''Ram'', ''COUNT(*)'': 1 },
  { name: ''Jack'', ''COUNT(*)'': 2 },
  { name: ''Jill'', ''COUNT(*)'': 1 },
  { name: ''Zara'', ''COUNT(*)'': 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class GroupByClause {
	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 AGE, AVG(SALARY) as avg_salary FROM CUSTOMERS GROUP BY age";
            rs = st.executeQuery(sql);
            System.out.println("Table records(gruop by age): ");
            while(rs.next()){
            	String age = rs.getString("Age");
            	String avg_salary = rs.getString("avg_salary");
            	System.out.println("Age: " + age + ", Salary: " + avg_salary);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Table records(gruop by age):
Age: 32, Salary: 2000.000000
Age: 25, Salary: 4000.000000
Age: 23, Salary: 2000.000000
Age: 27, Salary: 8500.000000
Age: 22, Salary: 4500.000000
Age: 24, Salary: 10000.000000

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
group_by_clause_query = """
SELECT ADDRESS, AVG(SALARY) AS average_salary
FROM customers
GROUP BY ADDRESS
"""
cursorObj.execute(group_by_clause_query)
# Fetching all the grouped rows
grouped_rows = cursorObj.fetchall()
for row in grouped_rows:
    address, average_salary = row
    print(f"Address: {address}, Average Salary: {average_salary}")
cursorObj.close()
connection.close()

Address: Hyderabad, Average Salary: 7500.000000
Address: Kerala, Average Salary: 8000.000000
Address: Mumbai, Average Salary: 1200.000000
Address: Vishakapatnam, Average Salary: 8500.000000
Address: Delhi, Average Salary: 10000.000000

MySQL Having Clause

The MySQL HAVING Clause is used to filter grouped rows in a table based on conditions.

This clause is used with the GROUP BY clause to group the rows based on one or more columns and then filter them based on the conditions specified in the HAVING clause. So, the HAVING clause must always be followed by the GROUP BY clause.

The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc.

This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions.

Syntax

Following is the basic syntax of the HAVING clause in MySQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ...;

Example

Let us begin with creating a table named CUSTOMERS using the following query −

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

The following INSERT statement inserts 7 records into the above-created 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 );

Using the following query, we can verify whether the CUSTOMERS table is created or not −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

HAVING clause with ORDER BY clause

In MySQL, the HAVING clause filters the groups, and the ORDER BY clause sorts the results. When we used both of them together, HAVING is executed first, then the result set is sorted according to the ORDER BY criteria.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table where the sum of their SALARY is less than 4540, ordered by their name in ascending order −

SELECT NAME, SUM(SALARY) as total_salary
FROM CUSTOMERS
GROUP BY NAME
HAVING SUM(SALARY) < 4540
ORDER BY NAME;

Output

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

HAVING clause with COUNT() function

We can use the MySQL HAVING clause in conjunction with the COUNT() function to filter the groups based on the number of rows they contain.

Example

In this query, we are fetching a record where the count of similar age is greater than or equal to 2.

SELECT AGE
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

Output

There are two records in CUSTOMERS table with age 25, thus the output is 25 −

HAVING clause with AVG() function

The MySQL HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

Example

In the following query, we are trying to return the names of the customers whose salary is greater than 3000 −

SELECT NAME, AVG(salary) as avg_salary
FROM customers
GROUP BY NAME
HAVING AVG(salary) > 3000;

Output

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

HAVING clause with MAX() function

In MySQL, we can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

In this query, we are retrieving the customer names whose maximum SALARY is less than 4000 −

SELECT NAME, MAX(salary) as max_salary
FROM customers
GROUP BY NAME
HAVING MAX(salary) < 4000;

Output

On executing the given query, the output is displayed as follows −

Having Clause Using a Client Program

Besides using MySQL HAVING clause to filter grouped rows in a table based on conditions, 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 −

$sql = "SELECT EXPRESSION1, EXPRESSION2, ...EXPRESSION_N,
AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME
[WHERE CONDITION] GROUP BY EXPRESSION1,
EXPRESSION2.. EXPRESSION_N HAVING CONDITION";
$mysqli->query($sql);

sql= " SELECT column1, column2, aggregate_function(column)
FROM table_name GROUP BY column1, column2, ...
HAVING condition ORDER BY column1, column2, ...";
con.query(sql);

String sql = "SELECT column1, column2, aggregate_function(column)
FROM table_name GROUP BY column1, column2, ...
HAVING condition ORDER BY column1, column2, ...";
statement.executeQuery(sql);

having_clause_query = "SELECT column1, column2, aggregate_function(column)
FROM table_name GROUP BY column1, column2 HAVING condition"
cursorObj.execute(having_clause_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 tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 1 GROUP BY tutorial_title HAVING count(tutorial_id) > 1
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Title: %s, Count: %d",
         $row["tutorial_title"],
         $row["tot_count"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Title: Learn MySQL, Count: 2

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,GENDER VARCHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS VALUES(1, ''Ramesh'', 25, ''Male'', 2000.00),(2, ''Ramesh'', 25, ''Male'', 1500.00),(3, ''kaushik'', 25, ''Female'', 2000.00),(4, ''kaushik'', 20, ''Male'', 6500.00),(5, ''Hardik'', 25, ''Male'', 8500.00),(6, ''Komal'', 20, ''Female'', 4500.00),(7, ''Muffy'', 25, ''Male'', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY)
   
Output
   
The output produced is as follows −
Connected!
--------------------------
[
  { NAME: ''Komal'', total_salary: ''4500.00'' },
  { NAME: ''Ramesh'', total_salary: ''3500.00'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HavingClause {
  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 NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY)
   
Output
   
The output obtained is as shown below −
Table records:
Name: Komal, Total_Salary: 4500.00
Name: Ramesh, Total_Salary: 3500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
having_clause_query = """SELECT ADDRESS, SUM(SALARY) as total_salary FROM CUSTOMERS
GROUP BY ADDRESS HAVING SUM(SALARY)
Output
Following is the output of the above code −
(''Mumbai'', Decimal(''1200.00''))

MySQL DISTINCT clause

The DISTINCT clause in MySQL is used with a SELECT statement to return the distinct values (unique values) from a single or multiple of columns in a table. It ignores all the duplicates values present in the particular column(s) and returns only the distinct values.

We can use this clause in various scenarios, such as identifying unique customer names, unique customer id”s, etc. It can be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to filter the data further.

Syntax

Following is the syntax of the DISTINCT clause in MySQL −

SELECT DISTINCT column1, column2, ..., columnN
FROM table_name
WHERE conditions // optional

Where,

• (column1, column2,…,columnN) are the columns from which we want the distinct (unique) values.

• table_name is the name of the table from which we want to select data.

• WHERE conditions is optional. These are used to filter the data.

Example

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

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

The following INSERT INTO statement adds 7 records into the above-created table −

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

Execute the below query to display all the inserted records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us retrieve the ADDRESS column from CUSTOMERS table without using the DISTINCT clause.

SELECT ADDRESS FROM CUSTOMERS;

Duplicate values are not ignored in the ADDRESS column.

Here, we are using the DISTINCT clause on the ADDRESS column −

SELECT DISTINCT ADDRESS FROM CUSTOMERS;

Output

As we can see in the output below, duplicate values are ignored in the ADDRESS column.

DISTINCT Clause with COUNT() Function

The MySQL count() function allows us to count the number of distinct values present in one or more columns of a table. Let us understand with the example below

Example

In the following query, we are using the MySQL COUNT() function to count the DISTINCT records in ADDRESS column of CUSTOMERS table −

SELECT COUNT(DISTINCT ADDRESS) FROM CUSTOMERS;

Output

There are 5 distinct records present in the ADDRESS column.

Example

In this query, we are retrieving unique SALARY records from the CUSTOMERS table where the ADDRESS is “Hyderabad”.

SELECT DISTINCT SALARY FROM CUSTOMERS WHERE ADDRESS = "HYDERABAD";

Output

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

DISTINCT on Multiple Columns

We can use the MySQL DISTINCT keyword on multiple columns of a table to return all the unique combinations of values across those columns, i.e. removing redundant records in a table.

Example

In the following query, we are retrieving the distinct combinations of ADDRESS and SALARY columns from the CUSTOMERS table and orders the result set by the ADDRESS column in ascending order.

SELECT DISTINCT ADDRESS, SALARY FROM CUSTOMERS ORDER BY ADDRESS;

Output

As we can see in the output below, the duplicate values “Hyderabad” and “Vishakapatnam” appears twice in the result set because each combination of Hyderabad and Vishakapatnam with SALARY is unique.

DISTINCT with NULL values

If there are NULL values present in a specific column, the MySQL DISTINCT will treat them as unique values and includes them in the result set.

Example

Here, we are returning the distinct salary of the customers using the following query −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

On executing the given program, the output is displayed as follows −

Distinct Clause Using a Client Program

In addition to fetch distinct records from a table with a MySQL query, you can also use a client program to perform the DISTINCT operation.

Syntax

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

$sql = "SELECT DISTINCT EXPRESSION FROM TABLE_NAME [WHERE CONDITION]";
$mysqli->query($sql);

sql = "SELECT DISTINCT column1, column2, ..., columnN FROM table_name";
con.query(sql);

String sql = "SELECT DISTINCT column1, column2, ..., columnN
FROM table_name WHERE conditions // optional";
statement.executeQuery(sql);

distinct_clause_query = "SELECT DISTINCT column1, column2, ...FROM table_name"
cursorObj.execute(distinct_clause_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 DISTINCT tutorial_title FROM tutorials_tbl where tutorial_id > 2
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Title: %s ",
         $row["tutorial_title"], "n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Title: JAVA Tutorial Title: Learn PHP Title: Learn MySQL

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), 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'', NULL),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', NULL),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using DISTINCT operator
  sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  { ADDRESS: ''Hyderabad'' },
  { ADDRESS: ''Delhi'' },
  { ADDRESS: ''Mumbai'' },
  { ADDRESS: ''Vishakapatnam'' },
  { ADDRESS: ''Indore'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DistinctClause {
  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 DISTINCT ADDRESS FROM CUSTOMERS";
            rs = st.executeQuery(sql);
            System.out.println("Distinct records: ");
            while(rs.next()){
              String Address = rs.getString("Address");
              System.out.println("Address: " + Address);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Distinct records:
Address: Ahmedabad
Address: Delhi
Address: Kota
Address: Mumbai
Address: Bhopal
Address: MP
Address: Indore

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
distinct_clause_query = """
SELECT DISTINCT ADDRESS
FROM customers
"""
cursorObj.execute(distinct_clause_query)
# Fetch all the distinct addresses
distinct_addresses = cursorObj.fetchall()
# Printing the distinct addresses
for address in distinct_addresses:
    print(f"Address: {address[0]}")
cursorObj.close()
connection.close()

Address: Hyderabad
Address: Kerala
Address: Mumbai
Address: Vishakapatnam
Address: Delhi

MySQL ORDER BY Clause

The MySQL ORDER BY clause is used to sort one or more columns of a table in provided order that can be either ascending or descending order. By default, it sorts the column(s) in ascending order if the sort order is not specified.

The sort is specified with two keywords; ASC for ascending order and DESC for descending order.

Using the ORDER BY clause, we can sort multiple columns of a table and provide different sort orders for each column. For instance, we can sort the result set first by one column, and then by another column to the first column, and so on.

Syntax

Following is the syntax of ORDER BY clause in MySQL −

SELECT column-list
FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]

Here,

• column-list are the names of the columns that we want to retrieve from the table_name.

• column1, column2,…columnN are the column(s) that we want to order (sort).

• ASC will sort the columns in ascending order.

• DESC will sort the columns in descending order.

By default, the ORDER BY clause sorts the provided column in Ascending order.

Example

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

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   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 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 following query to verify whether the CUSTOMERS table is created or not −

Select * from CUSTOMERS;

The CUSTOMERS table has been created successfully −

Now, let us display all the columns from the CUSTOMERS table, sorted by the NAME column −

By default, the ORDER BY clause sorts the provided column in Ascending order.

SELECT * FROM CUSTOMERS
ORDER BY NAME;

Output

As we can see in the output below, the NAME column is sorted in Ascending order.

ORDER BY with DESC

We can sort a particular column of a table in descending order by using the ORDER BY clause along with the DESC keyword. Let us understand with the following example.

Example

In the following query, we are displaying all the columns from the CUSTOMERS table, sorted by the NAME column in descending order −

SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;

Output

As we can see in the output below, the NAME column is sorted in descending order.

ORDER BY with Multiple Columns

We can also sort multiple columns of a MySQL table. To do so, we need to specify all the column names in the ORDER BY clause.

Example

Here, we are selecting all the columns from the CUSTOMERS table, sorted by the ADDRESS and NAME columns.

SELECT * FROM CUSTOMERS
ORDER BY ADDRESS, NAME;

Output

The above query first sorts the ADDRESS column in ascending order, and for any rows that have the same ADDRESS value, they will be sorted by the NAME column in ascending order.

This means, all the rows with the same ADDRESS value will be grouped together and sorted by NAME.

ORDER BY with ASC and DESC

In MySQL, we can order the columns with ASC and DESC in the same query. The column provided first with ASC will be sorted in Ascending order and the column provided second with DESC will be sorted in descending order.

Example

In this query, we are selecting all the columns from the CUSTOMERS table, sorted ascending by the AGE and descending by the SALARY column −

SELECT * FROM CUSTOMERS
ORDER BY AGE ASC, SALARY DESC;

Output

On executing the given program, the output is displayed as follows −

ORDER BY with LENGTH()

We can use the LENGTH() function with the ORDER BY clause in MySQL to sort the values present in a particular column based on the length.

Example

Using the following query, we are sorting the ADDRESS column based on the length −

SELECT * FROM CUSTOMERS
ORDER BY LENGTH(ADDRESS) ASC;

Output

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

Order By Clause Using a Client Program

Besides using MySQL ORDER BY clause to sort one or more columns of a table, 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 −

$sql = "SELECT COLUMN1, COLUMN2, .. FROM TABLE_NAME
ORDER BY COLUMN1, COLUMN2, ... ASC|DESC";
$mysqli->query($sql);

sql= SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
con.query(sql);

String sql = "SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]";
statement.executeQuery(sql);

order_by_clause_query = SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
cursorObj.execute(order_by_clause_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 ORDER BY tutorial_author ASC
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records based on ''tutorial_author'' in ascending order: n");
   while($row = $result->fetch_assoc()) {
      printf("ID %d, Title: %s, Author: %s ",
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records based on ''tutorial_author'' in ascending order:
ID 5, Title: Learn MySQL, Author: Abdul S
ID 4, Title: Learn PHP, Author: John Poul
ID 2, Title: PHP Tut, Author: New Author
ID 1, Title: Java Tutorial, Author: new_author
ID 3, Title: JAVA Tutorial, Author: Sanjay

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), 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'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using ORDER BY Clause
  sql = "SELECT * FROM CUSTOMERS ORDER BY NAME;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  },
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrderByClause {
  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 ORDER BY NAME DESC";
            rs = st.executeQuery(sql);
            System.out.println("Table records(in DESC order): ");
            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 + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records(in DESC order):
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
order_by_clause_query = """
SELECT *
FROM customers
ORDER BY AGE ASC
"""
cursorObj.execute(order_by_clause_query)
# Fetch all the ordered rows
ordered_rows = cursorObj.fetchall()
# Printing the ordered rows
for row in ordered_rows:
    print(row)
cursorObj.close()
connection.close()

(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))
(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))