SQL

NOT in SQL

🚫 The NOT operator in SQL is a logical operator used to reverse the result of a condition. If a condition evaluates to TRUE, NOT changes it to FALSE. Likewise, if a condition evaluates to FALSE, NOT changes it to TRUE. It is commonly used with the WHERE, HAVING, and other SQL clauses to exclude specific records.

📖 What is the NOT Operator?

The NOT operator negates a condition. Instead of selecting rows that satisfy a condition, it selects rows that do not satisfy that condition.

Information

Think of the NOT operator as meaning "everything except" the specified condition.

📝 Basic Syntax

General Syntax

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

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	Marks	City
101	Alice	Computer Science	92	Chennai
102	Bob	Mathematics	85	Coimbatore
103	Charlie	Physics	78	Madurai
104	David	Computer Science	95	Chennai
105	Eva	Physics	88	Coimbatore

🎯 Using NOT with a Simple Condition

Retrieve students who are not in the Computer Science department.

NOT Example

SELECT *
FROM Students
WHERE NOT Department = 'Computer Science';

This query returns all students except those belonging to the Computer Science department.

🔍 NOT with Comparison Operators

The NOT operator can be combined with comparison operators.

Expression	Meaning
NOT Marks > 90	Marks are 90 or below.
NOT Department = 'Physics'	Department is not Physics.
NOT City = 'Chennai'	City is not Chennai.

NOT with Comparison

SELECT *
FROM Students
WHERE NOT Marks > 90;

📌 NOT with BETWEEN

Use NOT BETWEEN to retrieve values outside a specified range.

NOT BETWEEN Example

SELECT *
FROM Students
WHERE Marks NOT BETWEEN 80 AND 90;

This query returns students whose marks are less than 80 or greater than 90.

📝 NOT with IN

The NOT IN operator excludes rows whose values appear in a given list.

NOT IN Example

SELECT *
FROM Students
WHERE Department NOT IN (
    'Computer Science',
    'Physics'
);

🔤 NOT with LIKE

The NOT LIKE operator excludes rows that match a specified pattern.

NOT LIKE Example

SELECT *
FROM Students
WHERE Name NOT LIKE 'A%';

This query returns all students whose names do not begin with the letter A.

🚫 NOT with IS NULL

To retrieve rows that contain actual values instead of NULL, use IS NOT NULL.

IS NOT NULL Example

SELECT *
FROM Students
WHERE Email IS NOT NULL;

Important

Do not write NOT Email = NULL. Always use IS NULL or IS NOT NULL when working with NULL values.

🔗 Combining NOT with AND and OR

The NOT operator can be combined with other logical operators to create more complex filtering conditions.

NOT with AND

SELECT *
FROM Students
WHERE NOT (
    Department = 'Computer Science'
    AND Marks >= 90
);

NOT with OR

SELECT *
FROM Students
WHERE NOT (
    City = 'Chennai'
    OR City = 'Coimbatore'
);

Tip

Use parentheses when combining NOT with AND or OR to make the intended logic clear.

📊 Common NOT Operators

Operator	Purpose
NOT	Reverses a condition.
NOT BETWEEN	Excludes a range of values.
NOT IN	Excludes values in a list.
NOT LIKE	Excludes matching patterns.
IS NOT NULL	Returns rows containing non-NULL values.

💼 Real-World Example

A university wants to list students who are not from the Computer Science department and whose marks are not below 80.

Real-World Query

SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students
WHERE NOT Department = 'Computer Science'
AND NOT Marks < 80
ORDER BY Marks DESC;

This query excludes Computer Science students and also excludes students whose marks are below 80.

⚠️ Best Practices

Best Practice

Use NOT only when it improves query readability. Prefer specific operators such as NOT IN, NOT BETWEEN, and NOT LIKE when appropriate. Always use parentheses in complex logical expressions, and use IS NOT NULL when checking for non-NULL values.

🚀 Key Points to Remember

📌 NOT reverses the result of a condition.
📌 It is commonly used with WHERE and HAVING.
📌 SQL provides specialized forms such as NOT IN, NOT BETWEEN, and NOT LIKE.
📌 Use IS NOT NULL to find non-NULL values.
📌 Parentheses improve readability when combining NOT with AND or OR.

>>"The NOT operator helps you focus on what to exclude, making SQL filtering more flexible and precise."

Summary

✅ The NOT operator is a powerful SQL logical operator that reverses conditions to exclude unwanted records. By combining it with operators such as IN, BETWEEN, LIKE, and IS NULL, you can build clear, efficient, and highly targeted SQL queries.