NULL HANDLING

This lesson on NULL HANDLING is hands-on and example-driven. You will master the SQL functions necessary to identify, replace, and conditionally manage missing data points. This mastery ensures your queries handle unknown values gracefully, preventing calculation errors and providing reliable default data.

What You'll Be Able To Do

• Distinguish between NULL, zero, and empty string values in database contexts.
• Write queries using COALESCE to prioritize data from multiple columns.
• Apply NULLIF to convert specific placeholder values into true NULLs.
• Use ISNULL to identify records containing missing data.
• Substitute NULL values with a specified default using IFNULL or COALESCE.

Detailed Concept Walkthrough

1. Understanding NULL Values

NULL is a marker indicating missing, unknown, or inapplicable data, fundamentally different from zero (a numerical value) or an empty string (a length-zero string). It signifies the absence of a value in the domain of the column.

• Mechanism: NULL is stored internally as a special bit or flag associated with the column value, not as data itself, which is why standard comparison operators like = or != fail when checking for NULL.
• Execution Flow: When SQL encounters NULL in arithmetic operations, the result is usually NULL (e.g., 5 + NULL = NULL), propagating the unknown state throughout the calculation.
• Best Practice: Always use the dedicated predicates IS NULL or IS NOT NULL in WHERE clauses to correctly filter records where the value is missing or present.

-- Demonstrating that NULL is not equal to anything, even itself
SELECT 
    CASE 
        WHEN NULL = NULL THEN 'Equal' 
        ELSE 'Not Equal' 
    END AS Comparison_Result_1, -- Returns 'Not Equal' or NULL depending on dialect
    CASE 
        WHEN salary IS NULL THEN 'Missing' 
        ELSE 'Present' 
    END AS Comparison_Result_2; -- Correctly identifies NULL

Key Takeaway: Treat NULL as "unknown" and use IS NULL for checks, never standard equality operators.

2. COALESCE for Prioritized Data Selection

COALESCE is a standard SQL function that evaluates a list of expressions sequentially and returns the first one that is not NULL, acting as a powerful multi-column fallback mechanism. It is highly portable across different SQL dialects.

• Mechanism: The function accepts two or more arguments and performs a short-circuit evaluation, meaning it stops processing the list as soon as it finds a non-null value and returns it immediately, improving performance.
• Under the Hood: All expressions passed to COALESCE must be implicitly convertible to the same data type, which becomes the data type of the final returned result set column.
• Execution Flow: It is often used to provide a default value if a primary column is missing, then a secondary column if the primary is missing, and finally a hardcoded constant if both are missing.
• Syntax Rule: Unlike some vendor-specific functions, COALESCE can take an unlimited number of arguments, making it ideal for complex data prioritization logic involving many columns.

-- Prioritizes the most specific contact method
SELECT 
    ID,
    COALESCE(
        phone_mobile, -- Try mobile first
        phone_work,   -- Fallback to work phone
        'No Contact'  -- Final default if all are NULL
    ) AS Preferred_Contact
FROM employee;

Key Takeaway: Use COALESCE when you need to select the first available non-null value from a sequence of columns or expressions.

3. NULLIF for Conditional Nullification

NULLIF is used to conditionally replace a value with NULL if that value matches a specified comparison value, effectively masking data points that are equivalent to a known placeholder or error state.

• Mechanism: It takes two arguments, NULLIF(expression1, expression2). If expression1 equals expression2, the function returns NULL; otherwise, it returns expression1.
• Best Practice: This function is particularly useful for cleaning data where placeholder values (like 'N/A', 0, or -1) are used to represent missing data, allowing aggregate functions to ignore them correctly.
• Under the Hood: The comparison performed internally is a standard equality check (=), meaning both expressions must be of compatible data types for the comparison to execute successfully.
• Execution Flow: By converting specific values (like zero) into NULL, you can prevent errors such as division by zero, as the entire expression involving the NULL result will also evaluate to NULL.

-- Replace old IDs with NULL if they match the new ID (meaning no change occurred)
SELECT 
    ID, 
    new_ID,
    NULLIF(ID, new_ID) AS Changed_ID_Only,
    -- Useful for preventing division by zero errors if 'score' is 0
    100 / NULLIF(score, 0) AS Safe_Calculation 
FROM employee;

Key Takeaway: NULLIF is the tool for turning specific, unwanted values into NULLs for cleaner aggregation or error prevention.

