NULL Values in SQL: What They Mean and How to Use Them

If you're new to SQL, handling NULL values can be one of the more confusing concepts to grasp. Unlike zero or an empty string, NULL means “unknown” or “missing.” This seemingly simple idea can have a huge impact on how queries behave.

In this guide, we’ll explain what NULL is, how it affects comparisons and queries, and how to work with it correctly. Whether you're writing your first query or debugging strange results, understanding how to use NULL properly is essential.

What Is NULL in SQL?

In SQL, NULL represents a missing or undefined value. It doesn't mean zero or empty—it means the value is not known or hasn’t been provided.

For example:

INSERT INTO users (name, email) VALUES ('Alice', NULL);


In this case, the email value for Alice is unknown. It's not an empty string, it’s explicitly NULL.

How NULL Affects Comparisons

This is where things get tricky. NULL is not equal to anything—not even another NULL. That’s why this condition fails:

SELECT * FROM users WHERE email = NULL;
-- returns nothing


To compare NULL values, you must use:

• IS NULL
• IS NOT NULL

Correct Usage

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


Want to practice these? You can use the Galaxy SQL Editor to run examples in a browser with no setup.

How NULL Works in Logic and Aggregates

When you use logical operators (=, !=, AND, OR) or functions like COUNT() with NULL, behavior can vary.

COUNT vs COUNT(*)

• COUNT(column) ignores NULL values.
• COUNT(*) counts all rows, including rows where the column is NULL.

SELECT COUNT(email) FROM users;     -- excludes NULLs
SELECT COUNT(*) FROM users;         -- includes all rows


Using COALESCE to Replace NULLs

The COALESCE() function returns the first non-NULL value in a list. It’s commonly used to replace NULL values with a default.

Example:

SELECT name, COALESCE(email, 'no-email@example.com') AS contact_email
FROM users;


This ensures that every user has a fallback email address in the result.

Other similar functions:

• IFNULL(a, b) (MySQL)
• NVL(a, b) (Oracle)

NULL and GROUP BY

When you group by a column that contains NULL, those rows form a separate group.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;


If some employees don’t have a department, they’ll be grouped under NULL.

For more clarity on how this interacts with query results, read our guide to GROUP BY vs ORDER BY.

Filtering NULLs in WHERE Clauses

Use IS NULL and IS NOT NULL to include or exclude missing values.

Example:

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


These are especially helpful in reporting, data cleanup, and debugging missing data.

NULL in Conditional Expressions

You can use CASE to handle NULL logic in custom outputs:

SELECT name,
 CASE
   WHEN email IS NULL THEN 'Missing Email'
   ELSE email
 END AS email_status
FROM users;


This will label users without email addresses clearly in your result set.

Real-World Use Cases

1. Handling Optional Fields

User signups often don’t include every field. NULL helps you track what’s missing.

2. Error Detection

Query for NULL values to find where data pipelines or forms failed.

3. Data Substitution

Use COALESCE to plug holes in your data for better reporting or presentation.

Want more examples like these? Explore our SQL Learning Center and our Common SQL Errors Guide.

Best Practices for Working with NULL

• Never use = or != to compare with NULL. Always use IS NULL or IS NOT NULL.
• Be careful with joins—NULL keys can cause rows to disappear.
• Use COALESCE or CASE to handle missing values explicitly in outputs.
• Understand how aggregate functions treat NULL so you don’t misinterpret results.

Final Thoughts

Working with NULL values is a critical skill in SQL that separates beginner mistakes from clean, production-ready queries. Knowing how to check, replace, and work around NULL will help you avoid errors and write more effective reports.

Want to try your own IS NULL and COALESCE queries? Launch the Galaxy SQL Editor and write safe, schema-aware SQL in seconds.