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

SQL IS NULL Example 1

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
SQL IS NULL Example 2

IS NULL Function Example 2

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

SQL IS NULL Example 5

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
SQL IS NULL Example 3

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
SQL IS NULL Example 4