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

MySQL Exists Operator

The EXISTS operator in MySQL checks for the existence of a record in a table. It”s used in the WHERE clause of a SELECT statement to verify if a subquery returns any rows. It returns TRUE if the subquery returns at least one record, else false.

We can also use the operator with the SQL statements such as SELECT, INSERT, UPDATE, and DELETE to verify the existence of the records in subqueries.

Syntax

Following is the syntax of the EXISTS operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

Example

Before performing the EXISTS operator, let us first two different tables named CUSTOMERS and CARS. Here, we are creating the CUSTOMERS table −

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 INTO statement to add 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 present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Let us create another table named CARS, which contains the details such as ID of the customer, NAME and PRICE of the car −

CREATE TABLE CARS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   PRICE INT NOT NULL,
   PRIMARY KEY (ID)
);

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

INSERT INTO CARS (ID, NAME, PRICE) VALUES
(2, ''Maruti Swift'', 450000),
(4, ''VOLVO'', 2250000),
(7, ''Toyota'', 2400000);

Execute the below query to fetch all the records present in the CARS table −

SELECT * FROM CARS;

Following is the CARS table −

EXISTS operator with SELECT statement

The SELECT statement in MySQL is used to retrieve data from one or more tables. The EXISTS operator can be used with the SELECT statement to check if rows exist that match a specific condition.

Example

Now, let us fetch the list of the customers with the price of the car greater than 2,000,000 −

SELECT * FROM CUSTOMERS
WHERE EXISTS
(SELECT PRICE FROM CARS
WHERE CARS.ID = CUSTOMERS.ID
AND PRICE > 2000000);

Output

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

EXISTS Operator with UPDATE statement

The MySQL EXISTS operator can be used with the UPDATE statement to update the rows in a table based on the existence of rows matching in another table.

Example

In this query, we are using the EXISTS operator to UPDATE the name ”Kushal” to all of the customers whose ID is equal to the ID of the CARS table −

UPDATE CUSTOMERS
SET NAME = ''Kushal''
WHERE EXISTS
(SELECT NAME FROM CARS
WHERE CUSTOMERS.ID = CARS.ID);

Output

As we can observe the output, 3 rows have been modified −

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

Verification

To verify whether the changes are reflected in the CUSTOMERS table, execute the following query −

SELECT * FROM CUSTOMERS;

The CUSTOMERS table is displayed as follows −

EXISTS Operator with DELETE statement

The MySQL EXISTS operator is used with the DELETE statement to delete the rows in a table based on the existence of rows returned by a subquery.

Example

Here, we are deleting all the records from the CUSTOMERS table whose ID is equal to the ID in the CARS table having a price equal to 2,250,000 −

DELETE FROM CUSTOMERS
WHERE EXISTS
(SELECT * FROM CARS
WHERE CARS.ID = CUSTOMERS.ID
AND CARS.PRICE = 2250000);

Output

As we can observe the output, 1 row has been deleted −

Query OK, 1 row affected (0.00 sec)

Verification

We can verify whether the changes have been reflected in the CUSTOMERS table using the following query −

SELECT * FROM CUSTOMERS;

Output

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

NOT Operator with EXISTS Operator

If we use the NOT with EXISTS operator in MySQL, it will select records from one table that do not exist in another table.

Syntax

Following is the syntax of the NOT EXISTS operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE NOT EXISTS (subquery);

Example

In the following query, we are fetching the NAME of the customers who have not bought any car −

SELECT * FROM CUSTOMERS
WHERE NOT EXISTS
(SELECT * FROM CARS
WHERE CUSTOMERS.ID = CARS.ID);

Output

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

Exists Operator Using a Client Program

In addition to verify whether a particular record exists in a MySQL table with a MySQL query, you can also use a client program to perform the EXISTS operation.

Syntax

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

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

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

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

