This tutorial will show you how to write a MySQL query with the not equal operator. To use the MySQL not equal operator, you need to be using MySQL version 5.6 or higher.
A not equal operator (<>) is used for comparing two fields or expressions to see if they are unequal. MySQL not equal operator looks as follows
<>!=
Both operators above return the same result, but the <> operator comes from ANSI SQL standard while != is a non-standard feature.
According to Bill from StackOverflow, only MS Access and IBM DB2 don’t support the != operator.
Since MySQL supports both operators, you are free to use the one you prefer.
You can test the not equal operator by comparing two expressions as follows:
SELECT 1 != 1, 1 <> 2;
The query above will return the following result set:
+--------+--------+
| 1 != 1 | 1 <> 2 |
+--------+--------+
| 0 | 1 |
+--------+--------+
1 row in set (0.01 sec)
When the result of the comparison is not equal, then MySQL will return 1 (true) instead of 0 (false).
Using not equal operator in WHERE statement
The not equal operator is commonly used in the WHERE statement to filter the result set returned by the query.
For example, suppose you have a MySQL table for students with the following data:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | English | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | Gary | Math | 6 | male |
| 4 | Joe | English | 8 | male |
| 5 | Sarah | Math | 6 | female |
| 6 | Peter | English | 6 | male |
| 7 | Nathan | English | NULL | male |
+----+---------+---------+-------+--------+
Now you need to query the table and return only rows where the subject is not English.
This is how you write a query with the not equal operator:
SELECT * FROM students WHERE subject <> "English";
The returned result set will be as shown below:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 2 | Natalia | Math | 8 | female |
| 3 | Gary | Math | 6 | male |
| 5 | Sarah | Math | 6 | female |
+----+---------+---------+-------+--------+
3 rows in set (0.00 sec)
When using the not equal operator, you are free to assign the field as the left operand or the right operand.
The position of the field and the expression doesn’t affect the returned result.
The following SELECT statement is the same as the above, although putting the field as the left operand is easier to follow:
SELECT * FROM students WHERE "English" <> subject;
The not equal operator can be used on both strings and numbers data types in your MySQL database.
Here’s another example where you filter the result to return rows where the score is not 6:
SELECT * FROM students WHERE score <> 6;
The returned result set will be as follows:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | English | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 4 | Joe | English | 8 | male |
+----+---------+---------+-------+--------+
3 rows in set (0.00 sec)
Did you notice that there’s one row missing from the result table above?
If you look back to the students table data above, there’s a student that has the score field with NULL value:
+----+--------+---------+-------+--------+
| id | name | subject | score | gender |
+----+--------+---------+-------+--------+
| 7 | Nathan | English | NULL | male |
+----+--------+---------+-------+--------+
While NULL is not equal to 6, a not equal comparison between NULL and another value always returns NULL.
Take a look at the following SQL statement and its results. Notice how even NULL <> NULL also returns NULL:
mysql> SELECT NULL <> 6, NULL <> "English", NULL <> NULL;
+-----------+-------------------+--------------+
| NULL <> 6 | NULL <> "English" | NULL <> NULL |
+-----------+-------------------+--------------+
| NULL | NULL | NULL |
+-----------+-------------------+--------------+
1 row in set (0.00 sec)
This is why a row that has a field with NULL value is excluded when the field is used in the not equal comparison.
Filter multiple values using not equal operator
Finally, you can also add multiple conditionals in your SQL query by using the AND clause in the WHERE clause.
The following SELECT statement has three conditionals using the not equal operator:
SELECT * FROM students WHERE subject <> "English"
AND score <> 7
AND gender <> "male";
The returned result set will be as follows:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 2 | Natalia | Math | 8 | female |
| 5 | Sarah | Math | 6 | female |
+----+---------+---------+-------+--------+
2 rows in set (0.01 sec)
Conclusion
And that’s how you can write MySQL query with the not equal operator.
The not equal operator is a comparison operator that allows you to compare the value between two expressions, two fields, or a field and an expression.
You can use the not equal operator in the WHERE clause to filter the result set returned by your query.
By default, the returned result filters out any row that has NULL value in the field used for comparison.