SQL Server IS NULL Operator

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:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

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