SQL NOT Operator

The SQL NOT Operator is a logical operator used to negate or reverse the result of a condition in SQL queries. It is commonly used with the WHERE clause to filter records that do not meet a specified condition, helping you exclude certain values from your results.

In this article, we will learn everything you need to know about the SQL NOT operator, from basic usage to advanced examples.

What is the SQL NOT Operator?

The SQL NOT operator is used to reverse the boolean result of a condition in SQL. It helps in retrieving records that do not match a specific condition. It is mostly used to specify what should not be included in the results table.

Syntax:

SELECT column1, colomn2, … 
FROM table_name WHERE NOT condition; 

Examples of SQL NOT Operator

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');

Example 1: Using SQL NOT to Exclude a Specific Value

The following SQL statement selects all fields from Customers table where the country is not UK.

Query:

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

Output:

In this example, the NOT operator filters out customers from the UK and returns all other customers.

Example 2: Using SQL NOT with IN Operator

The NOT operator can also be used with the IN condition to exclude multiple values from the result set.

Query:

SELECT * 
FROM Customers
WHERE NOT Country IN ('USA', 'UK');

Output:

Here, the NOT IN condition filters out customers from both the USA and UK and returns only customers from other countries.

Example 3: Using SQL NOT with LIKE Operator

We can also combine NOT with the LIKE operator to exclude records that match a certain pattern.

Query:

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

Output:

In this query, the NOT LIKE condition filters out customers whose name starts with the letter 'R', returning all others.

Example 4: Using SQL NOT with NULL Values

To exclude records where a column has a NULL value, combine NOT with the IS NULL condition.

Query:

SELECT * 
FROM Customers
WHERE NOT PostalCode IS NULL;

Output:

This query excludes customers who have a NULL value for PostalCode.

Example 5: Using NOT with AND Operator

We can combine NOT with the AND operator to create more complex conditions. This query retrieves customers who are not from the USA and are also not from the UK.

Query:

SELECT * 
FROM Customers
WHERE NOT Country = 'USA' AND NOT Country = 'UK';

Output:

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.

Conclusion

The SQL NOT operator is an essential tool for SQL developers. Whether you're excluding specific records, filtering null values, or creating more complex logical conditions, the NOT operator helps you fine-tune your queries. Understanding how to combine it with other SQL operators will improve your ability to write more efficient and precise SQL queries.