exists_query = "SELECT column1, column2, ... FROM table_name
WHERE EXISTS (subquery)"
cursorObj.execute(exists_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 EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);";
$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 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.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'',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);

  //Creating CARS table
  sql = "CREATE TABLE CARS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,PRICE INT NOT NULL,PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CARS VALUES(2, ''Maruti Swift'', 450000),(4, ''VOLVO'', 2250000),(7, ''Toyota'', 2400000);"
  con.query(sql);

  //Using EXISTS Operator
  sql = "SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''1200.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 ExistsOperator {
  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 EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000)";
            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: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.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()
exists_query = f"""
SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE
CARS.ID = CUSTOMERS.ID AND PRICE > 2000000); """
cursorObj.execute(exists_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))

MySQL NOT EQUAL Operator

The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator.

We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results.

Note: The comparison is case-sensitive by default when using this operator with text values.

Syntax

Following is the syntax of the NOT EQUAL operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;

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 below query uses INSERT INTO statement to add 7 records into 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 −

NOT EQUAL with String Values

In MySQL, we can also use the NOT EQUAL to compare two string values. It returns true if both values are not equal. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific value.

Example

In the following query, we are selecting all the records from the CUSTOMERS table whose NAME is not “Khilan”.

SELECT * FROM CUSTOMERS WHERE NAME <> "Khilan";

Output

The output of the above code is as shown below −

NOT EQUAL with GROUP BY Clause

MySQL”s NOT EQUAL operator can be used along with the GROUP BY clause. It will group the results by the values that are not equal to the specified text value.

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

In this query, we are counting the number of records with distinct ”ID” values for each ”AGE” in the ”CUSTOMERS” table. We are excluding records where ”AGE” is equal to ”22”, and grouping the results based on the ”AGE” column.

SELECT COUNT(ID), AGE FROM CUSTOMERS
WHERE AGE  ''22'' GROUP BY AGE;

Output

NOT EQUAL with Multiple Conditions

Depending on the situation, the NOT EQUAL operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

Here, we are going to select all the customers whose salary is either “>2000” or “=2000”. At the same time, the customer must not be from “Bhopal”.

SELECT * FROM CUSTOMERS
WHERE ADDRESS  ''Bhopal'' AND (SALARY>''2000'' OR SALARY=''2000'');

Output

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

Negating a Condition Using NOT EQUAL

The MySQL NOT EQUAL operator can be combined with the NOT operator to negate a condition and filter out rows that meet a specific condition.

Example

The following query retrieves all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” −

SELECT * FROM CUSTOMERS
WHERE NOT SALARY != ''2000

Output

When the query gets executed it will generate the following output as shown below −

NOT EQUAL Operator Using a Client Program

Besides using MySQL queries to perform the NOT EQUAL 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  value";
$mysqli->query($sql);

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

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

not_equal_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value"
cursorObj.execute(not_equal_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 NAME  ''Muffy''";
$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 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 4000.000000
Id 2, Name: Khilan, Age: 25, Address Kerala, Salary 8000.000000
Id 3, Name: kaushik, Age: 23, Address Hyderabad, Salary 11000.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

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'',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 NOT EQUAL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''4000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Kerala'',
    SALARY: ''8000.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''11000.00''
  },
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''1200.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''10000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 29,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''7000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotEqualOperator {
  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 NAME  ''Muffy''";
            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

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
not_equal_query = f"""
SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy
"""
cursorObj.execute(not_equal_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

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

Indexes in MySQL are used to retrieve the data much faster from the database. We (users) cannot see the indexes, but they work behind to speed up searches and queries. They are categorized into two types: clustered and non-clustered indexes.

A clustered index can sort the data in a table manually. When data is inserted into the column with clustered index, the records are automatically sorted in a specified order. So, each table can only have one clustered index since it determines the sort order of the data.

MySQL Clustered Indexes

MySQL database does not have separate provisions for Clustered indexes. They are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index.

If a table has no Primary Key or UNIQUE index, MySQL will internally create a hidden clustered index named GEN_CLUST_INDEX on a column that contains the row ID values.

The rows of a table are ordered using row ID values generated by InnoDB.

Example

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 (20, 2),
   PRIMARY KEY(ID)
);

Now, we will insert some values in to the above created table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2500),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''MP'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

