IS NULL and IS NOT NULL in SQL Server

In this SQL Server tutorial, you’ll learn how to use IS NULL in your T-SQL query in SQL Server with some examples of its usage.

In SQL Server or in any database instance, the value NULL indicates that there is no data. For example, when you are storing an Mobile Number of a Customer, there are 3 possible values that it can take – actual mobile number , empty data and unknown/no data. The unknown/no data is recorded as NULL in the database table.

The predicate or the logical expression can result TRUE or FALSE. NULL in SQL Server brings in the concept of three-valued logic by bringing in the result of UNKNOWN.

It is important to know that NULL does not equal to any value including NULL as each NULL value is treated differently.

If you want to check for NULL value in your T-SQL query, you’ll need to specialized IS NULL and IS NOT NULL operator. In this tutorial, we’ll mainly look at the IS NULL operator in SQL Server.

Syntax

IS NULL

expression IS NULL

IS NOT NULL

expression IS NOT NULL

Parameters

• expression
  • This refers to the field , expression or the value that you wanted to check if it is NULL.

This will return true if the expression is a NULL value. The false value will be returned if it is NOT a NULL.

Example 1 : = and NULL in SQL Server

Let’s try to understand the impact of checking the NULL value using the = operator

The table Person contains records with the Title containing NULL values as shown in the below screenshot.

Try running the below SQL Query where you want to find all the Person records that contains NULL title

SELECT *
  FROM .[Person].[Person]
  WHERE Title = NULL

You will notice that you will get zero records because the WHERE clause “Title=NULL” has evaluated to UNKNOWN instead of TRUE or FALSE.

So, to test for NULL values, you will need to use a different approach.

Example 2 : IS NULL and SELECT statement

The below query check and filters the Person records whose Title contains NULL value using the IS NULL operator.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM .[Person].[Person]
  WHERE Title IS NULL

Example 3 : IS NOT NULL and SELECT statement

The previous example showed how to find if a expression is NULL. If you want to find if the expression is a non null value, you can use the IS NOT NULL operator.

Below is a SQL Query that shows how to use the IS NOT NULL operator to return Person records that contains non-null titles.

SELECT *
  FROM .[Person].[Person]
  WHERE Title IS NOT NULL