4. ISNULL and IFNULL Replacement

While COALESCE is standard, many SQL dialects offer simpler, two-argument functions like IFNULL (MySQL/SQLite) or ISNULL (T-SQL replacement form) specifically designed to replace a single NULL value with a default alternative.

• Mechanism (IFNULL): IFNULL(expression, replacement) checks if the expression is NULL; if so, it returns the replacement value; otherwise, it returns the expression itself, acting as a concise two-argument COALESCE.
• Mechanism (ISNULL Boolean): In some dialects, ISNULL(expression) returns a boolean (1 or 0) indicating if the expression is NULL, which is useful for filtering or counting missing records.
• Aggregate Use: When using the replacement form of ISNULL in an aggregate context, it allows you to substitute a value for NULLs before the aggregation occurs, ensuring that missing data points contribute a defined value.
• Best Practice: Use COALESCE for maximum portability across databases, but use the vendor-specific functions (IFNULL, ISNULL replacement form) when optimizing for performance or brevity within a specific environment.

-- Example 1: IFNULL (Common in MySQL/SQLite)
SELECT IFNULL(salary, 999) AS Guaranteed_Salary FROM employee;

-- Example 2: ISNULL replacement in aggregate (T-SQL/SQL Server)
SELECT SUM(ISNULL(salary, 10000)) 
FROM employee; -- NULL salaries are treated as 10000 in the sum

Key Takeaway: IFNULL and the two-argument form of ISNULL are concise, vendor-specific ways to provide a single fallback value for a NULL column.

Topics Covered in NULL HANDLING

1. Concept: NULL Values (0:40 - 1:50) — NULL represents unknown or missing data and is distinct from zero or an empty string.
2. ISNULL Boolean Check (2:53 - 4:00) — The ISNULL function returns a boolean value indicating whether an expression is missing.
3. ISNULL in Aggregates (5:27 - 6:53) — ISNULL can substitute a value for NULLs before an aggregate function like SUM runs.
4. COALESCE Function (6:58 - 9:45) — COALESCE returns the first non-null expression found in a list of arguments.
5. NULLIF Function (9:49 - 12:37) — NULLIF returns NULL if its two arguments are equal, otherwise it returns the first argument.
6. IFNULL Function (12:40 - 14:27) — IFNULL provides a simple, two-argument way to replace a NULL value with a default.

SQL Cheat Sheet

• NULL — Represents unknown, missing, or inapplicable data

• ISNULL(expr) — Returns 1 (True) if the expression is NULL, 0 otherwise

  SELECT ISNULL(salary) FROM employee;
  

• COALESCE(e1, e2, ...) — Returns the first non-null expression in the list

  SELECT COALESCE(salary, ID) AS result FROM employee;
  

• NULLIF(e1, e2) — Returns NULL if e1 equals e2, otherwise returns e1

  SELECT NULLIF(ID, new_ID) AS result FROM employee;
  

• IFNULL(expr, replacement) — Replaces a NULL value with the specified replacement

  SELECT IFNULL(salary, 999) AS result FROM employee;
  

• SUM(ISNULL(salary, 10000)) — Replaces NULLs with a value before calculating the aggregate

  SELECT SUM(ISNULL(salary, 10000)) FROM employee;
  

Comparison Table

Common Pitfalls

• Mistake: Using WHERE salary = NULL to find missing records. Avoid: Use the dedicated predicate WHERE salary IS NULL.
• Mistake: Assuming NULL is treated as zero in aggregates. Avoid: NULLs are ignored by SUM(); use COALESCE or ISNULL first.
• Mistake: Using NULLIF when you want to replace NULLs. Avoid: NULLIF creates NULLs; use COALESCE or IFNULL to replace them.
• Mistake: Forgetting that ISNULL can return a boolean. Avoid: Check your dialect; some use ISNULL for identification (0/1).

FAQs

• Why does NULL = NULL return false? Because NULL represents an unknown value, comparing two unknown values results in an unknown outcome, which SQL treats as false in standard comparisons.
• Should I use COALESCE or IFNULL? Use COALESCE for maximum SQL standard compliance and portability, or when you need more than one fallback option. Use IFNULL for brevity in MySQL/SQLite.
• How does NULLIF help with division? You can use 100 / NULLIF(divisor, 0) to ensure that if the divisor is zero, the result is NULL instead of causing a runtime division-by-zero error.