SQL NULL Handling: Three-Valued Logic & Fallback Functions

Handling missing information correctly is one of the most critical aspects of database engineering. In SQL, NULL is a special marker indicating that a data value does not exist or is unknown. Because NULL represents unknown data, it obeys unique mathematical rules called Three-Valued Logic (3VL).

The Core Rule: NULL is Never Equal to NULL

In standard mathematics, (x = x) is always true. In SQL, because NULL means "unknown", one unknown value can never be asserted equal to another unknown value:

-- Evaluates to UNKNOWN (neither TRUE nor FALSE):
SELECT NULL = NULL;   -- Result: NULL

-- INCORRECT: Will NEVER return any rows!
SELECT * FROM students WHERE phone_number = NULL;

-- CORRECT: Use IS NULL or IS NOT NULL
SELECT * FROM students WHERE phone_number IS NULL;
SELECT * FROM students WHERE phone_number IS NOT NULL;

Handling Fallbacks with COALESCE & IFNULL

When presenting data in user interfaces, you often want to show a friendly default message (like 'Not Provided' or 0) instead of an ugly NULL:

-- 1. ANSI Standard COALESCE: Returns first non-NULL value
SELECT 
    student_id, 
    first_name,
    COALESCE(phone_number, emergency_contact, 'No Phone Available') AS contact_info
FROM students;

-- 2. MySQL IFNULL: Returns fallback if expression is NULL
SELECT 
    student_id,
    first_name,
    IFNULL(gpa, 0.00) AS safe_gpa
FROM students;

How NULL Affects Aggregate Functions (COUNT, SUM, AVG)

• COUNT(*): Counts all rows in the table, including rows with NULL attributes.

• COUNT(column_name): Ignores and skips NULL values, counting only populated entries.

• SUM, AVG, MIN, MAX: Automatically ignore NULL values during aggregation.

Next, let us learn how to manipulate text data using SQL String Functions!