NOT Operator

NOT Operator in MySQL

Introduction
Excluding specific conditions is essential for precise data filtering.
The NOT operator in MySQL negates conditions and other operators.
It is widely used for:

• Exclusion filtering
• Negating complex conditions
• Data validation

What is NOT Operator
NOT reverses the truth value of a condition (TRUE→FALSE, FALSE→TRUE).
Combines with =, >, LIKE, IN, EXISTS, IS NULL, BETWEEN.

Basic Syntax

sql

SELECT column_name 

FROM table_name 

WHERE NOT condition; 

Example

sql

SELECT * FROM Students 

WHERE NOT status = 'Inactive'; 

Returns all active and provisional students.

NOT with Equality
Negates exact matches.

sql

SELECT * FROM Students 

WHERE NOT city = 'Delhi'; 

All students except from Delhi.

NOT LIKE
Excludes pattern matches.

sql

SELECT * FROM Students 

WHERE NOT name LIKE 'A%'; 

Students whose names don't start with A.

NOT IN
Excludes list membership.

sql

SELECT * FROM Students 

WHERE NOT city IN ('Delhi', 'Mumbai'); 

Students from other cities.

NOT BETWEEN
Excludes ranges.

sql

SELECT * FROM Students 

WHERE NOT marks BETWEEN 60 AND 80; 

Students with marks <60 or >80.

NOT EXISTS
No matching subquery rows.

sql

SELECT * FROM Students 

WHERE NOT EXISTS ( 

    SELECT 1 FROM Orders WHERE student_id = Students.id 

); 

NOT with NULL
NOT (column IS NULL) is equivalent to column IS NOT NULL.

sql

SELECT * FROM Students 

WHERE NOT (marks IS NULL); 

NOT with Multiple Conditions
Negate complex logic.

sql

SELECT * FROM Students 

WHERE NOT (city = 'Delhi' AND marks < 70); 

Excludes Delhi students with low marks only.

NOT with ORDER BY
Filter exclusions first.

sql

SELECT * FROM Students 

WHERE NOT status = 'Dropped' 

ORDER BY marks DESC; 

NOT with GROUP BY
Exclude before aggregation.

sql

SELECT city, AVG(marks) 

FROM Students 

WHERE NOT city = 'Remote' 

GROUP BY city; 

NOT with AND/OR Precedence
NOT has the highest precedence.

sql

SELECT * FROM Students 

WHERE NOT city = 'Delhi' AND marks > 80; 

Parsed as: NOT(city = 'Delhi') AND marks > 80

De Morgan's Laws
Negate AND/OR combinations:
NOT (A AND B) = NOT A OR NOT B
NOT (A OR B) = NOT A AND NOT B

sql

-- Instead of: NOT (city='Delhi' AND marks<70) 

-- Use: city!='Delhi' OR marks>=70 

Execution Order with NOT
Evaluated in the WHERE clause after FROM.
FROM → WHERE (NOT conditions) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• NOT often prevents index usage
• NOT IN with NULL returns no rows
• Consider positive conditions when possible
• NOT EXISTS usually performs well

Important Notes

• NOT (col IS NULL) = col IS NOT NULL
• NOT IN (..., NULL) returns no rows
• NOT has the highest precedence
• NULL NOT = NULL (three-value logic)
• NOT LIKE '%pattern%' scans entire table

Example Scenario
Find exceptional students, excluding problem cases:

sql

SELECT name, marks, city 

FROM Students 

WHERE NOT status IN ('Dropped', 'Suspended') 

  AND NOT city IN ('Remote', 'Unknown') 

  AND marks IS NOT NULL 

ORDER BY marks DESC 

LIMIT 100;

Common Mistakes

• NOT IN with NULL (returns no rows)
• Performance hit from NOT on indexed columns
• Forgetting NOT precedence with AND/OR
• NOT (col = NULL) never works
• Complex NOT logic harder to read

Key Points to Remember

• NOT negates any condition or operator
• NOT IN (NULL) returns no rows
• NOT prevents index usage on many columns
• Use IS NOT NULL instead of NOT (col IS NULL)
• De Morgan's laws for complex negations
• NOT EXISTS is good for exclusion checks