MySQL NOT

The NOT operator reverses a logical condition.

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

This returns students whose course is not Python.

The basic syntax is:

WHERE NOT condition;

Example:

SELECT *
FROM students
WHERE NOT age > 20;

This returns records where the condition age > 20 is not true.

NOT can be used to find values that are not equal to a specified value.

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

This excludes students whose course is Python.

NOT can reverse a comparison condition.

SELECT *
FROM students
WHERE NOT age > 20;

The condition is reversed, so records that do not satisfy age > 20 are selected.

NOT can also be used with a less-than condition.

SELECT *
FROM students
WHERE NOT age < 18;

This excludes students whose age is less than 18.

NOT can reverse a greater-than condition.

SELECT *
FROM students
WHERE NOT fee > 15000;

This excludes records where the fee is greater than 15,000.

NOT IN is commonly used to exclude multiple values.

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

This returns students whose course is neither Python nor Java.

NOT BETWEEN is used to find values outside a specified range.

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

This returns students whose age is outside the range 18 to 25.

NOT LIKE is used to exclude records matching a pattern.

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

This excludes names that begin with the letter A.

You can use IS NOT NULL to find records where a value exists.

SELECT *
FROM students
WHERE mobile IS NOT NULL;

This returns records that have a non-NULL mobile value.

SELECT *
FROM students
WHERE email IS NOT NULL;

NOT can be combined with AND conditions.

SELECT *
FROM students
WHERE NOT (course = 'Python' AND age > 20);

The parentheses make it clear that NOT applies to the complete condition.

NOT can also be applied to a group containing OR.

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

This excludes both Python and Java students.

Parentheses are useful when NOT applies to multiple conditions.

SELECT *
FROM students
WHERE NOT (age > 18 AND fee > 10000);

Here, NOT reverses the complete expression inside the parentheses.

NOT can be combined with other logical operators.

SELECT *
FROM students
WHERE NOT (course = 'Python')
AND age > 20;

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

NOT can also be used with date conditions.

SELECT *
FROM students
WHERE NOT admission_date = '2026-09-21';

This excludes records with the specified admission date.

NOT can be used with decimal comparisons.

SELECT *
FROM students
WHERE NOT fee > 15000;

This returns records where the fee is not greater than 15,000.

NOT can be used with UPDATE to modify records that do not satisfy a condition.

UPDATE students
SET fee = fee + 500
WHERE course NOT IN ('Python', 'Java');

This updates students whose course is neither Python nor Java.

NOT can also be used with DELETE.

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

This deletes students whose course is not Python or Java.

NOT conditions can be combined with ORDER BY.

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

The excluded courses are filtered first, and the remaining records are sorted by fee.

NOT conditions can also be combined with LIMIT.

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

This returns up to five records that are not from the specified courses.

For simple inequality conditions, != can often express the same idea as NOT with equality.

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

Another form is:

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

Both express that the course should not be Python.

• Forgetting parentheses when NOT applies to multiple conditions
• Confusing NOT with AND
• Using = NULL instead of IS NULL
• Using NOT IN without considering NULL values
• Using NOT in UPDATE or DELETE without checking the affected records
• Writing a condition that excludes more records than intended

Suppose we want students who are not enrolled in Python.

SELECT id, name, course, fee
FROM students
WHERE course NOT IN ('Python');

This excludes Python students from the result.

For complex queries, parentheses make the intended logic easier to understand.

SELECT id, name, course, age, fee
FROM students
WHERE NOT (
    course = 'Python'
    AND age > 20
)
ORDER BY name;

This excludes students who satisfy both the Python and age conditions.

A simple workflow is:

1. Identify the condition you want to exclude.
2. Use NOT, NOT IN, NOT BETWEEN, or NOT LIKE.
3. Use parentheses for complex conditions.
4. Test the query using SELECT first.
5. Only then use UPDATE or DELETE if required.

SELECT *
FROM students
WHERE course NOT IN ('Python', 'Java')
AND age > 18
ORDER BY age DESC;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, age, fee)
VALUES
('Rahul', 'Python', 22, 15000.00),
('Priya', 'Java', 21, 18000.00),
('Amit', 'Python', 24, 12000.00),
('Neha', 'PHP', 20, 10000.00),
('Ravi', 'JavaScript', 26, 22000.00);

SELECT id, name, course, age, fee
FROM students
WHERE course NOT IN ('Python', 'Java')
AND age > 18
ORDER BY fee DESC;

This query returns students who are not enrolled in Python or Java and whose age is greater than 18.