SQL NULL Handling: COALESCE, IFNULL, NULLIF, NOT IN Traps and COUNT(*)

NULL means "unknown", and it behaves differently from every other value: it isn't equal to anything, not even another NULL. This guide covers checking for NULL, the functions that replace or produce it, the numeric functions you'll use alongside them, and the two NULL traps that cause silently wrong query results.

NULL Values

-- Checking for NULL (NEVER use = NULL)
SELECT * FROM students WHERE phone IS NULL;      -- CORRECT
SELECT * FROM students WHERE phone = NULL;       -- WRONG! Returns nothing

-- COALESCE: return first non-NULL value
SELECT name, COALESCE(phone, 'No Phone') AS contact FROM students;

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(score, 0) FROM results;  -- returns NULL when score=0

-- IFNULL (MySQL) / NVL (Oracle)
SELECT IFNULL(commission, 0) AS commission FROM employees;

NULL Handling Functions

-- COALESCE: return first non-NULL value (most important!)
SELECT name, COALESCE(phone, mobile, 'No Contact') AS contact
FROM employees;

-- IFNULL (MySQL only)
SELECT name, IFNULL(commission, 0) AS commission FROM employees;

-- NVL (Oracle only)
SELECT name, NVL(commission, 0) AS commission FROM employees;

-- NULLIF: return NULL if two values are equal (prevents divide-by-zero!)
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM monthly_stats;
-- Without NULLIF, total_orders=0 would cause DIVISION BY ZERO ERROR

-- CASE WHEN for NULL handling
SELECT name,
       CASE WHEN commission IS NULL THEN 'No Commission'
            ELSE CAST(commission AS CHAR)
       END AS commission_info
FROM employees;

Numeric Functions

-- ROUND: round to N decimal places
SELECT ROUND(salary / 12, 2) AS monthly_pay FROM employees;
SELECT ROUND(3.456, 1);  -- 3.5

-- FLOOR and CEILING
SELECT FLOOR(4.9);   -- 4  (always rounds DOWN)
SELECT CEILING(4.1); -- 5  (always rounds UP)

-- ABS: absolute value
SELECT ABS(-500) AS amount;  -- 500

-- MOD: modulus (remainder)
SELECT MOD(10, 3);   -- 1
SELECT 10 % 3;       -- 1 (shorthand)

-- POWER and SQRT
SELECT POWER(2, 10);  -- 1024
SELECT SQRT(144);     -- 12

-- TRUNCATE: cut off decimals without rounding
SELECT TRUNCATE(3.999, 1);  -- 3.9  (NOT 4.0)

-- Useful calculation example: percentage
SELECT dept,
       COUNT(*) AS emp_count,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees), 2) AS pct
FROM employees GROUP BY dept;

The NOT IN with NULLs Trap

-- ❌ DANGEROUS: NOT IN returns ZERO ROWS if subquery has ANY NULL
SELECT name FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders  -- if ANY order has customer_id=NULL → ZERO rows returned!
);

-- Why? NOT IN (1, 2, NULL) = NOT (id=1 OR id=2 OR id=NULL)
-- id=NULL evaluates to UNKNOWN → entire expression = UNKNOWN → row excluded

-- ✅ SAFE: Use NOT EXISTS (handles NULLs correctly)
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- ✅ ALSO SAFE: Add NULL filter to subquery
SELECT name FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

COUNT(*) vs COUNT(column)

-- COUNT(*):      counts ALL rows including those with NULLs
-- COUNT(column): counts only rows where column is NOT NULL

-- ❌ WRONG ASSUMPTION: These are the same
SELECT COUNT(*) FROM employees;        -- Returns: 100
SELECT COUNT(phone) FROM employees;    -- Returns: 73  (27 have no phone)

-- Common interview trap:
SELECT COUNT(DISTINCT manager_id) FROM employees;
-- This SKIPS rows where manager_id IS NULL (top-level managers)
-- Use COUNT(DISTINCT manager_id) + (1 if NULLs exist) if you want to count them

-- ✅ Correct way to count including NULLs
SELECT COUNT(DISTINCT COALESCE(manager_id, -1)) FROM employees;
-- Or: COUNT(*) - (rows where condition is met)

FAQs

What is the difference between COALESCE and IFNULL?

IFNULL(a, b) takes two arguments and is MySQL-specific; COALESCE(a, b, c, …) takes any number and is standard SQL, returning the first non-NULL value.

Why does NOT IN return no rows?

If the subquery returns any NULL, x NOT IN (…) evaluates to UNKNOWN for every row. Use NOT EXISTS or filter NULLs out of the subquery.

How do you avoid division by zero in SQL?

Wrap the divisor in NULLIF(divisor, 0), which turns zero into NULL so the result is NULL instead of an error.