SQL IS NULL Function

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

SELECT Column_Names 
FROM Table
WHERE Expression IS NULL

SQL Server IS NULL Example

For this SQL IS NULL function 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 an empty value.

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

SQL IS NULL Function for Non Empty Last Name records 2

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 IS NULL 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