Loading...

How to Query NULL Value in MySql

View: 487    Dowload: 0   Comment: 0   Post by: hanhga   Category: Php&mySql   Fields: Other

Misunderstanding NULL is common mistake beginners do while writing MySql query. While quering in MySql they compare column name with NULL. In MySql NULL is nothing or in simple word it isUnknown Value so if you use comparison operator for NULL values you’ll get empty result.

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');
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)

How to Query NULL Value in MySql

Let’s see what happens when comparing emp_pan_card with NULL value.

mysql> select * from emp where emp_pan_card = NULL;
Empty set (0.00 sec)

Second Mistake

NULL and ‘ ‘ (empty string) are different thing. NULL means value is unknown while empty string represents blank value.

mysql> select * from emp where emp_pan_card = '';
Empty set (0.00 sec)

This query will return empty result. In MySql, a 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 value in mysql, you have to use IS NULL.

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 you have to use IS NOT NULL.

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

How to Query NULL Value in MySql

Misunderstanding NULL is common mistake beginners do while writing MySql query. While quering in MySql they compare column name with NULL. In MySql NULL is nothing or in simple word it isUnknown Value so if you use comparison operator for NULL values you’ll get empty result.

Posted on 08-10-2016 

Comment:

To comment you must be logged in members.

Files with category

  • Twitter-like Hashtag Function in PHP

    Twitter-like Hashtag Function in PHP

    View: 0    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    If you ever want a function to hashtag and style words within a string which has '#' next to it like twitter. This piece of code will help

  • PSITS Automated Voting System

    PSITS Automated Voting System

    View: 24    Download: 3   Comment: 0

    Category: Php&mySql     Fields: none

    A free sourcecode for PSITS Automated Voting System develop in PHP programming language. The purposed of the system is to automate the process of voting and maintain the quality of data

  • PHP-University Application System

    PHP-University Application System

    View: 30    Download: 6   Comment: 0

    Category: Php&mySql     Fields: none

    Greeting from Malawi the warm heart of Africa. I developed this university registration system project just to share with the people who can make use of the project like this and at the same time for others like students who can learn from it. This...

  • School Event Management System in PHP/MSQLi

    School Event Management System in PHP/MSQLi

    View: 22    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    This School Event Management System can create school events such as Volleyball games, Basketball, Cultural presentation, Election of school officers etc . During school election Instead of having a compile list of candidates and voters this system...

  • Resort Reservation System with PayPal/Credit Card/Debit Card Payment

    Resort Reservation System with PayPal/Credit Card/Debit Card Payment

    View: 27    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    This reservation system has the ability to help its customers find available rooms, cottages and even function hall for their convenience . And in here, they will also have the idea of the room rates where they can quickly reserve for their family...

  • Activity log

    Activity log

    View: 20    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    Simple program to track user's activity log-in time and online/offline status.

  • Electricks - eCommerce

    Electricks - eCommerce

    View: 22    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    Electricks is an e-commerce website built using PHP, Bootstrap and some jQuery plugins. It has all the major functionalities of a typical e-shop. This project was used to present in Southern Luzon State University - Lucban. Feel free to use this code...

  • Simple Email App Using PHP

    Simple Email App Using PHP

    View: 18    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    Creating a simple PHP script using mail function for sending out emails.

 
File suggestion for you
Loading...
File top downloads
Loading...
Loading...
Codetitle - library source code to share, download the file to the community
Copyright © 2018. All rights reserved. codetitle Develope by Vinagon .Ltd