NULL Functions

Overview

NULL handling functions provide fallbacks, replacement values, and comparisons to prevent missing data from breaking calculations.

Learning Objectives

• Replace missing null attributes with default values using COALESCE().
• Compare null replacements and avoid division-by-zero errors with NULLIF().
• Distinguish database-specific null helpers (IFNULL, ISNULL).

Detailed Concept Explanation

• COALESCE(val1, val2, ...): Evaluates parameters in order and returns the first non-null value. It is the ANSI standard and universally supported.
• NULLIF(val1, val2): Returns NULL if val1 equals val2. Highly useful for avoiding "division by zero" errors by replacing zeros with NULLs.
• IFNULL(col, default) (or ISNULL): A shorthand function to swap out NULLs with defaults (PostgreSQL uses COALESCE, SQL Server uses ISNULL, MySQL uses IFNULL).

Code Examples

SQL

-- COALESCE fallback chain
SELECT name, 
  COALESCE(phone, mobile, email, 'No Contact Info') AS primary_contact
FROM customers;

-- Prevent division by zero errors using NULLIF
SELECT 
  product_id, 
  total_revenue / NULLIF(units_sold, 0) AS average_selling_price
FROM sales;

Best Practices

• Clean Dimensions: Use COALESCE to transform missing values into standard category labels (e.g. 'N/A' or 'Unknown') for clean dashboard reporting.
• Division Guard: Always wrap divisor columns in NULLIF(column, 0) if there is any chance they contain zero.

Interview Perspective

Interactive Challenges

Summary

NULL functions protect queries from arithmetic crashes and substitute missing elements with standard default values.