The MySQL NOT Operator
The NOT operator is used in the WHERE clause to return all records that DO NOT match the specified criteria. It reverses the result of a condition from true to false and vice-versa.
The following SQL selects all customers that are NOT from Germany:
Example
SELECT * FROM Customers
WHERE NOT Country = 'Germany';
In the example above, the NOT operator is used in combination with the = operator.
The NOT operator is also used in combination with other operators to exclude data, such as:
NOT LIKENOT BETWEENNOT INIS NOT NULLNOT EXISTS
NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Combining AND, OR and NOT
You can also combine the AND, OR and NOT operators.
The following SQL selects all customers where country is "Germany" AND city must be "Berlin" OR "Stuttgart" (use parenthesis to form complex expressions):
Example
SELECT * FROM Customers
WHERE Country = 'Germany'
AND (City = 'Berlin' OR City = 'Stuttgart');
The following SQL selects all customers where country is NOT "Germany" and NOT "USA":
Example
SELECT * FROM Customers
WHERE NOT Country = 'Germany'
AND NOT Country = 'USA';
The NOT LIKE Operator
The NOT LIKE operator is used in the WHERE clause to exclude rows that match a specified character pattern.
There are two wildcards often used in conjunction with the NOT LIKE operator:
- A percent sign
%- represents zero, one, or multiple characters - A underscore sign
_- represents a single character
The following SQL selects all customers that do NOT start with the letter "A":
Example
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';
The NOT BETWEEN Operator
The NOT BETWEEN operator is used in the WHERE clause to select rows where a value falls outside a specified inclusive range.
The NOT BETWEEN operator can be used with numeric, text, or date values.
The following SQL selects all customers with a CustomerID NOT between 10 and 60:
Example
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;
The NOT IN Operator
The NOT IN operator is used in the WHERE clause to exclude rows that match any value in a specified list or a subquery result set.
The following SQL selects all customers with Country NOT IN "Paris" or "London":
Example
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');
NOT Greater Than
In SQL, the "NOT Greater Than" condition is most often expressed with the standard greater than or equal to (>=) operator.
The following SQL selects all customers with a CustomerID not greater than 50:
Example
SELECT * FROM Customers
WHERE NOT CustomerID > 50;
NOT Less Than
In SQL, the "NOT Less Than" condition is most often expressed with the standard less than or equal to (<=) operator.
The following SQL selects all customers with a CustomerID not less than 50:
Example
SELECT * FROM Customers
WHERE NOT CustomerId < 50;
