Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
106 Views
Advertisements