MySQL: NOT Condition

This MySQL tutorial explains how to use the MySQL NOT condition with syntax and examples.

Description

The MySQL NOT Condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the NOT Condition in MySQL is:

NOT condition

Parameters or Arguments

condition

The condition to negate.

Note

• The MySQL NOT condition requires that the opposite of the condition be must be met for the record to be included in the result set.

Example - Combine With IN condition

The MySQL NOT condition can be combined with the IN Condition.

For example:

SELECT *

FROM contacts

WHERE first_name NOT IN ('Joseph','Andrew','Brad');

This MySQL NOT example would return all rows from the contacts table where the first_name is not Joseph, Andrew, or Brad. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example - Combine With IS NULL condition

The MySQL NOT condition can also be combined with the IS NULL Condition.

For example,

SELECT *

FROM contacts

WHERE first_name IS NOT NULL;

This MySQL NOT example would return all records from the contacts table where the first_name does not contain a NULL value.

Example - Combine With LIKE condition

The MySQL NOT condition can also be combined with the LIKE Condition.

For example:

SELECT supplier_id, supplier_name

FROM suppliers

WHERE supplier_name NOT LIKE 'P%';

By placing the MySQL NOT Operator in front of the LIKE condition, you are able to retrieve all suppliers whose supplier_name does not start with 'P'.

Example - Combine With BETWEEN condition

The MySQL NOT condition can also be combined with the BETWEEN Condition. Here is an example of how you would combine the NOT Operator with the BETWEEN Condition.

For example:

SELECT *

FROM orders

WHERE order_id NOT BETWEEN 300 AND 399;

This MySQL NOT example would return all rows where the order_id was NOT between 300 and 399, inclusive. It would be equivalent to the following SELECT statement:

SELECT *

FROM orders

WHERE order_id < 300

OR order_id > 399;

Example - Combine With EXISTS condition

The MySQL NOT condition can also be combined with the EXISTS Condition.

For example,

SELECT *

FROM suppliers

WHERE NOT EXISTS (SELECT *

                  FROM orders

                  WHERE suppliers.supplier_id = orders.supplier_id);

This MySQL NOT example would return all records from the suppliers table where there are no records in the orders table for the given supplier_id.