MySQL IS NULL and IS NOT NULL

In MySQL, NULL represents the absence of a value — a field that has no data. To check whether a column contains NULL, the special operators IS NULL and IS NOT NULL are used. Regular comparison operators like = or != do not work with NULL values.

Why Regular Operators Fail with NULL

In MySQL, any comparison with NULL using = or != returns unknown — neither true nor false. This means these queries return no rows even when NULL values exist:

-- Does NOT work for finding NULL values
SELECT * FROM employees WHERE phone = NULL;
SELECT * FROM employees WHERE phone != NULL;

The correct approach is:

SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

Syntax

-- Find rows where column is NULL
SELECT column_name FROM table_name WHERE column_name IS NULL;

-- Find rows where column has a value
SELECT column_name FROM table_name WHERE column_name IS NOT NULL;

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    manager_id INT
);

INSERT INTO employees VALUES (1, 'Rahul Mehta', '9876543210', NULL);
INSERT INTO employees VALUES (2, 'Sneha Joshi', NULL, 1);
INSERT INTO employees VALUES (3, 'Karan Das', '9123456789', 1);
INSERT INTO employees VALUES (4, 'Pooja Nair', NULL, 2);
INSERT INTO employees VALUES (5, 'Amit Roy', '9988776655', 2);

Example: Find Employees with No Phone Number

SELECT emp_name, phone
FROM employees
WHERE phone IS NULL;

Result:

emp_name    | phone
------------+------
Sneha Joshi | NULL
Pooja Nair  | NULL

Example: Find Employees with a Phone Number

SELECT emp_name, phone
FROM employees
WHERE phone IS NOT NULL;

Result:

emp_name     | phone
-------------+------------
Rahul Mehta  | 9876543210
Karan Das    | 9123456789
Amit Roy     | 9988776655

Example: Find Top-Level Managers (No Manager Assigned)

SELECT emp_name
FROM employees
WHERE manager_id IS NULL;

Result:

emp_name
---------
Rahul Mehta

Using IS NULL with UPDATE

UPDATE employees
SET phone = '0000000000'
WHERE phone IS NULL;

This sets a default phone number for all employees whose phone is currently NULL.

Using IS NULL with COUNT

SELECT COUNT(*) AS no_phone_count
FROM employees
WHERE phone IS NULL;

IFNULL Function — Handling NULL in Results

The IFNULL() function replaces NULL with a specified value in query output.

SELECT emp_name, IFNULL(phone, 'Not Provided') AS phone_number
FROM employees;

Result:

emp_name     | phone_number
-------------+-------------
Rahul Mehta  | 9876543210
Sneha Joshi  | Not Provided
Karan Das    | 9123456789
Pooja Nair   | Not Provided
Amit Roy     | 9988776655

Key Points

• NULL means no value is present — it is not zero, not empty string, not blank.
• Use IS NULL to find rows with missing values.
• Use IS NOT NULL to find rows with actual values.
• Standard operators like = and != cannot compare NULL values.
• IFNULL() is useful for replacing NULL with a readable placeholder in output.