SQL - IS NOT NULL

A NULL value indicates a missing or unknown value. It appears to be blank and does not contain any data. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. For checking null values we can use two basic operators.

• IS NULL
• IS NOT NULL

The SQL IS NOT NULL Operator

The SQL IS NOT NULL operator is used to filter data by verifying whether a particular column has a not-null values. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.

By using the IS NOT NULL operator, we can only fetch the records that contain valid data in a particular column.

Syntax

Following is the syntax of the SQL IS NOT NULL operator −

SELECT column_names
FROM table_name
WHERE column_name IS NOT 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 −

Example

In the following query, we are going to return all the records from the CUSTOMERS table where the ADDRESS is not null −

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NOT NULL;

Output

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

IS NOT NULL with COUNT() Function

We can use the IS NOT NULL operator along with the SQL COUNT() function to count only the non-null values in a specific column.

Syntax

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

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

Example

The following query returns the count of all rows in the CUSTOMERS table where the SALARY column is not null −

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

Output

The output produced is as shown below −

IS NOT NULL with DELETE Statement

In SQL, we can delete all rows that do not contain NULL values in a specific column using the DELETE statement with IS NOT NULL operator.

Syntax

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

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

Example

In the following query, we are deleting records which are not null in the SALARY column of the CUSTOMERS table −

DELETE FROM CUSTOMERS WHERE SALARY IS NOT NULL;

Output

We get the following result −

Query OK, 5 rows affected (0.02 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 −

IS NOT NULL with UPDATE Statement

We can use the UPDATE statement with the IS NOT NULL operator in SQL to update records with not-null records in a particular column.

Syntax

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

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

Example

Truncate the CUSTOMERS table and reinsert all the 7 records into it again. The following query, increments all the values in the SALARY column of the with 5000, where the salary value is not null −

UPDATE CUSTOMERS SET SALARY = SALARY+5000 WHERE SALARY IS NOT NULL;

Output

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

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  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 −