beginner 5 min read

Working with NULL Values

Written with a little help from AI, reviewed and edited by a human.

In SQL, NULL means “no value” or “unknown”. It’s not the same as zero or an empty string — it literally means the data is missing. Understanding how to work with NULL is important because it behaves differently than you might expect.

What is NULL?

NULL represents the absence of a value. For example:

idnameemailphone
1Alicealice@example.com555-1234
2Bobbob@example.comNULL
3CharlieNULL555-5678
4Dianadiana@example.comNULL
  • Bob hasn’t provided a phone number, so it’s NULL
  • Charlie hasn’t provided an email, so it’s NULL
  • Diana also hasn’t provided a phone number

NULL is different from:

  • An empty string '' (which is a value — it’s just empty)
  • Zero 0 (which is a number)
  • False (which is a boolean value)

NULL means “we don’t know” or “this doesn’t exist yet”.

Real life example

Imagine you’re taking attendance in class. You have a list of students and a column for “Homework Score”:

  • Alice turned in homework → 85 points
  • Bob turned in homework → 92 points
  • Charlie didn’t turn in homework yet → NULL (not zero — we don’t know the score yet because there’s no homework to grade)

NULL means “no data exists”. Zero would mean “they got zero points”, which is different.

Checking for NULL with IS NULL

You cannot use = to check for NULL. This will not work:

-- ❌ WRONG
SELECT * FROM users WHERE phone = NULL;

This returns nothing, even if there are rows with NULL phone numbers. Why? Because NULL means “unknown”, and you can’t compare something to “unknown” using =.

Instead, use IS NULL:

SELECT * FROM users WHERE phone IS NULL;

Result:

idnameemailphone
2Bobbob@example.comNULL
4Dianadiana@example.comNULL

This correctly finds all users who haven’t provided a phone number.

Checking for non-NULL with IS NOT NULL

To find rows that do have a value (not NULL), use IS NOT NULL:

SELECT * FROM users WHERE phone IS NOT NULL;

Result:

idnameemailphone
1Alicealice@example.com555-1234
3CharlieNULL555-5678

This finds users who have provided a phone number.

NULL in comparisons

NULL behaves strangely in comparisons. Look at this:

SELECT * FROM users WHERE email = 'test@example.com';

This will not return Charlie, even though Charlie’s email is NULL. Why? Because NULL = anything is always “unknown” (not true, not false — just unknown).

Even this returns nothing:

SELECT * FROM users WHERE email = NULL;  -- ❌ Returns nothing

Again, you must use IS NULL:

SELECT * FROM users WHERE email IS NULL;  -- ✅ Correct

NULL with AND/OR

NULL in conditions can produce unexpected results:

SELECT * FROM users WHERE phone IS NULL AND email IS NOT NULL;

This finds users who are missing a phone but have an email:

idnameemailphone
2Bobbob@example.comNULL
4Dianadiana@example.comNULL

COALESCE — Providing a default value

Sometimes you want to replace NULL with a default value. Use COALESCE:

SELECT name, COALESCE(phone, 'No phone provided') AS phone
FROM users;

Result:

namephone
Alice555-1234
BobNo phone provided
Charlie555-5678
DianaNo phone provided

COALESCE(phone, 'No phone provided') means: “If phone is NULL, use ‘No phone provided’ instead.”

You can provide multiple fallback values:

SELECT COALESCE(phone, email, 'No contact info') AS contact
FROM users;

This checks phone first. If it’s NULL, it checks email. If that’s also NULL, it uses ‘No contact info’.

NULL in aggregate functions

Most aggregate functions ignore NULL values:

SELECT AVG(score) FROM students;

If some students have NULL scores (because they haven’t taken the test yet), those rows are not included in the average. It only averages the non-NULL values.

Same with COUNT:

SELECT COUNT(phone) FROM users;

Result: 2 (only Alice and Charlie have phone numbers)

But COUNT(*) counts all rows, even if some columns are NULL:

SELECT COUNT(*) FROM users;

Result: 4 (all users, regardless of NULL values)

When to use NULL

Use NULL when:

  • The data is optional and might not exist (like a middle name, or a phone number)
  • The data hasn’t been provided yet (like a score for an ungraded assignment)
  • The data is unknown or not applicable

Don’t use NULL as a substitute for meaningful values like zero or empty string if those have specific meanings in your application.

Key takeaways

  • NULL means “no value” or “unknown”
  • Use IS NULL and IS NOT NULL, never = NULL
  • NULL is not the same as zero, false, or empty string
  • Use COALESCE to provide default values for NULL
  • Aggregate functions ignore NULL (except COUNT(*))
Share: