NOT Operator in SQL

The NOT operator reverses the result of a condition.

SELECT *
FROM Students
WHERE NOT course = 'Python';

This query returns students whose course is not Python.

SELECT column_name
FROM table_name
WHERE NOT condition;

The NOT operator is placed before the condition that you want to reverse.

SELECT *
FROM Students
WHERE NOT age = 20;

This returns students whose age is not 20.

SELECT *
FROM Students
WHERE NOT course = 'Java';

This returns students whose course is not Java.

SELECT *
FROM Students
WHERE NOT age > 20;

This returns records where the condition age > 20 is false, subject to SQL's treatment of NULL values.

For ordinary non-NULL age values, this corresponds to ages less than or equal to 20.

SELECT *
FROM Students
WHERE NOT age < 18;

For non-NULL age values, this returns records where age is greater than or equal to 18.

NOT can be used with the IN operator.

SELECT *
FROM Students
WHERE course NOT IN ('Python', 'Java');

This returns students whose course is neither Python nor Java.

SELECT column_name
FROM table_name
WHERE column_name NOT IN (value1, value2, value3);

NOT IN is a convenient way to exclude several specific values.

You can use NOT with BETWEEN to exclude a range.

SELECT *
FROM Students
WHERE age NOT BETWEEN 18 AND 25;

This returns values outside the range 18 through 25, for non-NULL age values.

Suppose the ages are:

16
18
20
22
25
28
30

Query:

SELECT *
FROM Students
WHERE age NOT BETWEEN 18 AND 25;

The matching ages are 16, 28, and 30.

NOT can be used with LIKE to exclude a pattern.

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

This returns names that do not start with A.

SELECT *
FROM Students
WHERE email NOT LIKE '%@gmail.com';

This excludes email addresses ending with @gmail.com, subject to the database's pattern-matching and collation rules.

You can use NOT with an IS NULL condition.

SELECT *
FROM Students
WHERE NOT (mobile IS NULL);

This finds records where the mobile value is not NULL.

A more common and simpler form is:

SELECT *
FROM Students
WHERE mobile IS NOT NULL;

IS NOT NULL is specifically used to find values that are not NULL.

SELECT *
FROM Students
WHERE mobile IS NOT NULL;

NOT can be combined with AND.

SELECT *
FROM Students
WHERE NOT course = 'Python'
AND age > 18;

This finds students who are not in Python and are older than 18.

SELECT *
FROM Students
WHERE NOT (course = 'Python' OR course = 'Java');

This excludes students whose course is Python or Java.

Parentheses are useful when NOT applies to a group of conditions.

SELECT *
FROM Students
WHERE NOT (
    course = 'Python'
    AND age < 20
);

The grouped condition is evaluated first, and NOT reverses its result.

SELECT *
FROM Students
WHERE NOT (
    city = 'Patna'
    AND course = 'Python'
);

This excludes records where both city is Patna and course is Python.

For a simple equality condition, these expressions are often equivalent for non-NULL values:

WHERE NOT course = 'Python'

WHERE course <> 'Python'

The second form is often shorter when checking that one value is not equal to another.

In MySQL, != can also be used for not equal.

SELECT *
FROM Students
WHERE course != 'Python';

For ordinary non-NULL values, this is equivalent to checking that the course is not Python.

Instead of writing multiple not-equal conditions:

WHERE course <> 'Python'
AND course <> 'Java'

You can use:

WHERE course NOT IN ('Python', 'Java');

NOT IN is usually easier to read when excluding multiple values.

SELECT *
FROM Students
WHERE name NOT LIKE 'R%';

The % wildcard represents zero or more characters. This query excludes names beginning with R.

SELECT *
FROM Students
WHERE course NOT IN ('Python', 'Java')
AND age >= 18;

This returns students who are at least 18 and are not enrolled in Python or Java.

SELECT *
FROM Students
WHERE age NOT BETWEEN 18 AND 25
AND fee > 5000;

This returns students whose age is outside the range 18–25 and whose fee is greater than 5000.

SELECT name, course, age
FROM Students
WHERE course NOT IN ('Python', 'Java')
ORDER BY age DESC;

The records are first filtered and then sorted by age in descending order.

SELECT *
FROM Students
WHERE course NOT IN ('Python', 'Java')
LIMIT 5;

In MySQL, this returns up to five matching records.

NOT can also be used when updating selected records.

UPDATE Students
SET status = 'Inactive'
WHERE NOT course = 'Python';

This updates records whose course is not Python.

NOT can also be used with DELETE.

DELETE FROM Students
WHERE course NOT IN ('Python', 'Java');

This targets students whose course is neither Python nor Java.

Suppose an institute wants students who are not enrolled in Python or Java and who are older than 18.

SELECT name, age, course
FROM Students
WHERE course NOT IN ('Python', 'Java')
AND age > 18;

This combines NOT IN with AND.

SELECT name, age, course, city, fee
FROM Students
WHERE NOT (
    course IN ('Python', 'Java')
)
AND age >= 18
AND city NOT IN ('Patna', 'Delhi')
AND fee > 5000
ORDER BY fee DESC;

This query:

• Excludes Python and Java students.
• Includes students aged 18 or above.
• Excludes students from Patna and Delhi.
• Requires a fee greater than 5000.
• Sorts the result by fee from highest to lowest.