How do you handle NULL values in SQL? Mention functions like COALESCE and NULLIF.

Handling NULL values in SQL is critical for data integrity and accurate query results, involving explicit filtering, substitution, and conditional assignment due to their unique behavior in comparisons and aggregations.

Core Functions and Behavior

The primary methods include IS NULL and IS NOT NULL for filtering rows. For value substitution, COALESCE(expr1, expr2, ...) returns the first non-NULL expression in its list, providing default values (e.g., COALESCE(email, 'unknown@example.com')). NULLIF(expr1, expr2) returns NULL if expr1 equals expr2, otherwise expr1. This is invaluable for preventing errors like divide-by-zero (NULLIF(divisor, 0)).

NULLs propagate in expressions (NULL + 5 is NULL). Aggregate functions like SUM(), AVG(), MAX(), MIN() ignore NULLs, while COUNT(*) counts all rows (including those with NULLs) and COUNT(column) counts only non-NULL values. Crucially, NULL does not equal NULL, meaning JOIN conditions like ON a.id = b.id will not match rows where a.id or b.id is NULL, potentially leading to silent data loss.

Performance and Data Quality Considerations

While COALESCE in a SELECT clause is generally efficient, using functions like COALESCE or NULLIF in WHERE or JOIN conditions can prevent the database optimizer from utilizing indexes, leading to full table scans and degraded performance on large datasets (e.g., in data warehouses like Snowflake or Spark).

SELECT
    product_id,
    COALESCE(product_name, 'Unnamed Product') AS product_name,
    NULLIF(price, 0) AS actual_price -- Treat 0 price as NULL
FROM products;

Explicitly handling NULLs avoids incorrect aggregations, unexpected data loss in joins, and ensures the reliability of downstream data models (e.g., dbt models) and analytics. This prevents silent data quality issues from propagating throughout the data ecosystem.

In the interview, also mention that a robust NULL handling strategy is fundamental for data quality, preventing silent data loss, and ensuring reliable analytics, especially in complex ETL pipelines.