SQL COALESCE and NULL Handling Techniques

🔹 Why NULL Handling Matters

NULL represents “unknown” data. It propagates through calculations and comparisons—10 + NULL becomes NULL and NULL = NULL is UNKNOWN. You need defensive patterns to turn NULLs into usable values.

🧠 COALESCE() – First Non-NULL Value

SELECT COALESCE(NULL, NULL, 'Hello', 'World');
-- Output: Hello

COALESCE stops as soon as a non-NULL value is found, making it perfect for fallback logic.

🧩 Example 1 – Display Names with Fallbacks

SELECT
  emp_id,
  COALESCE(first_name, nickname, 'Unknown') AS display_name
FROM employees;

Ensures every row has a friendly display value even if some columns are missing.

🧩 Example 2 – Safe Calculations

SELECT
  product_id,
  COALESCE(price, 0) * quantity AS total_value
FROM orders;

🧩 Example 3 – Clean Reporting Output

SELECT
  customer_name,
  COALESCE(phone, 'N/A') AS phone,
  COALESCE(email, 'No Email Provided') AS email
FROM customers;

Prevents blank cells from appearing in dashboards.

⚙️ NULLIF() to Prevent Divide-by-Zero

SELECT total / NULLIF(count, 0)
FROM sales;

NULLIF(a, b) returns NULL if the arguments are equal—ideal for protecting arithmetic operations.

⚙️ IS NULL / IS NOT NULL Predicates

SELECT * FROM employees WHERE nickname IS NULL;
SELECT * FROM employees WHERE nickname IS NOT NULL;

Never use = NULL; comparisons with NULL always yield UNKNOWN.

⚙️ CASE Expression Alternative

SELECT
  CASE
    WHEN salary IS NULL THEN 0
    ELSE salary
  END AS adjusted_salary
FROM employees;

📘 Function Comparison

💡 Interview Soundbite