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