SQL - IS NULL

SQL - Handling NULL Values

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is not the same as an empty string (”) or the number zero (0). A NULL value represents missing or unknown information. Because of its special nature, you cannot use standard comparison operators like = or != to test for NULL.

Three-Valued Logic (3VL)

SQL uses a three-valued logic system: TRUE, FALSE, and UNKNOWN. Any comparison involving a NULL value (e.g., 5 = NULL or NULL = NULL) evaluates to UNKNOWN. The WHERE clause of a query only returns rows where the condition evaluates to TRUE. This is why WHERE column = NULL never returns any rows.

The IS NULL and IS NOT NULL Operators

To correctly check for NULL values, SQL provides the IS NULL and IS NOT NULL operators.

Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example: Finding Customers with Missing Information

Let’s find customers who have not provided their phone number in a Customers table.

-- Create a sample table with some NULLs
CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    PhoneNumber VARCHAR(20)
);

INSERT INTO Customers VALUES
(1, 'John Doe', 'john.d@example.com', '555-1234'),
(2, 'Jane Smith', 'jane.s@example.com', NULL),
(3, 'Peter Jones', 'peter.j@example.com', '555-5678'),
(4, 'Mary Johnson', 'mary.j@example.com', NULL);

-- Select customers with a NULL phone number
SELECT Name, Email
FROM Customers
WHERE PhoneNumber IS NULL;

Advanced NULL Handling Functions

Beyond simply checking for NULL, SQL provides powerful functions to handle NULL values within your queries, which is essential for calculations and display.

COALESCE: Providing a Default Value

The COALESCE function returns the first non-NULL value in a list of expressions. It’s perfect for substituting a default value when a column might be NULL.

-- Display 'Not Provided' if PhoneNumber is NULL
SELECT Name, COALESCE(PhoneNumber, 'Not Provided') AS ContactNumber
FROM Customers;

NULLIF: Comparing Two Expressions

The NULLIF(expr1, expr2) function returns NULL if the two expressions are equal; otherwise, it returns the first expression. This is useful for preventing errors like division by zero or for treating a specific value (like an empty string) as NULL.

-- Example: Avoid division by zero
-- If 'quantity' is 0, NULLIF makes it NULL, and the division returns NULL instead of an error.
SELECT total_price / NULLIF(quantity, 0) AS price_per_item
FROM sales_data;

NULLs and Aggregate Functions

Aggregate functions like SUM(), AVG(), MIN(), and MAX() ignore NULL values in their calculations. However, COUNT() behaves differently.

• COUNT(*): Counts all rows in the group, regardless of NULL values.
• COUNT(column_name): Counts only the rows where column_name is not NULL.

Example: Counting with NULLs

-- Using our Customers table from before:
SELECT
    COUNT(*) AS TotalCustomers,
    COUNT(PhoneNumber) AS CustomersWithPhone
FROM Customers;

This query would return 4 for TotalCustomers and 2 for CustomersWithPhone, demonstrating how COUNT(column) ignores NULLs.