Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

NOT Operator

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.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
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
3
Kaushik
23
Kota
2000.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
12000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
12000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
12000.00
7
Muffy
24
Indore
12000.00
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 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
12000.00
3
Kaushik
23
Kota
12000.00
6
Komal
22
Hyderabad
12000.00
7
Muffy
24
Indore
12000.00
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 
PHPNodeJSJavaPython
To perform the NOT operator on a MySQL table through PHP program, we need to execute SELECT statement with NOT operator using the mysqli function query() as follows 
$sql = "SELECT column1, column2, ... FROM table_name 
WHERE NOT condition";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$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();  
Output
The output obtained is as follows 
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  
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿


﻿

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

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

NOT Operator

MySQL NOT Operator

Syntax

Example

Output

NOT with IN Operator

Example

Output

NOT with IS NULL Operator

Example

Output

NOT with LIKE Operator

Example

Output

NOT with BETWEEN Operator

Example

Output

NOT with UPDATE Statement

Syntax

Example

Output

Verification

Output

NOT with DELETE Statement

Syntax

Example

Output

Verification

Output

NOT Operator Using a Client Program

Syntax

Example

Output