How to Query NULL Value in MySql

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

10 point/1 review File has been tested

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

How to Query NULL Value in MySql Posted on 08-10-2016  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. 10/10 346

Comment:

To comment you must be logged in members.

Files with category

  • PHP source code: get content of google search

    PHP source code: get content of google search

    View: 26    Download: 1   Comment: 0

    Category: Php&mySql     Fields: Other

    How to get the results of google search and display on your website. This PHP code can do that. Refer to the following code.

  • PHP as a Scripting Language for C#

    PHP as a Scripting Language for C#

    View: 35    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    When creating .NET applications (including desktop and web applications), it may be useful to allow extending the application using some scripting language. The users of the application can write simple scripts to configure the application, modify...

  • How to Picking the Brains of Your Customers with Microsoft’s Text Analytics

    How to Picking the Brains of Your Customers with Microsoft’s Text Analytics

    View: 4113    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    With the explosion of machine learning services in recent years, it has become easier than ever for developers to create “smart apps”. In this article, I’ll introduce you to Microsoft’s offering for providing machine-learning capabilities to apps.

  • How to MySqli Tutorial PHP MySqli Extension

    How to MySqli Tutorial PHP MySqli Extension

    View: 414    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    PHP provides three api to connect mysql Database.

  • Make Laravel Artisan Commands

    Make Laravel Artisan Commands

    View: 394    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    Artisan is the command line tool used in Laravel framework. It offers a bunch of useful command that can help you develop application quickly. Apart from Artisan available commands, you can create your own custom commands to improve your workflow.

  • Check if a Number is a Power of 2

    Check if a Number is a Power of 2

    View: 351    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    How to check if a number is a power of 2. To understand this question, let’s take some example.

  • Concatenate columns in MySql

    Concatenate columns in MySql

    View: 418    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    Artisan is the command line tool used in Laravel framework. It offers a bunch of useful command that can help you develop application quickly. Apart from Artisan available commands, you can create your own custom commands to improve your workflow

  • How to Query NULL Value in MySql

    How to Query NULL Value in MySql

    View: 346    Download: 0   Comment: 0

    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...

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