How to select null values in MySql. Misunderstanding NULL is a common mistake beginners do while writing a MySql query. While writing a query in MySql most of them compare column name with NULL. In MySql NULL is nothing or in simple word it is an Unknown Value so if you use comparison operator for NULL values you’ll get an empty result.
In this tutorial, We are going to learn how to write a query to select NULL values in MySql.
Difference Between Inner Join and Left Join
To understand this concept, let’s first create a table and insert some dummy values.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create table emp ( id int(10) NOT NULL AUTO_INCREMENT, name varchar(200), emp_pan_card varchar(200), PRIMARY KEY(id) ); insert into emp (name,emp_pan_card) values('John',NULL); insert into emp (name,emp_pan_card) values('smith','DDS9167GH'); insert into emp (name,emp_pan_card) values('Amit',NULL); insert into emp (name,emp_pan_card) values('vikash','DD47H86GH'); |
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select * from emp; +----+--------+--------------+ | id | name | emp_pan_card | +----+--------+--------------+ | 1 | John | NULL | | 2 | smith | DDS9167GH | | 3 | Amit | NULL | | 4 | vikash | DD47H86GH | +----+--------+--------------+ 4 rows in set (0.00 sec) |
Related Post on MySql
Difference Between MyISAM and InnoDB Storage Engine
How to Increase PHPMyAdmin File Size Import Limit
How to Select NULL Values in MySql
How to Select Null Values in MySql
Let’s see what happens when comparing emp_pan_card with NULL value.
|
1 2 |
mysql> select * from emp where emp_pan_card = NULL; Empty set (0.00 sec) |
Second Mistake
The NULL and ‘ ‘ (empty string) both are different. NULL means the value is unknown while an empty string represents a blank value.
|
1 2 |
mysql> select * from emp where emp_pan_card = ''; Empty set (0.00 sec) |
This query will return an empty result. In MySql, NULL is never equal to anything, even another NULL.
** Never use arithmetic comparison operators such as =, <, or <> for NULL. If you use any arithmetic operator with NULL, the result is NULL.
To select rows which contain NULL values in a table, you have to use IS NULL condition.
|
1 2 3 4 5 6 7 8 |
mysql> select * from emp where emp_pan_card IS NULL; +----+------+--------------+ | id | name | emp_pan_card | +----+------+--------------+ | 1 | John | NULL | | 3 | Amit | NULL | +----+------+--------------+ 2 rows in set (0.00 sec) |
To select rows where emp_pan_card column is not null we have to use IS NOT NULL condition.
|
1 2 3 4 5 6 7 8 |
mysql> select * from emp where emp_pan_card IS NOT NULL; +----+--------+--------------+ | id | name | emp_pan_card | +----+--------+--------------+ | 2 | smith | DDS9167GH | | 4 | vikash | DD47H86GH | +----+--------+--------------+ 2 rows in set (0.00 sec) |
For further reading on this topic
