Core Code
Core Code Modern Dev Handbook

SQL Null Values

NULL represents missing or unknown data. Learn how to test for NULL correctly using IS NULL and IS NOT NULL.
On this page

SQL NULL Values

NULL represents a missing or unknown value. It does not mean zero, an empty string, or false. It means no value is stored.

Testing for NULL

You cannot compare NULL using = or !=. Instead, use IS NULL or IS NOT NULL.

SELECT name, phone
FROM customers
WHERE phone IS NULL;
SELECT name, phone
FROM customers
WHERE phone IS NOT NULL;

Why = NULL Does Not Work

Comparing a column to NULL using = will not return results because NULL means unknown. Any comparison with NULL results in unknown.

-- This will NOT work
SELECT name
FROM customers
WHERE phone = NULL;

NULL in Arithmetic Operations

If you perform calculations with NULL, the result is usually NULL.

SELECT price, discount, price - discount AS final_price
FROM products;

If discount is NULL, final_price will also be NULL.

Replacing NULL Values

Many database systems provide functions to replace NULL with a default value.

SELECT name, COALESCE(phone, 'N/A') AS phone
FROM customers;

In MySQL, IFNULL can also be used:

SELECT name, IFNULL(phone, 'N/A') AS phone
FROM customers;

NULL with NOT IN

Be careful when using NOT IN if the column may contain NULL values. The presence of NULL can cause the query to return no rows due to SQL’s three-valued logic.

Best Practices

  • Always use IS NULL / IS NOT NULL for NULL checks
  • Be careful when using NOT IN with nullable columns
  • Use COALESCE when displaying values to users

Next Step

Continue with SQL UPDATE to learn how to modify existing records.

SQL Update →