Open In App

SQL NOT Operator

Improve
Improve
Like Article
Like
Save
Share
Report

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 IDCustomer NameCityPostalCodeCountry
1John WickNew York1248USA
2Around the HornLondonWA1 1DPUK
3RohanNew Delhi100084India

To create this table on your system, run tte the following MySQL Query:

MySQL
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 IDCustomer NameCityPostalCodeCountry
1John WickNew York1248USA
3RohanNew Delhi100084India

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 IDCustomer NameCityPostalCodeCountry
3RohanNew Delhi100084India

Alternatively you can use <> ( Not Operator) to get the desired result

SELECT * FROM Customer WHERE Country <>'USA';

Output:

not operator example

NOT Operator Example

Key TakeAways About NOT Operator:

  • NOT operator returns opposite results or negative results. It negates boolean condition in the WHERE clause.
  • It is used to exclude specific data from the result set.
  • It can also be combined with other operators like- LIKE, BETWEEN, and IN.

Last Updated : 28 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads