MySQL IS NOT EQUAL TO Operator

In this tutorial, we will learn about the “not equal” operator in MySQL which is used to find the records which are not equal to a given expression. So, let’s get started!

Introduction to MySQL Not Equal To Operator

The “Not equal to” operator is exactly opposite to the equal to operator. When you want to find the records which are not fulfilling the condition, the not equal operator is used.

MySQL provides us with the two symbols to define the “not equal to” operator – <>, and !=.

Note that, both symbols are used for performing the same operation. The only difference between these two is that the symbol <> follows the ISO standards while the != doesn’t.

However, the (!=) symbol is used in almost every programing language to denote the not equal operation.

As said earlier, the not equal operator is used to omit the records which match the given condition and return the rest. For example, if you want to find the students which have a blood group other than A, then you can use the not equal operator.

MySQL Not Equal Operator Syntax

The syntax of the MySQL, not equal operator is straightforward.

SELECT colNames
FROM tableName
WHERE col != value

---or

SELECT colNames
FROM tableName
WHERE col <> value
Code language: SQL (Structured Query Language) (sql)

Here, both syntaxes will produce exactly the same result because both symbols perform the same operations.

MySQL Not Equal Operator Examples

Now let’s take examples of the not equal operator.

For this, we will need a table and some data in it to perform the operation.

Note that, the table will also contain the NULL values so that we can take examples of having the NULL values as well.

Following are the table schema and the table data.

As you can see, we have ten records in our table which contains the students’ details such as name, age and blood group. As you can see, the blood group column is set to accept the NULL values and there are a few NULL values present in the table too.

Now, let’s write a query to find the students who have blood groups other than A+.

SELECT * FROM stud_details WHERE blood_group != 'A+';Code language: SQL (Structured Query Language) (sql)

As you can see, it automatically escaped the NULL values because the not equal operator doesn’t work with the NULL values. We will discuss it later in this tutorial.

Now, let’s run the same query using the <> symbol to see the result.

SELECT * FROM stud_details WHERE blood_group <> 'A+';Code language: SQL (Structured Query Language) (sql)

As you can see, we have got the same result.

Now, what if you want to find the records which have any value other than null? Let’s try to run a query with the not equal operator with the NULL value.

SELECT * FROM stud_details WHERE blood_group <> NULL;
SELECT * FROM stud_details WHERE blood_group != NULL;
Code language: SQL (Structured Query Language) (sql)

As you can see, both queries return an empty set. However, there are multiple records present in the table which have values other than NULL.

In this case, we have to use the IS NOT NULL operator.

SELECT * FROM stud_details WHERE blood_group IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

As you can see, now we have got the records that don’t have the NULL values.

Conclusion

In this tutorial, we have learned about the “not equal” operator in MySQL which is used to return the records which do not match the given expression. This is a very easy topic to understand and learn. We have demonstrated some simple examples here. However, you can try it with the subqueries or nested subqueries and some complex queries as well.

Reference

MySQL official documentation on the ‘not equal’ operator.