SQL IS NULL Keyword

Purpose

The IS NULL operator in SQL is used to check for NULL values in a column. A NULL value represents the absence of a value, not an empty string or zero. It's crucial to distinguish NULL from other types of empty or missing data.

Usage

IS NULL is typically used within a WHERE clause to filter results. It only returns rows where the specified column contains a NULL value.

Example: Finding Customers with Missing Addresses

This example selects customer names, contact names, and addresses from the 'Customers' table, but only returns rows where the 'Address' column is NULL (meaning the address is missing).

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
            

-- Output will vary depending on your 'Customers' table. Example:
-- CustomerName | ContactName | Address
-- ------------- | ------------- | --------
--  (If any customers have NULL in the Address column, their data will appear here.)

            

Important Note

A NULL value is fundamentally different from a zero (0), an empty string (""), or a field containing only spaces. A NULL value indicates that the data is truly missing or undefined.

Best Practice

Always use IS NULL (and not = NULL) when checking for NULL values. = NULL will not work as expected because NULL represents the absence of a value, and thus, it cannot be compared using standard equality operators.