The 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 Spain:
Example
SELECT * FROM Customers
WHERE NOT Country = 'Spain';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 LIKE
- NOT BETWEEN
- NOT IN
- IS NOT NULL
- NOT EXISTS
NOT Syntax
SELECT column1 , column2, ... FROM table_name WHERE NOT condition ;
Demo Database
Below is a selection from the Customers table used in the examples:
| 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 |
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 City 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;