SQL NULL Values

Chapter 9 of 40 · Estimated read time: 8 min

What is NULL?

NULL is not zero. NULL is not an empty string. NULL means the value is unknown or missing. A customer whose phone number has not been collected has NULL in the phone column — not 0, not a blank space.

IS NULL and IS NOT NULL

Suppose some customers haven't provided their email:

-- Find customers who have NOT provided their email
SELECT customer_id, customer_name, city
FROM customers
WHERE email IS NULL;

-- Find customers who HAVE provided their email
SELECT customer_id, customer_name, email
FROM customers
WHERE email IS NOT NULL;

The Most Common NULL Trap

-- ❌ WRONG — returns 0 rows even when NULLs exist
SELECT * FROM customers WHERE email = NULL;
SELECT * FROM customers WHERE email != NULL;

-- ✅ CORRECT — always use IS NULL / IS NOT NULL
SELECT * FROM customers WHERE email IS NULL;
SELECT * FROM customers WHERE email IS NOT NULL;

NULL in Calculations and Expressions

Any arithmetic involving NULL produces NULL. Think of it as "unknown + anything = still unknown":

-- If discount column has NULL, the discounted_price is also NULL
SELECT
  product_name,
  price,
  discount,
  price - discount AS discounted_price   -- NULL if discount is NULL
FROM products;

-- Fix: treat NULL discount as 0 using COALESCE
SELECT
  product_name,
  price,
  price - COALESCE(discount, 0) AS discounted_price
FROM products;

NULL in COUNT — A Surprising Behaviour

-- COUNT(*) counts ALL rows including those with NULLs
SELECT COUNT(*) AS total_customers FROM customers;
-- Result: 5

-- COUNT(column) counts only NON-NULL values in that column
SELECT COUNT(email) AS customers_with_email FROM customers;
-- Result: 4  (Arjun's NULL email is skipped)

-- This is how you count missing values:
SELECT COUNT(*) - COUNT(email) AS customers_missing_email FROM customers;
-- Result: 1

NULL in ORDER BY and GROUP BY

-- NULL sorting behaviour varies by database:
-- PostgreSQL: NULLs go LAST in ASC, FIRST in DESC (by default)
-- MySQL:      NULLs go FIRST in ASC
-- Control it explicitly:

SELECT customer_name, email
FROM customers
ORDER BY email ASC NULLS LAST;   -- PostgreSQL: push NULLs to the bottom

-- GROUP BY treats all NULLs as one group
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city;   -- A NULL city would appear as its own group

-- 1.
SELECT * FROM products WHERE supplier_id IS NULL;

-- 2.
SELECT COUNT(*) - COUNT(total_amount) AS null_totals FROM orders;

-- 3.
SELECT
  customer_name,
  COALESCE(email, 'Not Provided') AS email
FROM customers;