MySQL - NOT Operator

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