Topic 7018 min readStructured Tutorial

SQL WHERE Clause: Using IS NULL

Quick Answer

The SQL WHERE clause with IS NULL is used to filter records where a column contains NULL values. It helps identify missing or undefined data in a table, which cannot be checked using standard equality operators.

Learning Objectives

Understand what NULL means in SQL and why it requires special handling.
Learn how to use the WHERE clause with IS NULL to filter records.
Practice writing SQL queries that correctly identify NULL values.

Introduction

In SQL, NULL represents missing or undefined data in a table column.

Since NULL is not a value but a marker for absence of data, it requires special handling in queries.

The WHERE clause with IS NULL allows you to filter rows where a column contains NULL values.

"NULL is not a value, it is the absence of a value."

Understanding NULL in SQL

NULL indicates that a data value does not exist in the database. It is different from zero or empty string.

Because NULL is not a value, you cannot use standard comparison operators like '=' or '<>' to test for NULL.

Special operators like IS NULL and IS NOT NULL are used to check for NULL values.

NULL means unknown or missing data.
NULL is not equal to anything, even another NULL.
Use IS NULL to find NULL values.
Use IS NOT NULL to find non-NULL values.

Using WHERE Clause with IS NULL

The WHERE clause filters rows based on specified conditions.

To find rows where a column is NULL, use the syntax: WHERE column_name IS NULL.

This condition returns all rows where the column has no value.

Syntax: SELECT * FROM table WHERE column IS NULL;
IS NULL checks for NULL values explicitly.
Cannot use '=' or '<>' to check for NULL.

Example Query

Suppose you have a table named Employees with a column called ManagerID that can be NULL if the employee has no manager.

To find employees without a manager, you write:

SELECT * FROM Employees WHERE ManagerID IS NULL;

Common Use Cases for IS NULL

Filtering NULL values is useful in many scenarios such as data cleaning, reporting, and conditional logic.

You might want to find missing data to update it or exclude NULLs from calculations.

Identify records missing important information.
Exclude NULLs from aggregates or calculations.
Join tables where foreign keys might be NULL.
Implement conditional logic based on presence or absence of data.

Practical Example

This query returns all customers who do not have an email address recorded.

This query finds orders that have not yet been shipped because the ShipDate is NULL.

Examples

Find Customers Without Email

SELECT CustomerID, CustomerName FROM Customers WHERE Email IS NULL;

This query returns all customers who do not have an email address recorded.

Filter Orders with Missing Ship Date

SELECT OrderID, OrderDate FROM Orders WHERE ShipDate IS NULL;

This query finds orders that have not yet been shipped because the ShipDate is NULL.

Best Practices

Always use IS NULL or IS NOT NULL to check for NULL values.
Avoid using '=' or '<>' operators with NULL as they will not work as expected.
Use NULL checks to improve data quality and handle missing data explicitly.
Combine IS NULL with other conditions using AND/OR for complex filters.

Common Mistakes

Using '=' or '<>' to compare with NULL values.
Assuming NULL means zero or empty string.
Ignoring NULL values in aggregate functions without proper handling.
Not considering NULLs in JOIN conditions leading to unexpected results.

Hands-on Exercise

Find Records with NULL Values

Write a query to find all employees in the Employees table where the 'PhoneNumber' column is NULL.

Expected output: A list of employees missing phone numbers.

Hint: Use the WHERE clause with IS NULL.

Filter Non-NULL Records

Write a query to select all products from the Products table where the 'DiscontinuedDate' is NOT NULL.

Expected output: Products that have a discontinued date.

Hint: Use IS NOT NULL in the WHERE clause.

Interview Questions

Why can't you use '=' to check for NULL in SQL?

Interview

Because NULL represents unknown or missing data, it is not equal to any value, including itself. Therefore, '=' does not work for NULL comparisons; IS NULL must be used instead.

What is the difference between IS NULL and IS NOT NULL?

Interview

IS NULL checks if a column contains NULL values, while IS NOT NULL checks for columns that have any non-NULL value.

How does SQL treat NULL in logical expressions?

Interview

In SQL, NULL propagates through logical expressions resulting in UNKNOWN rather than TRUE or FALSE, which affects filtering and requires special handling.