SQL - IS NOT NULL Operator

The SQL IS NOT NULL Operator

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

When you useIS 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.

SinceNULLcannot be compared using operators like=or<>,IS NOT NULLis the correct way to test for non-null values.

Syntax

Following is the syntax of the SQLIS NOT NULLoperator:

SQL CODE

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example

Firstly, let us create a table namedCUSTOMERSusing the following query:

SQL CODE

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:

SQL CODE

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:

IDNAMEAGEADDRESSSALARY1Ramesh32AhmedabadNULL2Khilan25NULL1500.003KaushikNULLKota2000.004Chaitali25MumbaiNULL5Hardik27Bhopal8500.006KomalNULLHyderabad4500.007Muffy24NULL10000.00

Example

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

SQL CODE

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NOT NULL;

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

IDNAMEAGEADDRESSSALARY1Ramesh32AhmedabadNULL3KaushikNULLKota2000.004Chaitali25MumbaiNULL5Hardik27Bhopal8500.006KomalNULLHyderabad4500.00

SQL IS NOT NULL with COUNT() Function

TheIS NOT NULLoperator can be used with theCOUNT()function to count how many rows in a table have non-null values in a specific column. SinceCOUNT(column_name)automatically ignoresNULLvalues, 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:

SQL CODE

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:

SQL CODE

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

The output produced is as shown below:

COUNT(*)5

SQL IS NOT NULL with DELETE Statement

TheIS NOT NULLoperator can be used in aDELETEstatement 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:

SQL CODE

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:

SQL CODE

DELETE FROM CUSTOMERS WHERE SALARY IS NOT NULL;

We get the following result:

SQL CODE

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:

SQL CODE

SELECT * FROM CUSTOMERS;

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

IDNAMEAGEADDRESSSALARY1Ramesh32AhmedabadNULL4Chaitali25MumbaiNULL

SQL IS NOT NULL with UPDATE Statement

TheIS NOT NULLoperator can be used in anUPDATEstatement 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:

SQL CODE

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:

SQL CODE

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

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

SQL CODE

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:

SQL CODE

SELECT * FROM CUSTOMERS;

The table is displayed as follows:

IDNAMEAGEADDRESSSALARY1Ramesh32AhmedabadNULL2Khilan25NULL6500.003KaushikNULLKota7000.004Chaitali25MumbaiNULL5Hardik27Bhopal13500.006KomalNULLHyderabad9500.007Muffy24NULL15000.00

SQL IS NOT NULL with JOINs

TheIS NOT NULLoperator can also be used in combination with SQLJOINclauses 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:

SQL CODE

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 namedORDERSwhich stores order details for customers:

SQL CODE

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:

SQL CODE

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:

SQL CODE

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:

NAMEAMOUNTKaushik3000.00Ramesh2000.00Khilan1800.00

SQL IS NOT NULL with ORDER BY Clause

You can use theIS NOT NULLoperator along with theORDER BYclause 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:

SQL CODE

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:

SQL CODE

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

The output will be as follows:

NAMESALARYMuffy15000.00Hardik13500.00Komal9500.00Kaushik7000.00Khilan6500.00

SQL IS NOT NULL with GROUP BY Clause

TheIS NOT NULLoperator can also be used in combination with theGROUP BYclause 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:

SQL CODE

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:

SQL CODE

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

The output will be as follows:

AGETOTAL_CUSTOMERS241252271321

Important Points About SQL IS NOT NULL

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

TheIS NOT NULLoperator checks for values that are not null, meaning the column contains actual stored data.
NULLin 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 NULLis the correct approach.
It can be used with other SQL clauses and statements such asJOIN,ORDER BY,GROUP BY,DELETE, andUPDATE.
When used with aggregate functions likeCOUNT(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.