SQL Server - IS NULL Operator

A field with no value is called a field with NULL value. To test the NULL value of a field, SQL Server (Transact-SQL) IS NULL operator is used. It returns TRUE if a NULL value is found, otherwise it returns FALSE.

Syntax

The syntax for using IS NULL operator in SQL Server (Transact-SQL) is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name IS NULL;

Example:

Consider a database containing a table called Employee with the following records:

• To select all records of the Employee table where Age is null, the query is given below.

  SELECT * FROM Employee
  WHERE Age IS NULL;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  3	Jo	Paris		2800
  4	Kim	Amsterdam		3100

• To fetch all records of the Employee table where Age is not null, the query is mentioned below:

  SELECT * FROM Employee
  WHERE Age IS NOT NULL;
  

  
  

  This will produce the following result:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  5	Ramesh	New Delhi	28	3000
  6	Huang	Beijing	28	2800

• To delete all records of the Employee table where Age is null, the following query can be used:

  DELETE FROM Employee
  WHERE Age IS NULL;
  
  -- see the result
  SELECT * FROM Employee;
  

  
  

  This will produce the following result:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  5	Ramesh	New Delhi	28	3000
  6	Huang	Beijing	28	2800