Blog

This SQL Server tutorial explains how to use the IS NOT NULL condition in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) IS NOT NULL condition is used to test for a NOT NULL value.

Syntax

The syntax for the IS NOT NULL condition in SQL Server (Transact-SQL) is:

expression IS NOT NULL

Parameters or Arguments

expression

The value to test where it is a non-NULL value.

Note

• If expression is NOT a NULL value, the condition evaluates to TRUE.
• If expression is a NULL value, the condition evaluates to FALSE.

Example – With SELECT Statement

Let’s look at an example of how to use the IS NOT NULL conditions in a SELECT statement in SQL Server.

For example:

SELECT *
FROM employees
WHERE last_name IS NOT NULL;

This SQL Server IS NOT NULL example will return all records from the employees table where the last_name does not contain a null value.

Example – With INSERT Statement

Let’s look at an example of how to use the IS NOT NULL conditions in an INSERT statement in SQL Server.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name IS NOT NULL;

This SQL Server IS NOT NULL example will insert records into the contacts table where the last_name does not contain a null value in the employees table.

Example – With UPDATE Statement

Let’s look at an example of how to use the IS NOT NULL conditions in an UPDATE statement in SQL Server.

For example:

UPDATE employees
SET status = 'Active'
WHERE last_name IS NOT NULL;

This SQL Server IS NOT NULL example will update records in the employees table where the last_name does not contain a null value.

Example – With DELETE Statement

Let’s look at an example of how to use the IS NOT NULL condition in a DELETE statement in SQL Server.

For example:

DELETE FROM employees
WHERE status IS NOT NULL;

This SQL Server IS NOT NULL example will delete all records from the employees table where the status does not contain a null value.