Build Query NULL Value in MySql

View: 443    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

  • AngularJS and REST API

    AngularJS and REST API

    View: 157    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: 132    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: 119    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: 128    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: 396    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: 112    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: 127    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

  • Yet Another Fluent JDBC Wrapper in 200 Lines of Code

    Yet Another Fluent JDBC Wrapper in 200 Lines of Code

    View: 138    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    Those who are not convinced to use Hibernate to manage persistence are forced to use plain old JDBC API. Though powerful, it requires lot of typing to get it right. For example, retrieving user data from database often requires such code snippet:

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