Mastering the NOT Operator in SQL

Published : 07 July 2024
Updated : 07 July 2024

131 Views

NOT operator is a fundamental logical operator used to negate a condition in WHERE clause or to negate the result of another logical operator. This operator plays a crucial role in querying databases where we need to reverse the result . You can use more than one NOT operator in single SQL query based on your business requirement. This Operator provides flexibility to reverse the logical state of a condition.

Syntax

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

NOT operator is a fundamental logical operator. It returns TRUE if the condition is FALSE and FALSE if the condition is TRUE.
NOT Operator can be use to combine multiple conditions to perform operation on database table.
NOT operator is use in the scenarios where you need to negate a condition or exclude certain rows from the result set.
Use parentheses to group conditions for clarity and to control the order of operations .
Try to avoid unnecessary NOT operators to maintain query efficiency and readability.
NOT operator can be used to handle NULL values effectively.
Use parentheses to control order of evaluation when mixing NOT with AND / OR for clarity.
Use NOT when it genuinely improves readability (i.e. when excluding is clearer than including), overuse of negations can make logic harder to follow.

Demo Database

we have 2 tables Users (Id, Name , Mobile ,Weight, DOB) and UserAddress (Id, UserId, AddressType, Address, Country, City and PostalCode)

CREATE TABLE Users (
    Id        INT           PRIMARY KEY,
    Name      VARCHAR(100)  NOT NULL,
    Mobile    VARCHAR(20)   NULL,
    Weight    DECIMAL(9,2)  NULL,
    DOB       DATE          NULL
);

CREATE TABLE UserAddress (
    Id          INT           PRIMARY KEY,
    UserId      INT           NOT NULL,
    AddressType VARCHAR(50)   NOT NULL,
    Address     VARCHAR(255)  NOT NULL,
    Country     VARCHAR(100)  NOT NULL,
    City        VARCHAR(100)  NOT NULL,
    PostalCode  VARCHAR(20)   NOT NULL
);

NOT Operator Example

NOT operator is used to negate a condition. If the condition is FALSE it returns TRUE otherwise it returns FALSE.

SQL Query to fetch users which Weight is less than or equal to 50. we can fetch these users by using <= operator or by using NOT with > operator.

SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE NOT Weight > 50;
            OR
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE  Weight <= 50;

NOT with LIKE

LIKE Operator is used to match wildcard pattern. NOT operator can be combined with LIKE operator to fetch records where a pattern does not match.

NOT column_name LIKE 'pattern%'
=> This is used to fetch records where column_name does not start with pattern

SQL query to retrieve users those mobile number not start with 98

SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE  NOT Mobile LIKE "98%";

NOT with BETWEEN

BETWEEN operator is used to define a range. NOT operator can be combined with BETWEEN operator to fetch records where records is not present in that range.

SQL Query to retrieve users, those Id is not BETWEEN range of 30 to 60.

SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE  NOT Id BETWEEN 30 AND 60;
               OR
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE  Id NOT BETWEEN 30 AND 60;

NOT with comparison operators (=, !=, <, >, <=, >=)

Comparison operators are fundamental tools used to compare values in expressions and conditions within SQL statements. NOT operator can be used with comparison operators. When you use NOT operator with comparison operator it will reverse comparison operator nature.

SQL query to retrieve users, those Id is less than 15. we can retrieve these users by using < operator or by using NOT with >= operator.

SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE  NOT ID >= 15;
           OR
SELECT Id, Name, Mobile, DOB, Weight
FROM Users
WHERE  ID < 15;

NOT with IN

IN operator is used to specify multiple values in a WHERE clause. NOT operator can be used with IN operator. It will retrieve data that is not in list value.

SQL query to fetch user , who is not permanent user of India and USA.

SELECT UserId ,AddressType, Country
FROM UserAddress
WHERE AddressType ="Permanent" AND Country NOT IN ("USA", "India");

Some real-world use cases of the SQL NOT operator

Use Case : Customer Segmentation - Excluding Key Segments from General Offers

Your marketing team wants to run a promotion for all customers except those in the “Enterprise” or “Partner” segments. These segments are high-value, managed separately or receive tailored offers, so they shouldn’t be included in the general campaign.

SELECT CustomerID,
       Name,
       Segment
FROM Customers
WHERE Segment NOT IN ('Enterprise', 'Partner');

Instead of listing every segment that can receive the general offer (which could change or grow), you simply exclude the segments that shouldn’t be included. That keeps the logic simpler and more maintainable.
Using NOT IN ('Enterprise', 'Partner') is more straightforward and future-proof than writing a long chain like Segment <> 'Enterprise' AND Segment <> 'Partner' AND Segment <> 'Other…', especially if new segments are added later.

Frequently Asked Questions

Q1. What does the NOT IN operator do?

The NOT IN operator allows you to filter out rows where a given column’s value matches any value in a specified list. In other words: show you all rows except those whose value is in that list.

SELECT CustomerID, Name
FROM Customers
WHERE Segment NOT IN ('Enterprise', 'Partner');

Q2. How does NOT IN compare with other exclusion methods like <> and NOT EXISTS?

NOT IN is essentially a shorthand for multiple <> conditions joined by AND. For example:

WHERE Category <> 'Books'
  AND Category <> 'Toys'

is equivalent to

WHERE Category NOT IN ('Books','Toys')

When using a subquery, NOT IN may behave poorly if the subquery returns NULL. NOT EXISTS is often safer in such scenarios.

Q3. Can I use NOT IN with strings, dates or other data types?

Yes. The NOT IN operator works with any data type (numeric, string, date) as long as the values in the list (or subquery) match the column’s type and there are no invalid NULL complications.

Q4. Is NOT IN efficient from a performance standpoint?

It can be efficient for simple cases (with a small fixed list), but there are performance considerations.

If the list is very large (many values), the query may become less readable and less efficient.
If you’re using a subquery that can return many rows or NULLs, then NOT IN might lead to full scans rather than index seeks. In such cases, NOT EXISTS or LEFT JOIN … IS NULL may perform better.
Keep the exclusion list short and ensure the column used with NOT IN is indexed and the logic is sargable.

Q5. When should you not use NOT IN?

You should be cautious or avoid using NOT IN in the following cases

When the subquery may return NULL values : If the list or subquery used with NOT IN includes even a single NULL, the entire comparison can evaluate to unknown and your query might return no rows at all. Use NOT EXISTS instead it handles NULLs safely and reliably.
When the exclusion list is very large : Having a long NOT IN list (hundreds or thousands of values) can degrade performance because SQL has to check each value individually. Consider using a temporary or small lookup table and perform an anti-join instead.
When readability and maintainability are important : If your logic is complex or includes multiple negations, using NOT IN can make the query harder to understand. In such cases, using NOT EXISTS, LEFT JOIN ... WHERE IS NULL or explicit logical expressions might be clearer.
When exclusion is based on ranges or non-equality conditions : NOT IN only works with specific values. Use other operators like NOT BETWEEN, <> or conditional expressions for range-based or custom filtering.
NOT EXISTS is generally the safer and more predictable choice, especially when working with subqueries or data sets that might contain NULL values.