. Advertisement .
..3..
. Advertisement .
..4..
You are going to learn how to make a MySQL query with not equal operators. They are essential tools that can help you perform many comparison tasks on your databases.
MySQL Query With Not Equal Operators
“<>” and “!=” are called “not equal operators” in MySQL. As straightforward as their name suggests, their job is to evaluate two SQL expressions and compare them.
If these expressions have the same value, the operators return 0. The <> and != only returns if the expressions aren’t equal. You may also get NULL if one of the two expressions is NULL.
This simple example demonstrates how the <> operator works. The first result value is 1 because 2 and 5 aren’t equal. Meanwhile, a comparison between 5 and 5 should yield 0.
Example:
mysql> SELECT 2 <> 5;
+--------+
| 2 <> 5 |
+--------+
| 1 |
+--------+
mysql> SELECT 5 <> 5;
+--------+
| 5 <> 5 |
+--------+
| 0 |
+--------+
You can use <> and !+ interchangeably. They should produce the same result.
Example:
mysql> SELECT 2 != 5;
+--------+
| 2 != 5 |
+--------+
| 1 |
+--------+
Remember that you can combine several operators in a single query. The results will be displayed in a table.
Example:
mysql> SELECT 2 <> 5, 2 != 5, 5 <> 5;
+--------+--------+--------+
| 2 <> 5 | 2 != 5 | 5 <> 5 |
+--------+--------+--------+
| 1 | 1 | 0 |
+--------+--------+--------+
The not equal operators in MySQL can’t just take integer expressions. They can also evaluate other data types, including strings, decimal values, floating-point numbers, and datetime.
Like with other comparison operations, MySQL carries out necessary type conversions when <> or !+ has operands of two different data types. Most of these conversions happen implicitly.
For example, when compared with a decimal value, an integer is converted to a decimal. This not equal operation gives us the correct result even though the operands are represented by different types (decimal and integer).
mysql> SELECT 1 <> 1.00;
+-----------+
| 1 <> 1.00 |
+-----------+
| 0 |
+-----------+
You can also use <> and !+ between a number and a string:
mysql> SELECT 1 <> '1';
+----------+
| 1 <> '1' |
+----------+
| 0 |
+----------+
Comparison between two strings are also possible:
mysql> SELECT 'aa' <> 'aa', 'aa' <> 'ab';
+--------------+--------------+
| 'aa' <> 'aa' | 'aa' <> 'ab' |
+--------------+--------------+
| 0 | 1 |
+--------------+--------------+
It is time to integrate the not equal in a more complicated manner. Suppose we have this small table containing data about the staff at a company. It has several pieces of information, including their first names, last names, titles, and birth dates. You can use the <> and != operators to select employees that match certain criteria.
|lastname |firstname|title |birthDate |
|------------|---------|---------------------|-----------------------|
|Davis |Sara |CEO |1958-12-08 00:00:00.000|
|Funk |Don |Vice President, Sales|1962-02-19 00:00:00.000|
|Lew |Judy |Sales Manager |1973-08-30 00:00:00.000|
|Peled |Yael |Sales Representative |1947-09-19 00:00:00.000|
|Buck |Sven |Sales Manager |1965-03-04 00:00:00.000|
|Suurs |Paul |Sales Representative |1973-07-02 00:00:00.000|
|King |Russell |Sales Representative |1970-05-29 00:00:00.000|
|Cameron |Maria |Sales Representative |1968-01-09 00:00:00.000|
|Dolgopyatova|Zoya |Sales Representative |1976-01-27 00:00:00.000|
If you want to select only those who aren't a sales representative, you use this query:
mysql> SELECT * FROM sample WHERE title <> 'Sales Representative';
In short, it removes every row that contains 'Sales Representative' in the 'title' column.
Output:
|lastname|firstname|title |birthDate |
|--------|---------|---------------------|-----------------------|
|Davis |Sara |CEO |1958-12-08 00:00:00.000|
|Funk |Don |Vice President, Sales|1962-02-19 00:00:00.000|
|Lew |Judy |Sales Manager |1973-08-30 00:00:00.000|
|Buck |Sven |Sales Manager |1965-03-04 00:00:00.000|
The not equal operators also work nicely with AND and OR to carry out finer-grained data selection.
In this query, we use <> and date comparison in MySQL to select employees that don’t work as sales representatives and were born after 1960.
mysql> SELECT * FROM sample WHERE title <> 'Sales Representative' AND DATE(birthDate) > '1960-12-31';Output:
|lastname|firstname|title |birthDate |
|--------|---------|---------------------|-----------------------|
|Funk |Don |Vice President, Sales|1962-02-19 00:00:00.000|
|Lew |Judy |Sales Manager |1973-08-30 00:00:00.000|
|Buck |Sven |Sales Manager |1965-03-04 00:00:00.000|
Conclusion
It is easy to make a MySQL query with not equal operators <> and !=, even when they are used in conjunction with other operators. They work with both numbers and strings, which are converted automatically for comparison.
Leave a comment