SQL NOT Operator

The NOT operator in SQL reverses the result of a condition. It essentially says "if this condition is true, make it false; if it's false, make it true". It's used to negate conditions and filter data based on the opposite of a given condition.

NOT: Definition and Usage

NOT is often used with other operators (like =, >, <, LIKE, BETWEEN, IN) to select rows that *don't* meet a specific criteria. This allows you to find records that are outside a particular set of conditions. It's a powerful tool for creating more refined queries.

Syntax


SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example Database: Customers Table

The following examples use this sample 'Customers' table:

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

Examples using NOT

Negating an Equality Condition

This selects all customers who are NOT from Spain.

Syntax


SELECT * FROM Customers
WHERE NOT Country = 'Spain';

Output


(All rows except those where Country is 'Spain' will be returned)

NOT LIKE

This selects customers whose names do not begin with 'A'.

Syntax


SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

Output


(All rows where CustomerName does not start with 'A' will be returned)

NOT BETWEEN

This selects customers with CustomerID not between 10 and 60.

Syntax


SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;

Output


(All rows where CustomerID is less than 10 or greater than 60 will be returned)

NOT IN

This selects customers not from 'Paris' or 'London'.

Syntax


SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');

Output


(All rows where City is neither 'Paris' nor 'London' will be returned)

NOT Greater Than

This selects customers with CustomerID not greater than 50. Note the equivalent `!<` operator.

Syntax


SELECT * FROM Customers
WHERE NOT CustomerID > 50;

Output


(All rows where CustomerID is less than or equal to 50 will be returned)

NOT Less Than

This selects customers with CustomerID not less than 50. Note the equivalent `!<=` operator.

Syntax


SELECT * FROM Customers
WHERE NOT CustomerId < 50;

Output


(All rows where CustomerID is greater than or equal to 50 will be returned)