SQL NOT Operator
SQL NOT Operator is used to return the opposite result or negative result. It is a logical operator in SQL, that negates the boolean expression in the WHERE clause.
It is mostly used to specify what should not be included in the results table.
NOT Syntax
SELECT column1, colomn2, …
FROM table_name WHERE NOT condition;
Demo SQL Database
Below is a selection from the “Customers” table in the Northwind sample database:
Customer ID | Customer Name | City | PostalCode | Country |
---|---|---|---|---|
1 | John Wick | New York | 1248 | USA |
2 | Around the Horn | London | WA1 1DP | UK |
3 | Rohan | New Delhi | 100084 | India |
To create this table on your system, run tte the following MySQL Query:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
City VARCHAR(50),
PostalCode VARCHAR(10),
Country VARCHAR(50)
);
INSERT INTO Customers (CustomerID, CustomerName, City, PostalCode, Country)
VALUES
(1, 'John Wick', 'New York', '1248', 'USA'),
(2, 'Around the Horn', 'London', 'WA1 1DP', 'UK'),
(3, 'Rohan', 'New Delhi', '100084', 'India');
NOT Operator Example
Lets look at some examples of NOT operator in SQL and understand it’s working.
The following SQL statement selects all fields from “Customers” where the country is not “UK” SELECT * FROM Customers WHERE NOT Country=’UK’;
Customer ID | Customer Name | City | PostalCode | Country |
---|---|---|---|---|
1 | John Wick | New York | 1248 | USA |
3 | Rohan | New Delhi | 100084 | India |
Combining AND, OR and NOT
You can also combine the AND, OR, and NOT operators. Example: 1.) SELECT * FROM Customers WHERE NOT Country=’USA’ AND NOT Country=’UK’;
Customer ID | Customer Name | City | PostalCode | Country |
---|---|---|---|---|
3 | Rohan | New Delhi | 100084 | India |
Alternatively you can use <> ( Not Operator) to get the desired result
SELECT * FROM Customer WHERE Country <>'USA';
Output:
Contact Us