NULL

A NULL value represents missing unknown data. By default, a table column can hold NULL values.
we will show you IS NULL and IS NOT NULL Operator.

The basic syntax is as follows:

SELECT Column_Name(s) FROM Table_Name

First, create a new table named contacts by using the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS contacts (      contacid INT(4) AUTO_INCREMENT PRIMARY KEY,    name varchar(20) NOT NULL,    ofcphone varchar(15),    homephone varchar(15)  )  ENGINE=InnoDB;

Each contact has contact name, business phone and home phone.

Second, insert data into the contacts table by using the INSERT statement:

INSERT INTO contacts(name,ofcphone,homephone) VALUES ('Kailash','(022) 255-5115',NULL),      ('Satish',NULL,'(022) 272-1106'),      ('Rajesh','(022) 765-8031','(022) 655-8589'),      ('Hemant',NULL,'(022) 754-3151');

Some contacts have only home phone or business phone. To get all the contact name and phone from the contacts table, you use the following query:

SELECT Name, ofcphone, homephone FROM contacts;

Name	ofcphone	homephone
Kailash	(022) 255-5115	NULL
Satish	NULL	(022) 272-1106
Rajesh	(022) 765-8031	(022) 655-8589
Hemant	NULL	(022) 754-3151

However, it would be nice if we can get the contact’s home phone if the contact’s business phone is not available. This is where the IFNULL function comes to play. The IFNULL function returns home phone if the business phone is  NULL.

Third, use the following query to get the names and phones of all the contacts:

SELECT name, IFNULL(of phone,home phone) phone FROM contacts;

Name	Phone
Kailash	(022) 255-5115
Satish	(022) 272-1106
Rajesh	(022) 655-8589
Hemant	(022) 754-3151