NULL values and IS NULL
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Real data has gaps: a customer who gave no phone number, a student who missed the exam, a new employee not yet placed in a department. SQL marks such a gap with NULL, and NULL follows its own rules in comparisons, calculations and filters.
What NULL means
NULL is a marker for "no value here": the value is missing or unknown. It is not zero, not an empty text and not the word 'NULL'. A column allows NULL unless it is declared NOT NULL.
Some customers gave no phone number. The first query lists six customers, where the word NULL marks a missing phone; the second adds 5 to an unknown mark and gets an unknown result.
Example 1 · NULL in real data
SELECT name, phone, cityFROM customersWHERE customer_id <= 6ORDER BY customer_id;
SELECT student_id, course_id, marks, marks + 5 AS with_graceFROM enrollmentsWHERE course_id = 5ORDER BY student_id;+--------------+------------+-----------+ | name | phone | city | +--------------+------------+-----------+ | Asha Rao | 9876500001 | Bengaluru | | Ravi Kumar | 9876500002 | Chennai | | Meena Iyer | NULL | Chennai | | Kabir Singh | 9876500004 | Delhi | | Priya Sharma | 9876500005 | Pune | | Arjun Mehta | NULL | Mumbai | +--------------+------------+-----------+ +------------+-----------+-------+------------+ | student_id | course_id | marks | with_grace | +------------+-----------+-------+------------+ | 4 | 5 | 80 | 85 | | 5 | 5 | NULL | NULL | +------------+-----------+-------+------------+
What this code does
- Meena Iyer and Arjun Mehta have
NULLinphone: nobody knows their numbers. The mysql client prints the marker as the word NULL. - Student 5 has no marks in course 5, so
marks + 5is NULL too. Adding grace marks to an unknown mark gives an unknown result.
A phone of '' means "we know it is empty"; NULL means "we do not know it". Without NULL people store fake values such as 0 or 'NA': a student with marks 0 really scored zero, while a student with marks NULL was absent.
In the practice database, three customers have no phone, Tanvi Kulkarni has no department, and one student has no marks yet.
NULL + 10 is NULL, because "unknown plus 10" is still unknown.Why = NULL never matches
Unknown is the result of comparing anything with NULL. It is neither true nor false, and MySQL shows it as NULL. Even NULL = NULL is unknown, because nobody can say whether two missing values are equal.
The query below puts NULL into several comparisons. Every test that uses = or <> comes back unknown; only the tests built for NULL, IS NULL and <=>, give a true 1.
Example 2 · Comparisons with NULL are unknown
SELECT NULL = NULL AS equal_test, NULL <> NULL AS not_equal_test, 0 = NULL AS zero_test, NULL IS NULL AS is_null_test, NULL <=> NULL AS null_safe_test;+------------+----------------+-----------+--------------+----------------+ | equal_test | not_equal_test | zero_test | is_null_test | null_safe_test | +------------+----------------+-----------+--------------+----------------+ | NULL | NULL | NULL | 1 | 1 | +------------+----------------+-----------+--------------+----------------+
What this code does
- The three tests that use
=or<>all give NULL: unknown. Not evenNULL = NULLis true. SoWHERE phone = NULLreturns no rows. IS NULLand<=>give1(true). They are built to treat NULL as a value you can check for.
WHERE keeps only true rows, so a test that is unknown for every row returns nothing. MySQL gives no error and no warning, so the mistake is easy to miss. <>, < and > behave the same way.
IS NULL and IS NOT NULL
IS NULL is the special test for a missing value, and IS NOT NULL for a present one. Unlike =, they always answer true or false, so they are the reliable way to find gaps.
To find the gaps, ask with IS NULL. The first query lists the customers to call for a phone number; the second keeps only the enrollments whose marks have already arrived.
Example 3 · IS NULL and IS NOT NULL
-- Customers to ask for a phone numberSELECT customer_id, name, emailFROM customersWHERE phone IS NULLORDER BY customer_id;
-- Employees not yet given a departmentSELECT emp_id, name, hire_dateFROM employeesWHERE dept_id IS NULL;
-- Students whose marks are inSELECT student_id, course_id, marksFROM enrollmentsWHERE course_id = 5 AND marks IS NOT NULL;+-------------+-------------+-------------------------+ | customer_id | name | email | +-------------+-------------+-------------------------+ | 3 | Meena Iyer | meena.iyer@example.com | | 6 | Arjun Mehta | arjun.mehta@example.com | | 10 | Imran Khan | imran.khan@example.com | +-------------+-------------+-------------------------+ +--------+----------------+------------+ | emp_id | name | hire_date | +--------+----------------+------------+ | 13 | Tanvi Kulkarni | 2025-01-06 | +--------+----------------+------------+ +------------+-----------+-------+ | student_id | course_id | marks | +------------+-----------+-------+ | 4 | 5 | 80 | +------------+-----------+-------+
What this code does
phone IS NULLfinds exactly the three customers with no number. The same query with= NULLwould return nothing.IS NOT NULLkeeps the rows that have a value. Student 4 has marks in course 5; student 5 is left out until the marks arrive.
Typical uses: customers to call, employees not yet given a department, students whose marks are still pending.
WHERE phone IS NULL: rows with no phone.WHERE phone IS NOT NULL: rows with a phone.- MySQL also has
<=>, the null-safe equal:a <=> bis true when both are NULL. It is handy when both sides may be NULL.
IFNULL and COALESCE
COALESCE(a, b, c, ...) returns the first value in its list that is not NULL. IFNULL(value, fallback) is MySQL's shortcut for two values. Both change only the result, never the stored data.
A report should show not given, not a bare NULL, and a total must not turn into NULL because one part is missing. The examples fill the gaps with IFNULL and COALESCE.
Example 4 · IFNULL and COALESCE in a report
SELECT name, IFNULL(phone, 'not given') AS phone, COALESCE(phone, email) AS best_contactFROM customersWHERE city IN ('Chennai', 'Mumbai', 'Hyderabad')ORDER BY customer_id;+-------------+------------+-------------------------+ | name | phone | best_contact | +-------------+------------+-------------------------+ | Ravi Kumar | 9876500002 | 9876500002 | | Meena Iyer | not given | meena.iyer@example.com | | Arjun Mehta | not given | arjun.mehta@example.com | | Imran Khan | not given | imran.khan@example.com | +-------------+------------+-------------------------+
What this code does
IFNULL(phone, 'not given')replaces only the missing numbers; Ravi Kumar's real number is untouched.COALESCE(phone, email)takes the phone when there is one and falls back to the email, so every customer gets some way to be contacted.
Example 5 · NULL breaks a total; COALESCE fixes it
DROP TABLE IF EXISTS order_charges;CREATE TABLE order_charges ( order_id INT PRIMARY KEY, item_total DECIMAL(9,2) NOT NULL, delivery_fee DECIMAL(5,2) NOT NULL, gift_wrap DECIMAL(5,2) NULL);INSERT INTO order_charges VALUES (1, 20597.00, 0.00, 50.00), (2, 1947.00, 49.00, NULL), (3, 43839.00, 0.00, 0.00), (5, 22198.00, 0.00, NULL);
SELECT order_id, item_total + delivery_fee + gift_wrap AS total_wrong, item_total + delivery_fee + COALESCE(gift_wrap, 0) AS total_paidFROM order_chargesORDER BY order_id;+----------+-------------+------------+ | order_id | total_wrong | total_paid | +----------+-------------+------------+ | 1 | 20647.00 | 20647.00 | | 2 | NULL | 1996.00 | | 3 | 43839.00 | 43839.00 | | 5 | NULL | 22198.00 | +----------+-------------+------------+
What this code does
- Orders 2 and 5 have no gift-wrap charge, so
item_total + delivery_fee + gift_wrapis NULL: one unknown part makes the whole sum unknown. A bill showing NULL is a real bug. COALESCE(gift_wrap, 0)treats a missing charge as zero, so every order gets a correct total. Order 3 has a stored gift wrap of0.00, which is a real value and is used as is.
COALESCE is standard SQL and works in every database.
IFNULL(marks, 0) turns an absent student into a student who scored 0. For marks, a label such as 'Absent' in a report is usually more honest.Three-valued logic in WHERE
Three-valued logic means an SQL condition can be TRUE, FALSE or UNKNOWN. WHERE keeps only the TRUE rows, so rows where the test is UNKNOWN are dropped silently, just like FALSE rows.
Two traps come from this: a NULL mark is in neither marks >= 50 nor marks < 50, and NOT IN with a NULL in its list returns nothing. The queries below show both.
Example 6 · Rows that fall through: marks and NOT IN
-- Course 5: student 4 has 80, student 5 has NULLSELECT student_id, marks FROM enrollmentsWHERE course_id = 5 AND marks >= 50;
SELECT student_id, marks FROM enrollmentsWHERE course_id = 5 AND (marks < 50 OR marks IS NULL);
-- NOT IN with a NULL in the listSELECT emp_id, name FROM employeesWHERE emp_id NOT IN (1, 2, 3) AND dept_id = 3ORDER BY emp_id;
SELECT emp_id, name FROM employeesWHERE emp_id NOT IN (1, 2, NULL) AND dept_id = 3ORDER BY emp_id;+------------+-------+ | student_id | marks | +------------+-------+ | 4 | 80 | +------------+-------+ +------------+-------+ | student_id | marks | +------------+-------+ | 5 | NULL | +------------+-------+ +--------+---------------+ | emp_id | name | +--------+---------------+ | 9 | Lakshmi Menon | | 10 | Rohan Das | +--------+---------------+
What this code does
- Student 5 is not in the
>= 50result, andmarks < 50alone would not find him either. AddingOR marks IS NULLbrings the missing row back. - The first NOT IN query returns both HR employees. The last query prints nothing at all: with NULL in the list,
emp_id <> NULLis unknown for every row, so no row is ever TRUE.
NOT does not help, because NOT UNKNOWN is still UNKNOWN. x NOT IN (2, NULL) means x <> 2 AND x <> NULL, and the second part is always unknown.
- TRUE AND UNKNOWN = UNKNOWN; FALSE AND UNKNOWN = FALSE.
- TRUE OR UNKNOWN = TRUE; FALSE OR UNKNOWN = UNKNOWN.
- To include the NULL rows, ask for them:
WHERE marks < 50 OR marks IS NULL.
Predict the output
How many rows?
Read the code and pick what it prints.
Round 1 of 3
Three customers have no phone number.
Next step
Check your understanding
11 quick questions