SQL - IS NULL Operator

---

---

The SQL IS NULL Operator

The SQL IS NULL operator is used to check if a column contains a NULL value. In SQL, NULL is a special marker that represents missing, undefined, or unknown data. It is not the same as zero, an empty string, or a space.

Since NULL represents the absence of a value rather than an actual value, you cannot compare it using regular operators like = or <>. Instead, IS NULL is the correct way to test for NULL. Any direct comparison with NULL using these operators will always return UNKNOWN instead of TRUE or FALSE.

Syntax

Following is the syntax of the SQL IS NULL operator:

SELECT column1, column2, ...
FROM table_name
WHERE column_name 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:

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;

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

SQL IS NULL with COUNT() Function

The IS NULL operator can be used with the COUNT() function to find how many rows in a table have NULL values in a specific column. Since COUNT(column_name) ignores NULL values by default, you need to explicitly use IS NULL in the WHERE clause to count them.

Syntax

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

SELECT COUNT(*)
FROM table_name
WHERE column_name 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;

The output produced is as shown below:

IS NULL with UPDATE Statement

The IS NULL operator can be used in an UPDATE statement to modify rows where a column contains a NULL value. This is useful for replacing missing or undefined data with a default value.

Syntax

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

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

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:

SQL IS NULL with DELETE Statement

The IS NULL operator can be used in a DELETE statement to remove rows where a column contains a NULL value. This is helpful when you want to delete records with missing or incomplete data.

Syntax

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

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

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:

SQL IS NULL Operator on Multiple Columns

You can use the IS NULL operator on multiple columns to check if more than one column contains NULL values. By combining multiple conditions with AND or OR operators, you can refine the filter criteria.

Syntax

Following is the syntax for checking NULL on multiple columns:

SELECT column1, column2, ...
FROM table_name
WHERE column1 IS NULL
  AND column2 IS NULL;

Example

The following query retrieves all customers whose AGE and ADDRESS are both NULL:

SELECT * FROM CUSTOMERS WHERE AGE IS NULL AND ADDRESS IS NULL;

We get an empty set as there are no customers whose AGE and ADDRESS are both NULL:

Empty set (0.00 sec)

Difference Between NULL, Zero, and Empty String

It is important to understand the difference between NULL, 0 (zero), and an empty string '' in SQL because they represent different concepts:

• NULL: Represents missing, undefined, or unknown data. It is not a value.
• Zero (0): A numeric value that means nothing more than "zero". It is a defined, actual value.
• Empty String (''): A string with no characters. It is a value of length zero but not the same as NULL.

For example,

SELECT * FROM CUSTOMERS WHERE SALARY IS NULL;   -- Finds missing salary data
SELECT * FROM CUSTOMERS WHERE SALARY = 0;       -- Finds salary exactly equal to zero
SELECT * FROM CUSTOMERS WHERE ADDRESS = '';     -- Finds rows with address stored as an empty string

IS NULL Behavior Across SQL Databases

The IS NULL operator works similarly across most SQL databases, but there are slight variations:

• MySQL: Supports IS NULL and IS NOT NULL in all clauses.
• PostgreSQL: Works the same as MySQL but also supports the NULLS FIRST and NULLS LAST options in ORDER BY.
• SQL Server: IS NULL behaves similarly, but special settings like SET ANSI_NULLS can affect equality comparisons with NULL.
• Oracle: Treats empty strings as NULL in most cases, which is different from MySQL and SQL Server.

Important Points About SQL IS NULL

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

• The IS NULL operator is used exclusively to test for missing or undefined values in a column.
• NULL cannot be compared using =, !=, or other standard operators, use IS NULL or IS NOT NULL instead.
• NULL is not the same as zero or an empty string.
• Aggregations like COUNT(column) ignore NULLs unless explicitly filtered.
• When performing arithmetic with NULL, the result is always NULL unless handled with functions like COALESCE() or IFNULL().
• Some databases (e.g., Oracle) treat empty strings as NULL, which may affect query results.
• Always handle NULLs carefully in conditions to avoid unexpected results in queries.