MySQL – NOT Operator
Table of content
- MySQL NOT Operator
- NOT with IN Operator
- NOT with IS NULL Operator
- NOT with LIKE Operator
- NOT with BETWEEN Operator
- NOT with UPDATE Statement
- NOT with DELETE Statement
- NOT Operator Using a Client Program
”;
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 −
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);
To perform the NOT operator on a MySQL table through Node.js program, we need to execute SELECT statement with NOT operator using the query() function of the mysql2 library as follows −
sql=" SELECT column1, column2, ... FROM table_name WHERE NOT condition"; con.query(sql);
To perform the NOT operator on a MySQL table through Java program, we need to execute SELECT statement with NOT operator using the JDBC function executeUpdate() as follows −
String sql = "SELECT column1, column2, ... FROM table_name WHERE NOT condition"; statement.executeQuery(sql);
To perform the NOT operator on a MySQL table through Python program, we need to execute SELECT statement with NOT operator using the execute() function of the MySQL Connector/Python as follows −
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();
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
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) }); });
Output
The output produced is as follows −
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(); } } }
Output
The output obtained is as shown below −
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()
Output
Following is the output of the above code −
(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''))
”;