SQL IS NULL Function

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

-- -- SQL Server IS NULL Syntax
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

In this example, we use IS NULL to return all the records whose Last Name is a 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

IS NULL Function Example 2

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

The following IS NULL query returns all the employee records whose Office Phone numbers are NULL 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 NULLs

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