MySQL Not Equal Operator Simplified With Practical Examples

So your MySQL database skills are improving, and you’ve been tasked with running a database SQL query that returns a set of rows where a specified identifier is not of a specific value. While a newbie might scratch their head first to understand the task and then how to implement it, a more experienced user know that this can be accomplished easily using the MySQL Not Equal operator.

This MySQL operator is also denoted as <> OR != and it’s main purpose is to test for inequality in the given query and return the appropriate rows.

ALSO READ: MySQL Count – The Ultimate Guide on MySQL Count() Function

What Is MySQL Not Equal?

If you’re wondering what is NOT EQUAL in MySQL, we’ve hinted in the above introduction that the inequality operator in MySQL is to used to check that the rows that are returned by a SQL query do not match a certain value. The syntax looks like this:

<>;
 
OR
 
!=;
 
So if you have a Student table that look like this:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
And you want run a query that returns all students except those from the USA, you’ll write an SQL query using the MySQL Not Equal To operator that looks like this:
 
SELECT COUNT(*)
FROM Student
WHERE country <> "USA";
 
or
 
SELECT COUNT(*)
FROM Student
WHERE country != "USA";
 
Ultimately, you can now see where the <> OR != Not Equal To operators are applied to ensure the appropriate resultset is returned to you.
 
When the above query is executed, you’re going to get a resultset that looks like this:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
Congratulations, you now know how to use the Not Equal operator in MySQL database. But, it doesn’t end there, let’s dive in some more!
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]
 
 
Practical Uses of MySQL Does Not Equal
 
Let’s look at some practice uses of the <> OR != operator when it comes to MySQL database queries. But before we proceed, let’s answer some pertinent questions often asked:
 
1. How Do You Say Not Equal in SQL
 
SQL (Structured Query Language) provides the NOT EQUAL operator to enable you to check if two query expressions are equal or not. So if the expression is not equal, the condition will evaluate to true and no matched results are returned; on the other hand if the condition is equal, it will evaluate to false and all values returned.
 
2. Can you use != in SQL?
 
ANS1/SQL 92 standard does not specifically support the != operator in its specifications. However, some database management systems such as MySQL do provide support for it.
 
3. What Does != Mean in SQL?
 
The != operator is the NOT EQUAL operator supported by some database management systems such as MySQL. It’s used to test an expression to see if the condition is met or not and returns a TRUE or FALSE value.
 
Also, <> operator can be used to achieve the same objective.
 
Now, let’s proceed with some practical examples as promised above.
 
MySQL Not Equal Null
 
If you want to evaluate for a NOT NULL value in a MySQL query statement, you can use the Not Equal operator to see if the condition equates to TRUE or FALSE. Therefore, if the query returns a NULL value, then the condition will equate to FALSE whereas if the query returns a NOT NULL value, the condition will equate to TRUE.
 
Here’s how it works, take a look at this Student table again:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       |                |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia |                |
+--------+----------------------+-----------+----------------+
 
 
Now, let’s run a query that return all students with a home_city value of not NULL. Btw – NULL simply means empty or no value at all. Here’s what the query will look like:
 
SELECT COUNT(*)
FROM Student
WHERE home_city IS NOT NULL;
 
And the results returned by the above query will be:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT004 | Alicia K.West        | England   | Bangalore      |
+--------+----------------------+-----------+----------------+
 
 
As you can see, those students whose home_city value is NULL were not returned. This example is how you use the NOT NULL to return results in MySQL that is not null or empty.
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]
 
 
MySQL Not Equal String
 
There will be occasions where you need to test the expression to see if the condition matches a string value; this is where the MySQL Not Equal String is used.
 
Take a look again as this Student table:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
And you want to run a query that returns all students except the one from the England, you’ll write an SQL query using the operator that looks like this:
 
SELECT COUNT(*)
FROM Student
WHERE country != "England";
 
or
 
SELECT COUNT(*)
FROM Student
WHERE country &amp;amp;amp;amp;lt;&amp;amp;amp;amp;gt; "England";
 
Hence the result returned by the query will be:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
Multiple Values
 
Another practical usage the Not Equal operator is when it comes to dealing with multiple values. So let’s take at a look at the MySQL Not Equal Multiple Values scenario.
 
Again, let’s go back to our Student table:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
Now, we want to display all students but not include those with sdt_id value of SDT002, SDT004, and SDT005. We’ll run a query like this:
 
SELECT COUNT(*)
FROM Student
WHERE sdt_id NOT IN (SDT002,SDT004,SDT005);
 
And the query result will be:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
+--------+----------------------+-----------+----------------+
 
 
Final Thoughts
 
In summary, the implication of the NOT EQUAL in MySQL is significant. It’s a vital operator that allows us to return database query results that meets a specific criteria. So what are your thoughts on this operator? Let us know below!
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]