This SQL tutorial focuses on the SQL Server IS NULL logical operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:


 

SQL Server IS NULL / IS NOT NULL

In SQL Server,  NULL value indicates an unavailable or unassigned value. The value NULL does not equal zero (0), nor does it equal a space (‘ ‘). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on this value by using operators such as ‘=’ or ‘<>’.

The following SQL Server queries will produce no results:

The employees who do not get a commission:

SELECT *
FROM employees
WHERE  commission_pct = NULL  
-- (no rows will be retrieved)

The employees who get a commission:

SELECT *
FROM employees
WHERE  commission_pct <> NULL 
-- (no rows will be retrieved)

In SQL Server, to handle comparison with NULL values, you need to use the following SQL Server operators:

  • IS NULL – equals the operation ‘= NULL’ (records with NULL values)
  • IS NOT NULL – equals the operation ‘<> NULL’ (records without NULL values)

The employees who do not get a commission:

SELECT *
FROM employees
WHERE  commission_pct IS NULL

The employees who get a commission:

SELECT *
FROM employees
WHERE  commission_pct IS NOT NULL