52 SQL - IS NULL

Let's assume a table with NULL values in some of its fields. These fields indicate that no values are present in them. SQL allows users to create new records or modify existing ones without specifying a value for a field. If no value is provided, the field is stored with a NULL value.

In SQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (negation of NULL values) operators.

The SQL IS NULL Operator

The SQL IS NULL operator is used to check whether a value in a column is NULL. It returns true if the column value is NULL; otherwise false.

The NULL is a value that represents missing or unknown data, and the IS NULL operator allows us to filter for records that contain NULL values in a particular column.

Syntax

Following is the syntax of IS NULL operator −

SELECT column_name1, column_name2, column_name3, ... , column_nameN
FROM table_name
WHERE column_nameN IS NULL;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
 ID INT NOT NULL, 
 NAME VARCHAR(20), 
 AGE INT, 
 ADDRESS CHAR(25), 
 SALARY DECIMAL(18, 2), 
 PRIMARY KEY(ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', NULL ),
(2, 'Khilan', 25, NULL, 1500.00 ),
(3, 'Kaushik', NULL, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', NULL ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', NULL, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, NULL, 10000.00 );

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	NULL	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	NULL	10000.00

IS NULL with SELECT Statement

We can use the IS NULL operator with a SELECT statement to filter the records with NULL values.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table where the ADDRESS is null −

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NULL;

Output

On executing the above query, it will generate the output as shown below −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	NULL	1500.00
7	Muffy	24	NULL	10000.00

IS NULL with COUNT() Function

We can also use the IS NULL operator with the COUNT() function in SQL to count the number of records with NULL values in a particular column.

Syntax

Following is the syntax of IS NULL operator with the COUNT() function −

SELECT COUNT(column_name)
FROM table_name
WHERE condition IS NULL;

Example

The following query returns the count of records have a blank field (NULL) in SALARY column of the CUSTOMERS table −

SELECT COUNT(*) FROM CUSTOMERS WHERE SALARY IS NULL;

Output

The output produced is as shown below −

COUNT(*)
2

IS NULL with UPDATE Statement

We can use the UPDATE statement with the "IS NULL" operator in SQL to update records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL operator with the UPDATE statement in SQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NULL;

Example

In the following query, we are updating the blank (NULL) records of the AGE column to a value of 48 −

UPDATE CUSTOMERS SET AGE = 48 WHERE AGE IS NULL;

Output

When we execute the program above, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

To check whether the table has been updated or not, execute the SELECT query below −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	NULL	1500.00
3	Kaushik	48	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	48	Hyderabad	4500.00
7	Muffy	24	NULL	10000.00

IS NULL with DELETE Statement

We can also use the DELETE statement with IS NULL operator to delete records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL operator with the DELETE statement in SQL −

DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NULL;

Example

In the following query, we are deleting the blank (NULL) records present in the SALARY column of CUSTOMERS table −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

We get the following result −

Query OK, 2 rows affected (0.01 sec)

Verification

Execute the SELECT query given below to check whether the table has been changed or not −

SELECT * FROM CUSTOMERS;

If we compile and run the program, the result is produced as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	NULL	1500.00
3	Kaushik	NULL	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	NULL	10000.00