00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL IS NULL Condition

Last updated: 5 min read

In SQL, NULL marks a value that is missing or unknown: a patient who hasn't been discharged yet, a phone number nobody gave. NULL isn't zero and isn't an empty string, and it can't be found with =. The IS NULL condition is how you find it.

1. Why = NULL Doesn't Work

Comparing with NULL gives NULL, not TRUEphone = NULL evaluates to NULL for every row, so no row is returned. phone IS NULL evaluates to TRUE for rows with a missing phone, so those rows are returned.WHERE phone = NULL'98450…' = NULL → NULLNULL = NULL → NULLno row is ever TRUE → 0 rowsWHERE phone IS NULL'98450…' IS NULL → FALSENULL IS NULL → TRUEmissing phones are found ✔
Figure 1: NULL means "unknown", so asking whether something equals unknown has an unknown answer. Use IS NULL.

Table: patients (5 rows)

idnamephoneemaildischarge_datebillinsurer
1Anil98450 11111anil@mail.example2026-03-0218500.00StarCare
2BinaNULLbina@mail.exampleNULLNULLNULL
3Cyril99000 22222NULL2026-03-057200.00NULL
4DollyNULLNULLNULL32000.00HealthPlus
5Emil97300 333332026-03-074100.00StarCare
query.sql 
SELECT name FROM patients WHERE phone = NULL;

= NULL: always empty

name
No rows
query.sql 
SELECT name FROM patients WHERE phone IS NULL;

IS NULL: correct

name
Bina
Dolly

2. Syntax

syntax.sql 
expression IS NULL
expression IS NOT NULL
query.sql 
SELECT NULL = NULL AS eq, NULL IS NULL AS is_null, 0 IS NULL AS zero, '' IS NULL AS empty_string;

Result: 1 row

eqis_nullzeroempty_string
NULL100

3. Finding Missing Data

query.sql 
SELECT name FROM patients WHERE discharge_date IS NULL;   -- still admitted

Result: 2 rows

name
Bina
Dolly
query.sql 
SELECT name FROM patients WHERE phone IS NULL AND email IS NULL;   -- no way to contact

Result: 1 row

name
Dolly

Emil's email is an empty string, which is not NULL. To treat empty strings as missing too:

query.sql 
SELECT name, email FROM patients WHERE email IS NULL OR email = '';

Result: 3 rows

nameemail
CyrilNULL
DollyNULL
Emil
query.sql 
SELECT name FROM patients WHERE NULLIF(email, '') IS NULL;   -- same thing, shorter

Result: 3 rows

name
Cyril
Dolly
Emil

4. IS NULL in UPDATE and DELETE

update.sql 
UPDATE patients SET insurer = 'Self-pay' WHERE insurer IS NULL AND bill IS NOT NULL;
SELECT name, insurer FROM patients;

Result: 5 rows

nameinsurer
AnilStarCare
BinaNULL
CyrilSelf-pay
DollyHealthPlus
EmilStarCare
UPDATE … SET col = NULL is correct: in SET, = is assignment, not comparison.

5. Replacing NULLs: IFNULL, COALESCE, NULLIF

FunctionReturnsExample
IFNULL(a, b)a, or b if a is NULLIFNULL(phone, 'n/a')
COALESCE(a, b, c, …)The first non-NULL argument (standard SQL)COALESCE(phone, email, 'none')
NULLIF(a, b)NULL if a = b, else aNULLIF(email, '')
ISNULL(a)1 if a is NULL, else 0ISNULL(bill)
functions.sql 
SELECT name,
       IFNULL(phone, 'n/a')                         AS phone,
       COALESCE(phone, NULLIF(email, ''), 'none')    AS best_contact,
       IFNULL(bill, 0)                               AS bill_or_zero
FROM patients;

Result: 5 rows

namephonebest_contactbill_or_zero
Anil98450 1111198450 1111118500.00
Binan/abina@mail.example0.00
Cyril99000 2222299000 222227200.00
Dollyn/anone32000.00
Emil97300 3333397300 333334100.00

6. The NULL-Safe Equal Operator <=>

<=> works like = but treats two NULLs as equal and never returns NULL. Handy when comparing two columns that may be NULL:

query.sql 
SELECT NULL <=> NULL AS both_null, 'x' <=> NULL AS one_null, 'x' <=> 'x' AS equal;

Result: 1 row

both_nullone_nullequal
101
query.sql 
SELECT name FROM patients WHERE insurer <=> NULL;

Result: 1 row

name
Bina

7. NULLs in Aggregates and Arithmetic

Aggregate functions skip NULLs (except COUNT(*)). Any arithmetic with NULL gives NULL:

query.sql 
SELECT COUNT(*) AS patients, COUNT(bill) AS with_bill, SUM(bill) AS total, AVG(bill) AS avg_known, AVG(IFNULL(bill, 0)) AS avg_as_zero FROM patients;

Result: 1 row

patientswith_billtotalavg_knownavg_as_zero
5461800.0015450.00000012360.000000
query.sql 
SELECT 100 + NULL AS plus, CONCAT('Ward ', NULL) AS concat_null, CONCAT_WS(' ', 'Ward', NULL, 'B') AS concat_ws;

Result: 1 row

plusconcat_nullconcat_ws
NULLNULLWard B

8. NULLs in ORDER BY and GROUP BY

query.sql 
SELECT name, discharge_date FROM patients ORDER BY discharge_date;

Result: 5 rows

namedischarge_date
BinaNULL
DollyNULL
Anil2026-03-02
Cyril2026-03-05
Emil2026-03-07

NULLs sort first in ascending order and form one group in GROUP BY:

query.sql 
SELECT discharge_date IS NULL AS still_admitted, COUNT(*) AS patients FROM patients GROUP BY discharge_date IS NULL;

Result: 2 rows

still_admittedpatients
03
12

9. IS NULL Can Use an Index

explain.sql 
CREATE INDEX idx_discharge ON patients (discharge_date);
EXPLAIN SELECT name FROM patients WHERE discharge_date IS NULL;

Result: 1 row

typekeyExtra
refidx_dischargeUsing index condition

10. Practice Exercises

Exercise 1: Patients without a phone number, showing their email or 'no contact'.
solution.sql 
SELECT name, COALESCE(NULLIF(email, ''), 'no contact') AS contact
FROM patients WHERE phone IS NULL;

Result: 2 rows

namecontact
Binabina@mail.example
Dollyno contact
Exercise 2: Show each patient's discharge date, or 'Admitted' if there is none.
solution.sql 
SELECT name, COALESCE(DATE_FORMAT(discharge_date, '%d %b %Y'), 'Admitted') AS status FROM patients;

Result: 5 rows

namestatus
Anil02 Mar 2026
BinaAdmitted
Cyril05 Mar 2026
DollyAdmitted
Emil07 Mar 2026

11. Summary

  • NULL means unknown; any comparison with NULL using =, <>, < or > gives NULL.
  • Use IS NULL / IS NOT NULL to test for it.
  • Empty strings and 0 are values, not NULL; NULLIF turns them into NULL when needed.
  • IFNULL and COALESCE supply defaults; <=> compares NULL-safely.
  • Aggregates skip NULLs; arithmetic with NULL gives NULL.