SQL IS NULL and IS NOT NULL

NULL is one of the most misunderstood concepts in SQL. It doesn’t mean zero, it doesn’t mean empty string — it means “unknown value.” Understanding how NULL behaves is essential for writing correct queries.

What is NULL?

NULL represents the absence of a value — information that’s unknown, missing, or not applicable:

A customer with no phone number: phone = NULL
An order that hasn’t shipped: shipped_at = NULL
A product with no description: description = NULL

NULL is not a value you can compare with =. Comparing NULL to anything — even another NULL — returns NULL (unknown), not TRUE:

NULL = NULL    -- NULL (not TRUE!)
NULL != NULL   -- NULL (not FALSE!)
NULL = 5       -- NULL
NULL > 0       -- NULL
1 = NULL       -- NULL

Because of this, WHERE column = NULL never returns any rows.

IS NULL

Use IS NULL to find rows where a column has no value:

-- Find customers without a phone number
SELECT name, email
FROM customers
WHERE phone IS NULL;

-- Find orders that haven't been shipped
SELECT order_id, customer_id, amount
FROM orders
WHERE shipped_at IS NULL AND status != 'cancelled';

-- Find products with no category assigned
SELECT product_id, name
FROM products
WHERE category_id IS NULL;

IS NOT NULL

Use IS NOT NULL to find rows where a column has a value:

-- Find customers who have provided a phone number
SELECT name, phone FROM customers WHERE phone IS NOT NULL;

-- Find orders that have been shipped
SELECT order_id, shipped_at FROM orders WHERE shipped_at IS NOT NULL;

-- Require all contact fields to be present
SELECT name FROM customers
WHERE email IS NOT NULL
  AND phone IS NOT NULL
  AND address IS NOT NULL;

Common Mistake: Using = NULL

This is a very common error that silently returns no rows:

-- WRONG: always returns 0 rows
SELECT * FROM customers WHERE phone = NULL;
SELECT * FROM orders WHERE shipped_at = NULL;

-- CORRECT
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM orders WHERE shipped_at IS NULL;

NULL in Aggregate Functions

All standard aggregate functions ignore NULL values:

SELECT
    COUNT(*) AS total_rows,           -- counts all rows including NULLs
    COUNT(phone) AS with_phone,        -- only counts rows where phone is NOT NULL
    COUNT(DISTINCT city) AS unique_cities,  -- unique non-null cities
    SUM(amount) AS total_amount,       -- ignores NULL amounts
    AVG(amount) AS avg_amount          -- ignores NULL amounts (denominator is non-null count)
FROM customers;

AVG divides by the count of non-null values, not total rows. If 2 out of 10 amounts are NULL, AVG(amount) divides by 8, not 10.

NULL in JOIN Conditions

NULL values in join columns never match — they’re excluded from INNER JOINs:

-- If product_id is NULL in order_items, that row won't match any product
SELECT p.name, oi.quantity
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id;
-- Rows where oi.product_id IS NULL are excluded

Use LEFT JOIN to keep rows even when the join column is NULL:

SELECT oi.order_id, p.name, oi.quantity
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id;
-- Rows where oi.product_id is NULL appear with p.name = NULL

COALESCE: Replace NULL with a Default

COALESCE returns the first non-NULL argument:

-- Display "No phone" when phone is NULL
SELECT name, COALESCE(phone, 'No phone on file') AS phone_display
FROM customers;

-- Use 0 when amount is NULL
SELECT order_id, COALESCE(discount_amount, 0) AS discount
FROM orders;

-- Try multiple fallbacks
SELECT
    COALESCE(mobile_phone, home_phone, work_phone, 'No number') AS best_phone
FROM contacts;

NULLIF: Convert a Value to NULL

NULLIF(a, b) returns NULL if a = b, otherwise returns a. Useful to avoid division by zero:

-- Avoid division by zero
SELECT revenue / NULLIF(visits, 0) AS revenue_per_visit
FROM campaign_stats;
-- When visits = 0, returns NULL instead of error

-- Treat empty strings as NULL
SELECT NULLIF(TRIM(notes), '') AS notes_cleaned
FROM records;

NULL in ORDER BY

NULLs sort differently by default in different databases:

PostgreSQL: NULLs last in ASC, first in DESC
MySQL: NULLs first in both ASC and DESC
SQL Server: NULLs last in ASC, first in DESC

Control NULL sort order explicitly (PostgreSQL):

ORDER BY last_login DESC NULLS LAST    -- put NULLs at the end
ORDER BY last_login ASC NULLS FIRST    -- put NULLs at the start

NULL vs Empty String

NULL and empty string ('') are different in SQL:

-- NULL: no value was ever provided
WHERE notes IS NULL

-- Empty string: value was provided, but it's blank
WHERE notes = ''

-- Check for either (no useful content)
WHERE notes IS NULL OR notes = ''
WHERE COALESCE(NULLIF(TRIM(notes), ''), NULL) IS NULL  -- null or blank

In PostgreSQL, '' and NULL are completely different. In Oracle, they’re treated the same (empty string becomes NULL).

Practical Examples

Report completeness: find records with missing required fields:

SELECT
    customer_id,
    name,
    CASE WHEN email IS NULL THEN 'Missing' ELSE 'OK' END AS email_status,
    CASE WHEN phone IS NULL THEN 'Missing' ELSE 'OK' END AS phone_status,
    CASE WHEN address IS NULL THEN 'Missing' ELSE 'OK' END AS address_status
FROM customers
WHERE email IS NULL OR phone IS NULL OR address IS NULL
ORDER BY name;

Safe average that excludes outliers:

SELECT AVG(NULLIF(response_time_ms, 0)) AS avg_response_time
FROM api_logs
WHERE endpoint = '/api/search';
-- Zeros (likely errors or cached) treated as NULL and excluded from average