Loading...
Loading...
Loading...
Loading...

Build Query NULL Value in MySql

View: 536    Dowload: 0   Comment: 0   Post by: hanhga   Category: Javascript   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.

Let’s first create a table.

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)

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

Build 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 12-10-2016 

Comment:

To comment you must be logged in members.

Files with category

  • Mini Youtube Using ReactJS

    Mini Youtube Using ReactJS

    View: 156    Download: 3   Comment: 0

    Category: Javascript     Fields: none

    This is one the best starter for ReactJS. MiniYoutube as the name suggests is a youtube like website developed using reactJS and youtube API. This project actually let's you search , play and list youtube videos. Do check it out and start learning...

  • AngularJS and REST API

    AngularJS and REST API

    View: 309    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    This is a tutorial for those interested in a quick introduction to AngularJS and REST API. We will build the familiar Periodic Table of the Elements found in every chemistry textbook, and allow the user to select a Chemical Element by clicking on...

  • Collective Intelligence, Recommending Items Based on Similar Users' Taste

    Collective Intelligence, Recommending Items Based on Similar Users' Taste

    View: 242    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Using Collaborative Filtering to find people who share tastes, and for making automatic recommendations based on things that other people like.

  • Think Like a Bird for Better Parallel Programming

    Think Like a Bird for Better Parallel Programming

    View: 219    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Coding an application to run in parallel is hard, right? I mean, it must be hard or we’d see parallel programs everywhere. All we'd see are slick parallel apps that use every available core effortlessly. Instead multi-threaded apps are the exception...

  • Getting Started with the Bing Search APIs

    Getting Started with the Bing Search APIs

    View: 238    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Bing Search API is a set of REST interfaces that find web pages, news, images, videos, entities, related searches, spelling corrections, and more in response to queries from any programming language that can generate a web request. Applications that...

  • Brief Introduction of SocketPro High Performance and Scalable Persistent Message Queue

    Brief Introduction of SocketPro High Performance and Scalable Persistent Message Queue

    View: 558    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Continuous in-line request/result batching, real-time stream sending/processing, asynchronous data transferring and parallel computation for best performance and scalability

  • Iteration Over Java Collections with High Performance

    Iteration Over Java Collections with High Performance

    View: 210    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Java developers usually deal with Collections such as ArrayList, HashSet, Java 8 come with lambda and streaming API helps us to easily work with Collections. In most cases, we work with few thousands of items and performance isn't a concern. But in...

  • SR2JLIB - A Symbolic Regression Library for Java

    SR2JLIB - A Symbolic Regression Library for Java

    View: 210    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Grammar-Guided Genetic Programming library featuring: multi-threading, just-in-time compilation of individuals, dynamic class loading, and JNI interfacing with C/C++ code

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