Updated May 26, 2023
Overview of NOT in MySQL
‘NOT’ is a MySQL operator, mostly used with Data Query Language. This language consists of SELECT command for fetching records from the database along with many clauses like WHERE, BETWEEN, LIKE, IN, etc. These clauses are used for defining many conditions or a range of values for conditions for filtering out our required records. Below are the description and syntax of each operator.
| Operator | Description | Syntax |
| WHERE | Used for giving conditions in the retrieval of records. | SELECT employee_id FROM employee WHERE name = ‘stella’; |
| BETWEEN | This operator selects records within a given range. Mostly we use this where we want to specify a range of dates. | SELECT emp_id FROM employee WHERE emp_id BETWEEN 1 AND 10; |
| SELECT * FROM employee WHERE join_date BETWEEN ‘2007-01-01’ AND ‘2008-01-01’; |
||
| IN | This operator allows us to specify multiple values in a WHERE clause. | SELECT * FROM employee WHERE employee_id IN (1001,1004,1008,1012); |
| LIKE | This operator uses the WHERE clause to search for a specified pattern in a string column.
|
SELECT first_name FROM table WHERE first_name LIKE ‘A%’; |
Syntax of NOT in MySQL
‘NOT’ with all other operators.
Syntax #1:
SELECT employee_id FROM employee
WHERE name IS NOT 'stella';OR
SELECT employee_id FROM employee
WHERE name <> 'stella';Here it will fetch all records except the record where the name is ‘stella’.
Syntax #2:
SELECT emp_id FROM employee
WHERE emp_id NOT BETWEEN 1 AND 10;Here it will fetch all emp_id whose ids are not between 1 to 10
Syntax #3:
SELECT * FROM employee
WHERE employee_id NOT IN (1001,1004,1008,1012);Here it will fetch all employee_id except 1001,1004,1008 and 1012.
Syntax #4:
SELECT * FROM employee
WHERE first_name NOT LIKE 'A%';It will fetch records where first_name’s first letter doesn’t start with A.
How does the NOT Condition Work in MySQL?
Below we will discuss some small examples to illustrate how this NOT operator works in MySQL.
Let’s create a fruit table with the following attributes.
| Product_name | Price |
| Apple | 20 |
| Orange | 25 |
| Grapes | 30 |
| Banana | 22 |
| Pomegranate | 28 |
| Guava | 35 |
| Kiwi | 50 |
1. With WHERE Clause
Query:
SELECT * FROM fruit
WHERE product_name IS NOT 'Apple';Output:
| Product_name | Price |
| Orange | 25 |
| Grapes | 30 |
| Banana | 22 |
| Pomegranate | 28 |
| Guava | 35 |
| Kiwi | 50 |
2. With BETWEEN Operator
Query:
SELECT * FROM fruit
WHERE Price NOT BETWEEN 20 AND 30;It will look into the table for those records whose prices will not be between 20 and 30. As mentioned earlier, this between operators is inclusive; it will not include both 20 and 30.
Output:
| Product_name | Price |
| Guava | 35 |
| Kiwi | 50 |
3. With IN Operator
Query:
SELECT * FROM fruit
WHERE price NOT IN (25,30,50);Output:
| Product_name | Price |
| Apple | 20 |
| Banana | 22 |
| Pomegranate | 28 |
| Guava | 35 |
4. With LIKE Operator
Query:
SELECT * FROM fruit
WHERE product_name NOT LIKE 'G%';Output:
| Product_name | Price |
| Apple | 20 |
| Orange | 25 |
| Banana | 22 |
| Pomegranate | 28 |
| Kiwi | 50 |
Examples to Implement NOT Condition in MySQL
Here we will create another “employee” table with the following attributes.
| Employee_id | First_name | Last_name | Hire_date | Salary | |
| 100 | ste abcd | King | SKING | 1987-06-17 | 24000.00 |
| 101 | Neena | Kochhar | NKOCHHAR | 1989-09-21 | 17000.00 |
| 102 | Lex | De Haan | LDEHAAN | 1993-01-13 | 17000.00 |
| 108 | Nancy | Greenberg | NGREENBE | 1994-08-17 | 12000.00 |
| 114 | Den | Raphaely | DRAPHEAL | 1994-12-07 | 11000.00 |
| 145 | John | Russell | JRUSSEL | 1996-10-01 | 14000.00 |
| 146 | Karen | Partners | KPARTNER | 1997-01-05 | 13500.00 |
| 147 | Alberto | Errazuriz | AERRAZUR | 1997-03-10 | 12000.00 |
| 148 | Gerald | Cambrault | GCAMBRAU | 1999-10-15 | 11000.00 |
| 149 | Eleni | Zlotkey | EZLOTKEY | 2000-01-29 | 10500.00 |
| 162 | Clara | Vishney | CVISHNEY | 1997-11-11 | 10500.00 |
| 168 | Lisa | Ozer | LOZER | 1997-03-11 | 11500.00 |
| 174 | Ellen | Abel | EABEL | 1996-05-11 | 11000.00 |
| 201 | Michael | Hartstein | MHARTSTE | 1996-02-17 | 13000.00 |
| 205 | Shelley | Higgins | SHIGGINS | 1994-06-07 | 12000.00 |
Example #1
It will fetch records except for the record where the first_name is Neena.
Query:
SELECT * FROM employees
WHERE first_name <> 'Neena';Output:
Example #2
This will exclude the records where the salary is between 12000 to 20000.
Query:
SELECT * FROM employee
WHERE salary NOT BETWEEN 12000 AND 20000;Output:
Example #3
This will exclude the records where the hire_date is between 1992-1-1 to 1995-1-1.
Query:
SELECT * FROM employee
WHERE hire_date NOT BETWEEN '1992-1-1' AND '1995-1-1';Output:
Example #4
Query:
SELECT * FROM employees
WHERE employee_id NOT IN (100,114,148);Output:
Example #5
Query:
SELECT * FROM employee
WHERE first_name NOT LIKE 'L%';Output:
Conclusion
This operator basically negates the condition in a SELECT, INSERT, UPDATE, and DELETE statement. This condition requires that the opposite of the condition must be met. We can use this operator with other SQL commands as we performed these examples with the SELECT command similarly. INSERT is used for inserting new rows, UPDATE for updating the table, and DELETE for deleting rows from a table.
Recommended Articles
We hope that this EDUCBA information on “NOT in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
