PHPGurukul

MySQL NULL Values

We have seen the SQL SELECT command along with the WHERE caluse to fetch data from a MySQL table, but when we try to give a condition, which compares the field or column value to NULL, it does not work properly.

To handle this type of situation, MySQL provides three operators-

  • IS NULL –  This operator returns true, if the column value is NULL.
  • IS NOT NULL – This operator  returns true, if the column value is not NULL.
  • <=> – This operator compares values, which (unlike the = operator) is true even for two NULL values.

The conditions involving NULL are special. You can’t  use = NULL or !=NULL to look for NULL values in columns. Such comparisons always fail because it is  impossible to tell wheteher they are true or not. Some- times, even NULL=NULL fails.

To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.

For Examples

Table tblstudents

idNameRoll_noClass
1Anuj kumar10806121B.tech
2Sanjeev10883327NULL
3Amit13123121BA
4Rahul23424323NULL

Handling NULL values

Ex:

Select * from tblstudents where Class IS NULL;

OUTPUT

idNameRoll_noClass
2Sanjeev10883327NULL
4Rahul23424323NULL

Ex:

Select * from tblstudents where Class IS NOT NULL;

OUTPUT

idNameRoll_noClass
1Anuj kumar10806121B.tech
3Amit13123121BA

NOTE :

You can see  that = and != do not work with the NULL Values.

Select * from tblstudents where Class=NULL;

Select * from tblstudents where Class!=NULL;

Anuj Kumar

This is Anuj Kumar. I’m a professional web developer with 4+ year experience. I write blogs in my free time. I love to learn new technologies and share with others.
I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials.
.

Recommended Tutorials for you


Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Web Hosting with a FREE domain. On sale ₹99.00/mo

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.