SQL IS NULL Function

The SQL IS NULL test whether the user-specified expression is empty or not, and if it is, TRUE will return. Otherwise, it returns FALSE. The basic syntax of this IS NULL is

SELECT Column_Names 
FROM Table
WHERE Expression IS NULL

Simple SQL IS NULL Example

For this SQL Server IS NULL demonstration, We are going to use the [TenCustomers] table, and the data inside the table is

Tabel with last name and Adress as Empty values 1

In this example, we use this function to return all the records whose Last Name is a empty value

SELECT [CustomerKey]
      ,[FirstName]
      ,[LastName]
      ,[EmailAddress]
      ,[YearlyIncome]
      ,[EnglishOccupation]
      ,[AddressLine1]
      ,[Phone]
  FROM [TenCustomers]
  WHERE [LastName] IS NULL

Example 2

The below image shows the data inside the SQL Server Emp table, and it has 15 records.

Employee Table with Few Empty Phone Numbers 5

The following query returns all the employee records whose Office Phone numbers are empty values

SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [Emp]
  WHERE [Office Phone] IS NULL

It returns the employees whose Office Phone, and Mobile numbers are empty.

SELECT [Id]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Office Phone]
      ,[Mobile]
      ,[Home Phone]
  FROM [Emp]
  WHERE [Office Phone] IS NULL AND 
	[Mobile] IS NULL