Handling `NULL` values in SQL

Handling `NULL` values in SQL can be done in various ways depending on your use case.

Here are the `NULL` handling functions used in different databases:

Each of these functions is used to handle `NULL` values in similar but sometimes slightly different ways across these databases.

1. Using `IS NULL` or `IS NOT NULL` in Queries

You can use `IS NULL` or `IS NOT NULL` to filter records with or without `NULL` values.

• Example: Products table

select *from product where unitsonorder is null;
select *from product where unitsonorder is not null;

2. Using `IFNULL()` Function

The `IFNULL()` function allows you to replace `NULL` values with a specific value.

Syntax :
SELECT IFNULL(column_name, 'default_value') FROM table_name;

Example
select *,ifnull(unitsonorder,0) from product

3. Using `COALESCE()` Function

Get Nikita Nikumbh’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The `COALESCE()` function returns the first non-`NULL` value from a list of arguments.

#Syntax
SELECT COALESCE(column1, column2, 'default_value') FROM table_name;

#Example
select *,COALESCE(unitsonorder,unitprice,1) from product

It will check each row for unitsonorder,unitprice. If unitsonorder is null it will return value of column unitprice . If unitprice column is also null it will give default value here it is 1.

4. Using `NULLIF()` Function

· The `NULLIF()` function returns `NULL` if two expressions are equal; otherwise, it returns the first expression.

· The NULLIF() function is useful for handling specific cases where you want to replace certain values (e.g., zeros, negative numbers, etc.) with NULL to avoid calculation issues or to mark them as missing.

· It helps prevent common issues like division by zero and can be used to conditionally return NULL in various scenarios.

#syntax
SELECT NULLIF(column1, 0) FROM table_name;

#Example
SELECT payment_id, payment_amount, NULLIF(discount, 0) AS discount
FROM payments;

5. Handling `NULL` in Aggregations

Some aggregation functions, like `COUNT()`, ignore `NULL` values, while others like `SUM()`, `AVG()`, and `MAX()` include them unless handled.

#Example: - Counts non-NULL values
SELECT COUNT(isnull(unitprice,0)) FROM product;
SELECT avg(isnull(unitprice,0)) FROM product;

6. Using `UPDATE` to Replace `NULL`

You can update a table to replace `NULL` values with specific values.

#syntax
UPDATE table_name SET column_name = 'default_value' WHERE column_name IS NULL;

7. Handling `NULL` in Joins

When joining tables, be careful with `NULL` values as they can result in missed rows if not handled.

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
WHERE table2.column_name IS NULL;

These techniques give you flexibility in handling `NULL` values based on your specific requirements.