SQL - IS NOT NULL Operator

---

---

The SQL IS NOT NULL Operator

The IS NOT NULL operator in SQL is used to check whether a column contains a non-null value. In SQL, NULL represents missing, undefined, or unknown data, it is not the same as zero, an empty string, or a space.

When you use IS NOT NULL, the query returns only those rows where the specified column has an actual stored value. This makes it useful for filtering out incomplete or missing information and working only with meaningful data.

Since NULL cannot be compared using operators like = or <>, IS NOT NULL is the correct way to test for non-null values.

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;

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

SQL IS NOT NULL with COUNT() Function

The IS NOT NULL operator can be used with the COUNT() function to count how many rows in a table have non-null values in a specific column. Since COUNT(column_name) automatically ignores NULL values, this is useful when you want to count only rows with actual data.

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;

The output produced is as shown below:

SQL IS NOT NULL with DELETE Statement

The IS NOT NULL operator can be used in a DELETE statement to remove rows where a column contains a non-null value. This is useful when you want to delete records that have specific fields filled in.

Syntax

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

DELETE FROM table_name
WHERE column_name 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;

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:

SQL IS NOT NULL with UPDATE Statement

The IS NOT NULL operator can be used in an UPDATE statement to modify only those rows where a column contains a non-null value. This is useful when you want to update records that already have existing data in a particular field.

Syntax

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

UPDATE table_name
SET column_name = new_value
WHERE column_name 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;

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:

SQL IS NOT NULL with JOINs

The IS NOT NULL operator can also be used in combination with SQL JOIN clauses to retrieve only those matching records where a certain column has a non-null value. This is useful when you want to join tables but exclude rows with missing or undefined values from one of them.

Syntax

Following is the syntax of the SQL IS NOT NULL operator used with an INNER JOIN:

SELECT a.column1, b.column2
FROM table_a a
INNER JOIN table_b b
ON a.id = b.a_id
WHERE b.column_name IS NOT NULL;

Example

Consider another table named ORDERS which stores order details for customers:

CREATE TABLE ORDERS(
   ORDER_ID INT NOT NULL,
   CUSTOMER_ID INT,
   AMOUNT DECIMAL(10, 2),
   PRIMARY KEY (ORDER_ID)
);

Insert sample data into the ORDERS table:

INSERT INTO ORDERS VALUES
(101, 3, 3000.00),
(102, NULL, 1500.00),
(103, 5, NULL),
(104, 1, 2000.00),
(105, 2, 1800.00);

The following query retrieves customer names and their order amounts only for orders where the amount is not null:

SELECT c.NAME, o.AMOUNT
FROM CUSTOMERS c
INNER JOIN ORDERS o
ON c.ID = o.CUSTOMER_ID
WHERE o.AMOUNT IS NOT NULL;

On executing the above query, it will produce the following output:

SQL IS NOT NULL with ORDER BY Clause

You can use the IS NOT NULL operator along with the ORDER BY clause to sort results that contain only non-null values in a specific column. This helps to organize filtered results in ascending or descending order based on meaningful data.

Syntax

Following is the syntax of the SQL IS NOT NULL operator with ORDER BY clause:

SELECT * FROM table_name
WHERE column_name IS NOT NULL
ORDER BY column_name ASC|DESC;

Example

The following query lists all customers who have a salary (non-null) in descending order of their salary:

SELECT NAME, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC;

The output will be as follows:

SQL IS NOT NULL with GROUP BY Clause

The IS NOT NULL operator can also be used in combination with the GROUP BY clause to group rows based on non-null values in a column. This is helpful when summarizing or aggregating data only for fields containing actual values.

Syntax

Following is the syntax of the SQL IS NOT NULL operator with GROUP BY clause:

SELECT column_name, aggregate_function(*)
FROM table_name
WHERE column_name IS NOT NULL
GROUP BY column_name;

Example

The following query groups customers by age and counts how many customers fall into each age group, ignoring rows where the age is null:

SELECT AGE, COUNT(*) AS total_customers
FROM CUSTOMERS
WHERE AGE IS NOT NULL
GROUP BY AGE;

The output will be as follows:

Important Points About SQL IS NOT NULL

Following are some of the important points you should know about the SQL IS NOT NULL operator:

• The IS NOT NULL operator checks for values that are not null, meaning the column contains actual stored data.
• NULL in SQL represents missing, undefined, or unknown data, and is not equivalent to zero, an empty string, or a blank space.
• Comparison operators like = or <> cannot be used to check for nulls; IS NOT NULL is the correct approach.
• It can be used with other SQL clauses and statements such as JOIN, ORDER BY, GROUP BY, DELETE, and UPDATE.
• When used with aggregate functions like COUNT(column_name), null values are ignored, and only non-null entries are counted.
• Performance can improve when filtering out null values, especially in indexed columns.