MySQL syntax not evaluating with not equal operator in presence of null?

Use the IS NOT NULL operator to compare with NULL values. The syntax is as follows −

SELECT *FROM yourTableName where yourColumnName1 is not null or yourColumnName2 <> anyIntegerValue;

To check the not equal to in presence of null, let us create a table. The query to create a table is as follows −

mysql> create table IsNullDemo
   −> (
   −> ProductId int,
   −> ProductName varchar(100),  
   −> ProductBackOrder int
   −> );
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table with null value to avoid the presence of null. The query to insert records is as follows −

mysql> insert into IsNullDemo values(100,'First-Product',null);
Query OK, 1 row affected (0.14 sec)

mysql> insert into IsNullDemo values(101,'Second-Product',2);
Query OK, 1 row affected (0.22 sec)

mysql> insert into IsNullDemo values(102,'Third-Product',null);
Query OK, 1 row affected (0.20 sec)

mysql> insert into IsNullDemo values(103,'Fourth-Product',4);
Query OK, 1 row affected (0.17 sec)

mysql> insert into IsNullDemo values(104,'Fifth-Product',10);
Query OK, 1 row affected (0.17 sec)

mysql> insert into IsNullDemo values(105,'Sixth-Product',null);
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement. The query to display all records is as follows −

mysql> select *from IsNullDemo;

The following is the output −

+-----------+----------------+------------------+
| ProductId | ProductName    | ProductBackOrder |
+-----------+----------------+------------------+
| 100       | First-Product  |             NULL |
| 101       | Second-Product |                2 |
| 102       | Third-Product  |             NULL |
| 103       | Fourth-Product |                4 |
| 104       | Fifth-Product  |               10 |
| 105       | Sixth-Product  |             NULL |
+-----------+----------------+------------------+
6 rows in set (0.00 sec)

Case 1:

Here is the query to avoid the presence of null. The query is as follows −

mysql> select *from IsNullDemo
   −> where ProductBackOrder is not null or ProductBackOrder <> 2;

The following is the output −

+-----------+----------------+------------------+
| ProductId | ProductName    | ProductBackOrder |
+-----------+----------------+------------------+
|       101 | Second-Product |                2 |
|       103 | Fourth-Product |                4 |
|       104 | Fifth-Product  |               10 |
+-----------+----------------+------------------+
3 rows in set (0.03 sec)

Case 2:

Whenever you want the presence of null (or not equal to 2), use IS NULL concept. The query is as follows −

mysql> select *from IsNullDemo
   −> where ProductBackOrder is null or ProductBackOrder <> 2;

The following is the output −

+-----------+----------------+------------------+
| ProductId | ProductName    | ProductBackOrder |
+-----------+----------------+------------------+
|       100 | First-Product  |             NULL |
|       102 | Third-Product  |             NULL |
|       103 | Fourth-Product |                4 |
|       104 | Fifth-Product  |               10 |
|       105 | Sixth-Product  |             NULL |
+-----------+----------------+------------------+
5 rows in set (0.00 sec)

Jennifer Nicholas

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• Counting presence of a NOT NULL value in MySQL
• Working with NULL and IS NOT NULL in MySQL
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• Enum with NOT NULL in a MySQL field?
• Conditional NOT NULL case MySQL?
• Does NOT EQUAL exist in MySQL?
• Is the !! (not not) operator in JavaScript equivalent to reverse process of not operator?
• Using “not equal” in MySQL?
• Is there a “not equal” operator in Python?
• How MySQL NULL-safe equal operator performs when used with row comparisons?
• MongoDB query which represents not equal to null or empty?
• Display the result with not null value first and then with null value in MySQL
• Empty string in not-null column in MySQL?
• Removing NOT NULL restriction from column in MySQL?
• Set 1 for NOT NULL value in MySQL