SQL

Managing NULL Values with COALESCE and NULLIF Functions

Replace NULLs with meaningful default values using COALESCE or convert specific values to NULL with NULLIF, improving data consistency and readability.

SELECT
    product_id,
    product_name,
    COALESCE(description, 'No description available') AS description_display,
    COALESCE(image_url, '/assets/default_image.png') AS image_url_display,
    NULLIF(stock_quantity, 0) AS available_stock_or_null
FROM
    products;

How it works: The `COALESCE` function returns the first non-NULL expression in its argument list, providing a way to display default values instead of NULLs for fields like `description` or `image_url`. Conversely, `NULLIF` returns NULL if its two arguments are equal, otherwise it returns the first argument. This is useful for treating specific values (e.g., a `stock_quantity` of 0) as equivalent to NULL for logical processing or display.

Managing NULL Values with COALESCE and NULLIF Functions

Need help integrating this into your project?