When SQL Has Nothing to Say: Understanding NULLs

NULL in SQL carry different meanings;

• missing value
• unknown value
• no value
• inapplicable data

customer_id	customer_name	contact
101	Alice	0712345678
102	Brian	NULL
103	Carol	0798765432

You shouldn't interpret NULL as;

• 0 (zero)
• '' (empty string)
• blank space

Using IS NULL, IS NOT NULL to find NULLs

Since NULL is not equal to zero or an empty string, you cannot use = to compare it; you must use IS NULL or IS NOT NULL.

select *
from customers
where contact = NULL;

This doesn't return 'Brian'

= null returns 0 rows, always!
SQL uses three-valued logic: TRUE, FALSE and UNKNOWN.
The comparison where contact = NULL produces UNKNOWN, so the row doesn't satisfy the WHERE condition.

NULL = NULL       → UNKNOWN
NULL <> NULL      → UNKNOWN
NULL = 10         → UNKNOWN

• is null returns True if the value is NULL otherwise False.

It checks whether a column contains a NULL value

-- Retrieve customers without an address

select customer_name, contact, address
from customers
where address is null;

This query returns only rows where the address column has no value.

• is not null returns True if the value is not NULL otherwise False.

It checks for non-NULL values in a column; rows with a value.

-- Retrieve customers with contact

select customer_name, contact, address
from customers
where contact is not null;

This ensures only rows with actual data in contact are returned

Counting NULL values in a column

To count how many rows in a column have NULL we use the COUNT(*) and filter the column using is null

-- Retrieve number of customers with no contact

select count(*) as null_contacts
from customers
where contact is null;

When you specify a column name instead of *, COUNT() ignores NULL values and counts all non-NULL values
i.e its equivalent to using COUNT(*) with is not null in the where clause

-- Retrieve number of customers with contact

select count(contact) as contact_count
from customers;

-- same as

select count(*) as contact_count
from customers
where contact is not null;

But, count(*) by itself counts every row, including rows containing NULLS.

NULL in calculations

Because NULL represents an unknown or missing value, SQL generally cannot determine the result of an expression involving it.

For instance:

select 100 + NULL;

returns

NULL

Updating NULL Values

Depending on how the business understands NULL, you might need to replace the NULL with a specified/default value for aggregations and other mathematical operations.

update customers
set home_address = work_address
where home_address is null;

The query fills missing home_address values with the corresponding work_address where a work_address is available.

NULL can also be used to standardize missing values

-- Syntax

UPDATE your_table
SET column_name = NULL
WHERE column_name = '';

-- replace empty strings with NULLs
update customers
set home_address = NULL
where home_address = '';

This finds rows where home_address is an empty string ('') and convert those empty values to SQL NULL.

It is useful because in a database, these two are different;

''       → empty string
NULL     → missing/unknown value

Deleting rows with NULL Values

Deleting records based on NULL values should be done carefully. Missing data does not automatically mean the entire record is invalid, so always confirm the business requirement before deleting rows.

Syntax:
DELETE FROM table_name
WHERE column_name IS NULL;

-- Delete customers with no home_address

delete from customers
where home_address is null;

Conclusion

NULL carries an important meaning: the value is missing, unknown, or not applicable.

Knowing when and how to use IS NULL and IS NOT NULL allows you to identify missing and available data, while functions such as COUNT() help you measure the extent of missing values.

Then you can decide whether those values should be updated, retained or removed based on the context of your data and business requirements.

The key takeaway? When SQL has nothing to say, NULL is saying something. You just need to know how to listen.

Watch out for part 2 on how to handle NULLs.