The table will be created as follows −

Using the following query, we can list all the indexes created on the CUSTOMERS table −

SHOW INDEX FROM CUSTOMERSG

Output

As we can see in the output below, the PRIMARY KEY is created on the ID column of CUSTOMERS table.

*************************** 1. row ***************************
        Table: customers
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

Indexes in MySQL are used to retrieve the data much faster from the database tables or views. Users cannot see the indexes on the application level, but they work behind to speed up searches and queries.

There are two types of Indexes in MySQL −

• Clustered Index

• Non-Clustered Index

A clustered index in MySQL can sort the data in a table manually by ordering all the rows in the table based on the key columns used to create it. On the other hand, a non-clustered index stores data in one location and indexes containing pointers to this data in another location.

MySQL Non-Clustered Indexes

Non-Clustered indexes store data in one location and its indexes in another location. These indexes contain pointers to the actual data.

However, MySQL does not provide ways to explicitly create clustered and non-clustered indexes. A PRIMARY KEY is treated as a clustered index. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is a clustered index. All the other indexes on a table are non-clustered indexes.

Syntax

Following is the basic syntax to create a non-clustered index on a MySQL table −

CREATE INDEX index_name ON table_name(column_name(s));

Example

Let us see an example to create a non-clustered index on a table named ”Students”. This table contains details of students like their Roll Number, Name, Age, and Department. Here, we are trying to apply the non-clustered index on columns Roll Number and Department, using the following query −

Let us first create the table Students using CREATE TABLE statement shown below −

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

Using the following query, create a non-clustered index on the NAME column −

CREATE INDEX nc_index ON CUSTOMERS(NAME);

Note − As MySQL does not have specific provision for Non-Clustered Index, we are using the usual CREATE INDEX statement.

Verification

To verify whether the INDEX is created on the table CUSTOMERS or not, display the table definition using DESC command −

DESC CUSTOMERS;

As we can see below, there are two indexes created on the CUSTOMERS table. The PRIMARY KEY index is a clustered index and the multi-index is a non-clustered index −

Creating a Non-Clustered Index Using NodeJS

In addition to using SQL queries to create non-clustered indexes, we can also create them on a MySQL database using a client program.

The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database.

Syntax

Following is the syntax to create a non-clustered index in MySQL database using NodeJS −

sql = "CREATE INDEX index_name ON table_name(column_name(s))";
con.query(sql);

Example

