1.3 Nulls, Case, and Conditional Logic

• NULL means unknown, not zero or empty.
• Any arithmetic or comparison involving NULL results in… you guessed it… NULL.

Example:

That’s because SQL follows three-valued logic:

• TRUE
• FALSE
• UNKNOWN (NULL)

Hence, filters like WHERE column = 'X' exclude NULL rows — because NULL comparisons are neither true nor false.

To explicitly test for NULL: Use IS NULL or IS NOT NULL.

WHERE discount IS NULL

The CASE expression adds if-else reasoning to SQL — allowing custom logic during queries.

Syntax:

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

It evaluates conditions top-down, returning the first true match.

Example:

SELECT 
  customer_id,
  CASE 
    WHEN total_spent >= 10000 THEN 'VIP'
    WHEN total_spent >= 5000 THEN 'Loyal'
    ELSE 'New'
  END AS customer_segment
FROM customers;

When data is missing, you can replace or flag it using conditional logic:

SELECT 
  order_id,
  CASE 
    WHEN discount IS NULL THEN 'No Discount'
    ELSE 'Discount Applied'
  END AS discount_flag
FROM orders;

Or simply use COALESCE() — a shortcut for “pick the first non-null value”:

SELECT COALESCE(discount, 0) AS safe_discount FROM orders;

SELECT *
FROM orders
WHERE discount IS NULL;

Explanation: You must use IS NULL, because discount = NULL will never match anything. That’s the golden rule: NULL is not equal to anything — not even another NULL.

SELECT 
  order_id,
  CASE 
    WHEN discount IS NULL THEN 'No Discount'
    WHEN discount = 0 THEN 'Zero Discount'
    ELSE 'Discount Applied'
  END AS discount_status
FROM orders;

Explanation: Notice how we distinguish between missing, zero, and non-zero values — something most analysts miss.

SELECT 
  order_id,
  amount,
  COALESCE(discount, 0) AS adjusted_discount,
  amount - COALESCE(discount, 0) AS net_amount
FROM orders;

Explanation: COALESCE() replaces missing values on the fly. It’s your best friend when computing numeric or string results without losing rows to NULL.

SELECT 
  COUNT(*) AS total_orders,
  COUNT(discount) AS non_null_discounts
FROM orders;

Explanation:

• COUNT(*) counts all rows.
• COUNT(discount) counts only rows where discount IS NOT NULL.

That’s an easy way to compute data completeness in interviews or analytics audits.

• “What is the difference between NULL, 0, and an empty string?”
• “How does COUNT(column) treat NULL?”
• “What does COALESCE() do?”
• “Write a query that replaces NULLs with default values.”

Interviewers look for conceptual clarity — especially around the three-valued logic and impact on aggregations.

✅ Great answer:

“NULL means unknown, so any comparison with it becomes unknown too. That’s why we must use IS NULL. COUNT(column) skips nulls, while COUNT(*) doesn’t.”

Find customers who have never used a discount, including those with missing values.

SELECT customer_id
FROM orders
WHERE COALESCE(discount, 0) = 0;

Can you explain why COALESCE is necessary here? Without it, customers with NULL discounts would be excluded!

• ❌ Writing WHERE column = NULL → Always use IS NULL.
• ❌ Assuming COUNT(column) counts all rows. → It ignores nulls!
• ❌ Forgetting to handle NULL in arithmetic. → 100 + NULL = NULL.
• ❌ Using COALESCE in indexed filters. → Breaks index optimization.

💡 Debug Tip: Run small test queries to inspect NULL behavior — they reveal surprises faster than debugging production queries.