SQL IS NULL Function

The SQL IS NULL is used to check whether the user specified expression is NULL or not, and if it is NULL then TRUE will be returned, otherwise it returns FALSE. In this article we will show you, How to write, or Use IS NULL in SQL Server with example

SQL IS NULL Syntax

Before getting into the practical example, the basic syntax behind the SQL Server IS NULL is as follows:

-- -- SQL Server IS NULL Syntax
SELECT Column_Names 
FROM Table
WHERE Expression IS NULL

TIP: It is always advisable to use IS NULL to look for NULL values

Simple SQL IS NULL Example 1

In this example we will show you, How to write a simple SELECT Statement with IS NULL in SQL Server. For this demonstration, We are going to use the [TenCustomers] table present in our [SQL Tutorial] Database. Following screenshot will show you the data inside the table

In this example, we will use SQL Server IS NULL to return all the records from [TenCustomers] table, whose Last Name is NULL value

--Example for SQL Server IS NULL
USE [SQL Tutorial]
GO
SELECT [CustomerKey]
      ,[FirstName]
      ,[LastName]
      ,[EmailAddress]
      ,[YearlyIncome]
      ,[EnglishOccupation]
      ,[AddressLine1]
      ,[Phone]
  FROM [TenCustomers]
  WHERE [LastName] IS NULL

OUTPUT

SQL IS NULL Function 2

This example will show you, How to write IS NULL in SQL Server. For this demonstration, We are going to use the [Emp] table present in our [SQL Tutorial] Database. Following screenshot will show you the data inside the Emp table, as you can see it has 15 records.

Following SQL Query will return all the employee records from [Emp] table, whose Office Phone numbers are NULL values

--Example for SQL Server IS NULL
USE [SQL Tutorial]
GO
SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [Emp]
  WHERE [Office Phone] IS NULL

OUTPUT

SQL IS NULL Example 3

Below SQL Query will return all the employee records, whose Office Phone numbers, and Mobile numbers are NULL values

-- SQL Server IS NULL example
SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [SQL Tutorial].[dbo].[Emp]
  WHERE [Office Phone] IS NULL AND 
	[Mobile] IS NULL

OUTPUT

Thank You for Visiting Our Blog