? MySQL NULL Values

⚡ Quick Overview

In MySQL, NULL represents an unknown or missing value, not zero, not an empty string, and not false. You must use special comparison operators and functions like IS NULL, IS NOT NULL, IFNULL(), and COALESCE() to work with NULL values correctly.

Understanding how NULL behaves in comparisons, conditions, and aggregate functions is essential for accurate queries and reports.

KEY IDEA: NULL = unknown / missing, not 0

? Key Concepts

NULL value – indicates that a column has no value (unknown / not provided).
NOT the same as:
- 0 (zero)
- '' (empty string)
- FALSE or 0.0
Comparison with = or <> does not work with NULL – use IS NULL / IS NOT NULL.
NULL in expressions – arithmetic or concatenation with NULL usually produces NULL.
Aggregates & NULL – functions like SUM, AVG, COUNT(col) ignore NULL values.
Handling NULL – use IFNULL() or COALESCE() to replace NULL with default values.

? Syntax & Theory

Common patterns when working with NULL in MySQL:

Check for NULL:
- column_name IS NULL
- column_name IS NOT NULL
Replace NULL with some default value:
- IFNULL(expr, default_value)
- COALESCE(expr1, expr2, ..., exprN) – returns first non-NULL value.
Sorting with NULL:
- ORDER BY will usually place NULLs first (ASC) or last (DESC), depending on MySQL version / settings.

? Example 1 – Basic NULL Checks

Suppose we have a employees table where some employees do not have a recorded commission:

? View Code Example (Table Structure & Sample Data)

-- Create employees table with NULL-able commission column
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  salary DECIMAL(10,2) NOT NULL,
  commission DECIMAL(10,2) NULL
);

-- Insert sample rows; some employees have no commission
INSERT INTO employees (id, name, salary, commission) VALUES
(1, 'Alice', 50000.00, 2000.00),
(2, 'Bob',   45000.00, NULL),
(3, 'Carol', 60000.00, 3000.00),
(4, 'Dave',  40000.00, NULL);

? View Code Example (IS NULL / IS NOT NULL)

-- Find employees where commission is missing (NULL)
SELECT id, name, salary, commission
FROM employees
WHERE commission IS NULL;

-- Find employees where commission is recorded (NOT NULL)
SELECT id, name, salary, commission
FROM employees
WHERE commission IS NOT NULL;

? Output / Explanation

Query 1 (commission IS NULL):

Returns rows for Bob and Dave because their commission column has NULL.

Query 2 (commission IS NOT NULL):

Returns rows for Alice and Carol where commission has a numeric value.

❓ Example 2 – Why `= NULL` Does Not Work

Using = NULL or <> NULL does not produce the expected result, because comparisons with NULL yield UNKNOWN in three-valued logic (TRUE / FALSE / UNKNOWN).

? View Code Example (Incorrect vs Correct)

-- ❌ Incorrect: this returns zero rows even if commission is NULL
SELECT id, name, commission
FROM employees
WHERE commission = NULL;

-- ✅ Correct: use IS NULL to test for NULL values
SELECT id, name, commission
FROM employees
WHERE commission IS NULL;

? Explanation

commission = NULL is never TRUE, so the query returns no rows.
commission IS NULL is specially defined to check for NULL values correctly.

? Example 3 – Aggregates with NULL

Aggregate functions like SUM(), AVG(), and COUNT(col) ignore NULL values. Only COUNT(*) counts all rows.

? View Code Example (Aggregate Functions)

-- Count all rows in the table (including NULL values)
SELECT COUNT(*) AS total_employees
FROM employees;

-- Count only rows where commission is NOT NULL
SELECT COUNT(commission) AS employees_with_commission
FROM employees;

-- Calculate total and average commission (NULL values ignored)
SELECT
  SUM(commission) AS total_commission,
  AVG(commission) AS average_commission
FROM employees;

? Output / Explanation

COUNT(*) → counts all 4 employees.
COUNT(commission) → counts only rows with non-NULL commission (2 employees).
SUM(commission) and AVG(commission) ignore NULL commission values.

? Example 4 – Replacing NULL with Default Values

To display or use a substitute value instead of NULL, use IFNULL() or COALESCE().

? View Code Example (IFNULL & COALESCE)

-- Show commission; if NULL, display 0 instead
SELECT
  name,
  IFNULL(commission, 0) AS commission_amount
FROM employees;

-- Use COALESCE to choose first non-NULL value
SELECT
  name,
  COALESCE(commission, 0) AS commission_amount
FROM employees;

-- Use COALESCE with multiple fallbacks (example pattern)
SELECT
  name,
  COALESCE(commission, salary * 0.05, 0) AS final_commission
FROM employees;

? Output / Explanation

IFNULL(commission, 0) returns the commission if present, otherwise 0.
COALESCE(commission, 0) behaves similarly for this two-argument case.
With multiple arguments, COALESCE() returns the first non-NULL expression.

? Tips & Best Practices

Always use IS NULL and IS NOT NULL to test for NULL values.
Do not confuse NULL with 0 or an empty string. They have different meanings and behaviors.
When designing tables, allow NULL only if “unknown / not applicable” is a valid state.
Use IFNULL() or COALESCE() when displaying data to users to avoid showing blank or NULL values.
Be careful with expressions like col1 + col2; if one column is NULL, the result may be NULL.
Prefer COUNT(*) for total rows and COUNT(column) specifically when you want to exclude NULL.

? Try It Yourself

Create a table students with columns: id, name, email, and phone. Allow phone to be NULL.
Insert at least 5 sample records where some students have phone as NULL and some have valid numbers.
Write a query to list only students without a phone number (use IS NULL).
Write a query to show all students, but where phone is NULL, display 'Not Provided' using IFNULL() or COALESCE().
Count how many students have provided a phone number using COUNT(phone), and compare it with COUNT(*).

? View Code Example (Sample Practice Solution)

-- 1. Create students table
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NULL
);

-- 2. Insert sample data
INSERT INTO students (id, name, email, phone) VALUES
(1, 'John',  'john@example.com',  '9991110001'),
(2, 'Mia',   'mia@example.com',   NULL),
(3, 'Ravi',  'ravi@example.com',  '8882220003'),
(4, 'Sara',  'sara@example.com',  NULL),
(5, 'Liam',  'liam@example.com',  '7773330005');

-- 3. Students without phone (NULL)
SELECT id, name, email, phone
FROM students
WHERE phone IS NULL;

-- 4. Display 'Not Provided' when phone is NULL
SELECT
  id,
  name,
  email,
  IFNULL(phone, 'Not Provided') AS phone_display
FROM students;

-- 5. Compare total rows vs rows with phone
SELECT
  COUNT(*)        AS total_students,
  COUNT(phone)    AS students_with_phone
FROM students;