SQL NULL Handling Cheat Sheet

NULL means unknown, not zero or empty

Comparisons with NULL do not evaluate to true. Use IS NULL and IS NOT NULL rather than = NULL or != NULL.

SELECT employee_name
FROM employees
WHERE manager_id IS NULL;

COALESCE: choose the first non-NULL value

COALESCE is useful for display values and calculations where the business meaning of a missing value is known.

• Do not replace NULL with zero unless zero is truly correct
• Keep returned values type-compatible

SELECT
  customer_name,
  COALESCE(phone, email, 'No contact') AS preferred_contact
FROM customers;

COUNT behaves differently with NULL

COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL. This difference is especially important after a LEFT JOIN.

SELECT
  c.customer_id,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Watch NOT IN

If a NOT IN subquery returns even one NULL, the comparison can become unknown and return no rows. NOT EXISTS is usually safer for anti-join logic.

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);