SQL NOT

The NOT Operator

The NOT operator is combined with other operators to yield the opposite or negative result.

In the following select statement, we aim to retrieve all customers who are not from Spain:

Example

Retrieve only the customers who are not from Spain.

SELECT * FROM Customers
WHERE NOT Country = ‘Spain’;

In the example provided, the NOT operator is utilized in conjunction with the = operator, yet it can be paired with various other comparison and/or logical operators, as illustrated in the examples below.

Syntax

SELECT column1, column2, ...

FROM table_name

WHERE NOT condition;

Demo Database

Here is a excerpt from the Customers table utilized 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 helado	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

NOT LIKE

Example

Choose customers that do not commence with the letter ‘A’.

SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘A%’;

NOT BETWEEN

Example

Choose customers whose customerID falls outside the range of 10 to 60.

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

NOT IN

Example

Choose customers who are located outside of Paris or London.

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

NOT Greater Than

Example

Choose customers whose CustomerId is less than or equal to 50.

SELECT * FROM Customers
WHERE NOT CustomerID > 50;

Note: You can achieve the same result using the not-greater-than operator, !>, for selecting customers with a CustomerId not greater than 50.

NOT Less Than

Example

Choose customers whose CustomerID is greater than or equal to 50.

SELECT * FROM Customers
WHERE NOT CustomerId < 50;

Note: You can achieve the same result using the not-less-than operator, !<, for selecting customers with a CustomerID not less than 50.