Loading...

Build Query NULL Value in MySql

View: 474    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: 25    Download: 2   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...

  • Angular 6 Starter with Laravel 5.6 API Service

    Angular 6 Starter with Laravel 5.6 API Service

    View: 62    Download: 0   Comment: 0

    Category: Javascript     Fields: none

    Angular 6 and Laravel 5.6 This project is a starter for creating interface with Angular using bootstrap && css && sass and using Laravel 5.6 for api requests. Demo Installation This project is divided in two parts (projects) and before use them you...

  • Simple Richtext Editor Based on pellJS

    Simple Richtext Editor Based on pellJS

    View: 26    Download: 0   Comment: 0

    Category: Javascript     Fields: none

    A simple visual editor for websites using the pell javascipt. It also has the option to switch between visual editor mode and source code mode. I will upload an update for new functionality soon. Source Code Editor Visual Editor

  • Data Visualization for BI: How to Design Layouts for .NET Financial Reports

    Data Visualization for BI: How to Design Layouts for .NET Financial Reports

    View: 37    Download: 0   Comment: 0

    Category: Javascript     Fields: Other

    With the Active Reports Server, you can have a multi-tenant environment where users from various departments, companies, or other specifications can log in, view their reports (and only their reports), export the data, or set up a distribution...

  • AngularJS and REST API

    AngularJS and REST API

    View: 204    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: 168    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: 157    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: 169    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...

 
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