MySQL NOT

Last Updated: 02 July, 2023

In MySQL, the NOT keyword is used to negate a condition or expression in various contexts, such as SELECT statements, WHERE clauses, and other SQL operations. It is used to filter rows where the specified condition is not met.

Here are some examples of how the NOT keyword can be used:

Using NOT in WHERE clause:

Suppose we have a table called employees with columns employee_id, name, and is_active. To select all inactive employees, we can use the NOT keyword with the equal (=) operator.

 SELECT * FROM employees WHERE NOT is_active = 1;

This query will retrieve all rows from the employees table where the is_active column is not equal to 1 (i.e., where is_active is not 1).

Using NOT with LIKE operator:

Suppose we want to select all employees whose name does not start with 'John':

 SELECT * FROM employees WHERE NOT name LIKE 'John%';

This query will retrieve all rows from the employees table where the name column does not start with 'John'.

Using NOT with BETWEEN operator:

Suppose we have a table called products with a price column, and we want to select all products whose price is not between $50 and $100:

 SELECT * FROM products WHERE NOT price BETWEEN 50 AND 100;

This query will retrieve all rows from the products table where the price is not between 50 and 100.

Using NOT with IN operator:

Suppose we have a table called orders with an status column, and we want to select all orders whose status is not 'completed' or 'shipped':

 SELECT * FROM orders WHERE NOT status IN ('completed', 'shipped');

This query will retrieve all rows from the orders table where the status is not 'completed' or 'shipped'.

These are just a few examples of how we can use the "NOT" condition in MySQL. We can combine it with various other operators, functions, and logical operators to create more complex conditions to suit our needs.