Following are the implementation of this operation using NodeJS −

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 STUDENTS(RNO INT NOT NULL,NAME VARCHAR(50),AGE INT,DEPT VARCHAR(50));"
  con.query(sql);

  //Creating Index
  sql = "CREATE INDEX nc_index ON STUDENTS(RNO, DEPT);"
  con.query(sql);

  //Describing the Table
  sql = "DESC STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {Field: ''RNO'',Type: ''int'',Null: ''NO'',Key: ''MUL'',Default: null,Extra: ''''},
  {Field: ''NAME'',Type: ''varchar(50)'',Null: ''YES'',Key: '''',Default: null,Extra: ''''},
  {Field: ''AGE'',Type: ''int'',Null: ''YES'',Key: '''',Default: null,Extra: ''''},
  {Field: ''DEPT'',Type: ''varchar(50)'',Null: ''YES'',Key: '''',Default: null,Extra: ''''}
]

MySQL WHERE Clause

We know that the SQL SELECT command is used to fetch records from a MySQL table. In addition to that, we can also use a conditional clause called the WHERE Clause in conjunction with the SELECT statement to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.

The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.

Operators Used in WHERE Clause

Here is the list of comparison operators, which can be used with the WHERE clause.

• =: Checks if the values of the two operands are equal or not, if yes, then the condition becomes true.

• !=: Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true.

• >: Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.

• <: Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true.

• >=: Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.

• <=: Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.

Along with these, the WHERE clause can also contain logical operators, like AND, OR and NOT.

• AND: If an AND operator is used in WHERE Clause with two conditions, the query will return true only if both the conditions are satisfied.

• OR: If an OR operator is used in WHERE Clause with two conditions, the query will return true only if either of the conditions are satisfied.

• NOT: If a NOT operator is used in WHERE Clause with a condition, the query will return true only if the table records does not satisfy the condition.

Fetching Data Using Where Clause

The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. Joins are discussed in another chapter.

If the given condition does not match any record in the table, then the query would not return any row.

Syntax

Following is the generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table −

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.

• You can specify any condition using the WHERE clause.

• You can specify more than one condition using the AND or the OR operators.

• A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

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 below query to fetch all the records of CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us fetch the CUSTOMERS whose AGE is greater than 23 using the MySQL WHERE clause in conjunction with SELECT statement −

Select * From CUSTOMERS Where AGE > 23;

Output

Following are the records −

WHERE Clause Using a Client Program

Besides using MySQL Where clause to fetch the selected rows from a table, we can also use client programs like PHP, Node.js, Java, and Python to achieve the same result.

Syntax

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

$sql = "SELECT COLUMN_NAME1, COLUMN_NAME2,.. FROM TABLE_NAME WHERE CONDITION";
$mysqli->query($sql,$resultmode)

sql= "SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2....." ;
Con.query(sql);

String sql = "SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2....";
statement.executeQuery(sql);

where_clause_query = SELECT column1, column2, ...
   FROM table_name WHERE condition;
cursorObj.execute(where_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 where tutorial_author = ''Sanjay''";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d 
",
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"],
         $row["submission_date"]);
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Id: 3, Title: JAVA Tutorial, Author: Sanjay, Date: 2007

$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 = ''Sanjay''";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d 
",
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"],
         $row["submission_date"]);
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Connected!
--------------------------
[
  {
    tutorial_id: 3,
    tutorial_title: ''JAVA Tutorial'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-20T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class WhereClause {
	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 tutorials_tbl WHERE tutorial_author = ''Sanjay''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
            	String Id = rs.getString("tutorial_id");
            	String Title = rs.getString("tutorial_title");
            	String Author = rs.getString("tutorial_author");
            	String Date = rs.getString("submission_date");
            	System.out.println("Id: " + Id + ", Title: " + Title + ", Author: " + Author + ", Submission-date: " + Date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Table records:
Id: 3, Title: Learning Java, Author: Sanjay, Submission-date: 2007-05-06

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
where_clause_query = """SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl
WHERE tutorial_author = ''John Paul''
"""
cursorObj.execute(where_clause_query)
# Fetch all the matching rows
matching_rows = cursorObj.fetchall()
# Printing the matching rows
for row in matching_rows:
    print(row)
cursorObj.close()
connection.close()

(1, ''Learn PHP'', ''John Paul'', datetime.date(2023, 3, 28))

MySQL Limit Clause

The LIMIT clause in MySQL can be used to specify the number of records to return. This clause is mostly used when dealing with tables that have thousands of records. It accepts one or two arguments (offset or count). The values of both arguments should be either be positive integers or zero.

The offset of the first row starts from 0, not from 1 and the count of the first row starts from 1. Let us understand it better using the following picture:

Assume the name of the above table is students. If we execute the above-mentioned query, we will get the output as Mahika, Aarohi, and Nikhil.

Syntax

Following is the generic syntax of MySQL Limit clause −

SELECT column1, column2, ... FROM table_name
LIMIT number;

Example

The following example demonstrates the usage of the MySQL Limit query.

First of all, 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)
);

Here, we are inserting 7 records into the above-created table using the following INSERT INTO statement −

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 present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are selecting the first four records from the CUSTOMERS table using the MySQL LIMIT clause in conjunct with SELECT statement −

SELECT * FROM CUSTOMERS LIMIT 4;

Output

As we can see the output below, it returned the first four rows from the CUSTOMERS table −

Example

In the following query, we are selecting rows from the CUSTOMERS table starting from the third row (offset 2) from then four rows −

SELECT * FROM CUSTOMERS LIMIT 2,4;

Output

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

LIMIT with WHERE Clause

In MySQL, we can use the LIMIT clause along with the WHERE clause in a SELECT statement to specify the number of rows returned from the query based on the conditions.

Syntax

Following is the generic syntax −

SELECT column1, column2, ... FROM table_name
WHERE condition
LIMIT number;

Example

In the query below, we are selecting the first two rows from the CUSTOMERS table where the AGE is greater than 21 −

SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 2;

Output

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

Example

In the following query, we are selecting the next 3 records from the CUSTOMERS table starting from the 2nd record (off set) where the value of the AGE column is greater than 21:

SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 1,3;

Output

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

LIMIT with ORDER BY clause

The ORDER BY clause will sort the rows of a column in the specified order (ASC or DESC). In MySQL, we can use the LIMIT clause along with the ORDER BY clause to limit the number of rows returned in the sorted result set.

Syntax

Following is the syntax of LIMIT clause with WHERE clause in MySQL −

SELECT column1, column2, ... FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;

Example

In the below query, we are fetching all the records from the CUSTOMERS table and sorting the SALARY column in descending order. Then we are fetching 5 rows from the sorted result set.

SELECT * FROM CUSTOMERS
ORDER BY SALARY DESC
LIMIT 5;

Output

If we compile and run the above query, the result is produced as follows −

Example

Here, we are selecting all the records from the table and sorting the SALARY column in ascending order. Then we are fetching rows from the sorted result set starting from the second row (offset 1) from then three rows −

SELECT * FROM CUSTOMERS
ORDER BY SALARY ASC
LIMIT 1,3;

Output

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

Limit Clause Using a Client Program

In addition to limiting the number of records returned from a table with a MySQL query, we can also use a client program to perform the LIMIT operation.

Syntax

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

$sql = "SELECT * FROM tutorials_tbl WHERE tutorial_title = ''Java Tutorial'' LIMIT 3";
$mysqli->query($sql);

sql = "SELECT * FROM CUSTOMERS LIMIT 4";
con.query(sql);

String sql = "SELECT * FROM CUSTOMERS LIMIT 4";
statement.executeQuery(sql);

limit_query = "SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_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_title = ''Java Tutorial'' LIMIT 3";
if($result = $mysqli->query($sql)){
   printf("SELECT LIMIT statement executed successfully..!  ");
   printf("Records are(limit 3): ");
   while($row = mysqli_fetch_row($result)){
      print_r ($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

SELECT LIMIT statement executed successfully..!  Records are(limit 3): Array
(
    [0] => 1
    [1] => Java Tutorial
    [2] => new_author
    [3] =>
)
Array
(
    [0] => 3
    [1] => Java Tutorial
    [2] => newauther1
    [3] => 2023-12-20
)
Array
(
    [0] => 4
    [1] => Java Tutorial
    [2] => newauther2
    [3] => 2022-06-10
)

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!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating a 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, ''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 );"
  con.query(sql);

  sql = "SELECT * FROM CUSTOMERS LIMIT 4"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Connected!
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class LimitQuery {
  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 LIMIT 4";
            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: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.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''
)
cursorObj = connection.cursor()
limit_value = 3
limit_query = f"SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_query)
result = cursorObj.fetchall()
print(f"First {limit_value} rows from Tutorial Table:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

First 3 rows from Tutorial Table:
(1, ''Learn PHP'', ''John Paul'', datetime.date(2023, 3, 28))
(2, ''Learn MySQL'', ''Abdul S'', datetime.date(2023, 3, 28))
(3, ''JAVA Tutorial'', ''Sanjay'', datetime.date(2007, 5, 6))

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

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