NOT EQUAL Operator in MySQL
Introduction
Excluding specific values requires precise comparison operators.
MySQL provides two NOT EQUAL operators: != and <>.
They are widely used for:
- Value exclusion filtering
- Data cleanup operations
- Conditional exclusion logic
What is NOT EQUAL Operator
NOT EQUAL compares two values and returns TRUE if they differ.
MySQL supports both != (standard) and <> (SQL standard) syntax.
Basic Syntax
sql
SELECT column_name
FROM table_name
WHERE column_name != value;
-- Or
SELECT column_name
FROM table_name
WHERE column_name <> value;
Example
sql
SELECT * FROM Students
WHERE city != 'Delhi';
Returns students from all cities except Delhi.
!= vs <> Syntax
Both identical in functionality.
sql
SELECT * FROM Students
WHERE marks != 0; -- Modern style
SELECT * FROM Students
WHERE marks <> 0; -- SQL standard
NOT EQUAL with Strings
Case-sensitive based on collation.
sql
SELECT * FROM Students
WHERE status != 'Active';
NOT EQUAL with Numbers
Works with integers and decimals.
sql
SELECT * FROM Students
WHERE marks != 35.5;
Multiple NOT EQUAL Conditions
Exclude multiple values.
sql
SELECT * FROM Students
WHERE status != 'Dropped' AND status != 'Suspended';
NOT EQUAL with NULL
!= and <> with NULL always return NULL (not TRUE).
sql
SELECT * FROM Students
WHERE marks != NULL; -- Returns no rows
Use IS NOT NULL instead.
NOT EQUAL with AND
Combine exclusion conditions.
sql
SELECT * FROM Students
WHERE city != 'Remote' AND marks != 0;
NOT EQUAL with OR
Exclude using OR logic.
sql
SELECT * FROM Students
WHERE city != 'Delhi' OR marks != 0;
NOT EQUAL with ORDER BY
Filter first, then sort.
sql
SELECT * FROM Students
WHERE status != 'Inactive'
ORDER BY marks DESC;
NOT EQUAL with GROUP BY
Exclude before aggregation.
sql
SELECT city, AVG(marks)
FROM Students
WHERE status != 'Dropped'
GROUP BY city;
NOT EQUAL with JOIN
Exclude across related tables.
sql
SELECT s.name, c.course_name
FROM Students s
JOIN Courses c ON s.course_id = c.id
WHERE s.status != 'Suspended';
Performance: NOT EQUAL vs NOT IN
NOT EQUAL uses indexes better than NOT IN.
sql
-- Index-friendly
WHERE status != 'Dropped';
-- May not use index as well
WHERE status NOT IN ('Dropped', 'Suspended');
NOT EQUAL Truth Table
| Left | Right | != Result | <> Result |
| 5 | 5 | FALSE | FALSE |
| 5 | 10 | TRUE | TRUE |
| 5 | NULL | NULL | NULL |
| NULL | 5 | NULL | NULL |
Execution Order with NOT EQUAL
Evaluated in the WHERE clause after FROM.
FROM → WHERE (!=/<>) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- != and <> identical performance
- Index works with NOT EQUAL
- Better than NOT IN for single exclusions
- Multiple exclusions: consider IN/NOT IN
Important Notes
- != and <> completely interchangeable
- NULL != value always NULL (use IS NOT NULL)
- Case sensitivity depends on collation
- Works with numbers, strings, and dates
- String comparison pads shorter strings with spaces
Example Scenario
Find valid data excluding test records:
sql
SELECT name, city, marks, status
FROM Students
WHERE marks != 0 -- Valid marks
AND marks IS NOT NULL -- Not null
AND status != 'Test' -- Not test data
AND city != 'Demo' -- Not demo city
ORDER BY marks DESC
LIMIT 100;
Common Mistakes
- Using != with NULL (returns no rows)
- Expecting != NULL to work like IS NOT NULL
- Performance confusion between != and NOT IN
- Case sensitivity issues with strings
- Forgetting collation affects string comparison
Key Points to Remember
- Use != or <> (both identical) for not equal
- != NULL always returns NULL (use IS NOT NULL)
- Index-friendly, unlike many NOT operations
- Multiple exclusions are better with NOT IN
- Case-sensitive based on table collation
- Works with all data types consistently