SQL Server: IS NULL Condition

In this post explains how to use the IS NULL condition in SQL Server (Transact-SQL) with syntax and examples.

Description

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

Syntax

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

expression IS NULL

Parameters or Arguments

expression

The value to test whether it is a NULL value.

Note

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

Example - With SELECT Statement

Let's look at how to use the IS NULL condition in a SELECT statement in SQL Server.

For example:

SELECT *
FROM employees
WHERE last_name IS NULL;

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

Example - With INSERT Statement

Let's look at how to use the IS NULL condition in an INSERT statement in SQL Server.

For example:

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

This SQL Server IS NULL example will insert records into the employees table records from the contacts table where the first_name contains a null value.

Example - With UPDATE Statement

Lets' look at an example of how to use the IS NULL condition in an UPDATE statement in SQL Server.

For example:

UPDATE employees
SET first_name = 'Unknown'
WHERE first_name IS NULL;

This SQL Server IS NULL example will update records in the employees table where the first_name contains a null value.

Example - With DELETE Statement

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

For example:

DELETE FROM employees
WHERE last_name IS NULL;

This SQL Server IS NULL example will delete all records from the employees table where the last_name contains a null value.