How to Handle Null Values in SQL

Dealing with data in databases is often like navigating a maze, especially when you come across the mysterious NULL values. If you’ve ever wondered “How to handle Null Values in SQL” effectively, you’re not alone! These pesky placeholders can be tricky, often causing unexpected errors in your queries. Understanding how to manage NULL values is crucial, and that’s exactly what we’ll dive into today. This guide will simplify NULL values using easy explanations and relatable examples. Stick around to unravel the mystery of NULL, turn those uncertainties into certainties, and boost your SQL skills to the next level!

Understanding NULL in SQL

What is NULL in SQL?

In SQL, NULL represents a missing, unknown, or undefined value in a database column. It is not the same as:

Zero (0) – A numerical value.

Empty String ('') – A valid string with zero characters.

Key Characteristics of NULL:
✅ NULL means no value is assigned.
✅ NULL is not equal to anything, including another NULL.
✅ Comparisons using = or != do not work with NULL (use IS NULL instead).

When Do NULL Values Occur?

NULL values appear in a database for various reasons:

Missing Data:
- A customer has not provided their phone number.
- Example: INSERT INTO Customers (Name, Email, Phone) VALUES ('John Doe', 'john@example.com', NULL);

Optional Fields:
- Some columns allow NULL because they are not mandatory (e.g., “Middle Name” in a user table).
Failed Joins:
- If using an OUTER JOIN, NULL appears for non-matching rows.
- Example: Finding customers with or without orders: SELECT Customers.Name, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; Customers without orders will have NULL in OrderID.
Aggregate Functions:
- Functions like AVG(), SUM(), and COUNT() ignore NULL values unless handled explicitly.
- Example: SELECT AVG(Salary) FROM Employees; -- NULL values are ignored

Implications of NULL Values in Queries

NULL values can significantly impact SQL queries, especially when using comparison operators and logical conditions. Understanding these effects is crucial to avoid incorrect query results.

1️⃣ NULL and Comparison Operators

In SQL, NULL cannot be compared using standard operators (=, !=, >, <, etc.). Any direct comparison with NULL results in UNKNOWN, not TRUE or FALSE.

Example: Why `=` Doesn’t Work with NULL

SELECT * FROM Employees WHERE Salary = NULL;  -- ❌ Returns no rows

The query fails because NULL is not equal to anything, even another NULL.
Instead, use IS NULL to check for NULL values: SELECT * FROM Employees WHERE Salary IS NULL; -- ✅ Correct approach

2️⃣ NULL in Logical Conditions (AND, OR, NOT)

SQL follows Three-Valued Logic (3VL):
✅ TRUE (Condition is met)
❌ FALSE (Condition is not met)
❓ UNKNOWN (Due to NULL involvement)

Expression	Result
`NULL = NULL`	UNKNOWN
`NULL <> 5`	UNKNOWN
`NULL AND TRUE`	UNKNOWN
`NULL OR TRUE`	TRUE
`NOT NULL`	UNKNOWN

Example: NULL with AND & OR

SELECT * FROM Employees WHERE Age > 30 AND Department = NULL;

The second condition (Department = NULL) is UNKNOWN.
Since AND requires both conditions to be TRUE, the entire condition is UNKNOWN, and the row won’t be returned.

To handle this properly, use IS NULL:

SELECT * FROM Employees WHERE Age > 30 AND Department IS NULL;

3️⃣ NULL in Aggregations and Functions

COUNT(*) counts all rows, including those with NULL values.
COUNT(column_name) ignores NULL values.

AVG(), SUM(), MIN(), MAX() ignore NULL unless explicitly handled.

Example: NULL Impact on AVG()

SELECT AVG(Salary) FROM Employees;

If some employees have NULL salaries, those rows are ignored in the average calculation.

To include NULLs as 0, use COALESCE():

SELECT AVG(COALESCE(Salary, 0)) FROM Employees;

Handling NULL in Queries

✅ Use IS NULL or IS NOT NULL instead of = or !=.
✅ Use COALESCE() to replace NULL values.
✅ Use NULLIF() to avoid division by zero errors.

Testing for NULL Values in SQL

Using `IS NULL` and `IS NOT NULL`

Since NULL cannot be compared with = or !=, SQL provides special operators:

IS NULL – Finds records where a column has NULL values.

IS NOT NULL – Finds records where a column has a non-NULL value.

Example: Using `IS NULL`

Find employees without a department assigned:

SELECT * FROM Employees WHERE Department IS NULL;

✅ Returns rows where Department has NULL values.

Example: Using `IS NOT NULL`

Find employees who have a department assigned:

SELECT * FROM Employees WHERE Department IS NOT NULL;

✅ Returns rows excluding NULL values.

Handling NULLs in Functions and Expressions

1️⃣ NULL in Arithmetic Operations

Any arithmetic operation involving NULL results in NULL.

Example: NULL in Calculations

SELECT Salary + Bonus AS Total_Pay FROM Employees;

If Bonus is NULL, the result of Salary + NULL is NULL.
To avoid this, use COALESCE().

2️⃣ Handling NULL with COALESCE()

✅ COALESCE(expression, default_value) replaces NULL with a default value.

Example: Replacing NULL Bonus with 0

SELECT Name, Salary + COALESCE(Bonus, 0) AS Total_Pay FROM Employees;

If Bonus is NULL, it is replaced with 0 instead of returning NULL.

3️⃣ Handling NULL with NULLIF()

✅ NULLIF(value1, value2) returns NULL if both values are equal; otherwise, it returns value1.

Example: Avoiding Division by Zero Errors

SELECT Salary / NULLIF(Bonus, 0) AS Bonus_Ratio FROM Employees;

If Bonus is 0, NULL is returned instead of causing an error.

Best Practices for Handling NULL

✔ Always use IS NULL or IS NOT NULL for filtering.
✔ Use COALESCE() to replace NULL with meaningful values.
✔ Use NULLIF() to prevent division by zero.

Would you like examples of handling NULL in JOINS or GROUP BY next? 😊Testing for NULL Values in SQL

Using `IS NULL` and `IS NOT NULL`

Since NULL cannot be compared with = or !=, SQL provides special operators:

IS NULL – Finds records where a column has NULL values.
IS NOT NULL – Finds records where a column has a non-NULL value.

Example: Using `IS NULL`

Find employees without a department assigned:

SELECT * FROM Employees WHERE Department IS NULL;

✅ Returns rows where Department has NULL values.

Example: Using `IS NOT NULL`

Find employees who have a department assigned:

SELECT * FROM Employees WHERE Department IS NOT NULL;

✅ Returns rows excluding NULL values.

Handling NULLs in Functions and Expressions

1️⃣ NULL in Arithmetic Operations

Any arithmetic operation involving NULL results in NULL.

Example: NULL in Calculations

SELECT Salary + Bonus AS Total_Pay FROM Employees;

If Bonus is NULL, the result of Salary + NULL is NULL.

To avoid this, use COALESCE().

2️⃣ Handling NULL with COALESCE()

✅ COALESCE(expression, default_value) replaces NULL with a default value.

Example: Replacing NULL Bonus with 0

SELECT Name, Salary + COALESCE(Bonus, 0) AS Total_Pay FROM Employees;

If Bonus is NULL, it is replaced with 0 instead of returning NULL.

3️⃣ Handling NULL with NULLIF()

✅ NULLIF(value1, value2) returns NULL if both values are equal; otherwise, it returns value1.

Example: Avoiding Division by Zero Errors

SELECT Salary / NULLIF(Bonus, 0) AS Bonus_Ratio FROM Employees;

If Bonus is 0, NULL is returned instead of causing an error.

NULLs in Aggregate Functions

In SQL, NULL values are ignored by most aggregate functions, except for COUNT(*). Understanding how NULLs affect calculations is crucial for accurate data analysis.

How Aggregate Functions Handle NULLs

Function	Effect on NULL Values
`COUNT(*)`	✅ Includes NULL values
`COUNT(column_name)`	❌ Excludes NULL values
`SUM(column_name)`	❌ Ignores NULL values
`AVG(column_name)`	❌ Ignores NULL values
`MAX(column_name)`	❌ Ignores NULL values
`MIN(column_name)`	❌ Ignores NULL values

**Example: COUNT(*) vs. COUNT(column_name)**

Consider a table named Employees:

ID	Name	Salary	Bonus
1	John	50000	5000
2	Jane	60000	NULL
3	Mark	55000	3000

SELECT COUNT(*) FROM Employees;  
-- ✅ Returns 3 (counts all rows, including NULLs)

SELECT COUNT(Bonus) FROM Employees;  
-- ❌ Returns 2 (ignores NULLs)

🔹 COUNT(*) counts all rows, including NULLs.
🔹 COUNT(Bonus) excludes NULL values.

Example: NULL in SUM and AVG

SELECT SUM(Bonus) FROM Employees;  
-- ✅ Returns 8000 (ignores NULL)

SELECT AVG(Bonus) FROM Employees;  
-- ✅ Returns 4000 (8000 / 2, ignores NULL)

🔹 SUM and AVG ignore NULL values, which may distort calculations.
🔹 To include NULLs as zero, use COALESCE():

SELECT AVG(COALESCE(Bonus, 0)) FROM Employees;  
-- ✅ Returns 2666.67 (8000 / 3, replaces NULL with 0)

Example: NULL in MIN and MAX

SELECT MIN(Bonus), MAX(Bonus) FROM Employees;  
-- ✅ Returns (3000, 5000) (ignores NULL)

🔹 NULL values are ignored in MIN and MAX calculations.

Real-Life Applications of Handling Null Values in SQL

In real-world scenarios, companies need to handle nulls smartly to maintain data accuracy and functionality. Here’s how some have done it:

E-commerce Platforms: Missing customer data, like email or phone number, is often handled by displaying messages such as “Contact info not available.” This ensures that the user interface remains clean and informative.

Healthcare Applications: Patient records might sometimes lack data related to treatment details. By using COALESCE, health systems fill in gaps like “Data Pending” when accessing patient information, maintaining continuity in medical records.
Banking Software: Financial institutions use IS NULL checks to manage incomplete application forms. For example, a missing middle name might not raise an error and defaults gracefully to ‘N/A’. This prevents possible application interruptions.

By learning how to handle null values in SQL, you ensure that your database operations proceed smoothly, giving you a more accurate reflection of your data landscape. Have you started thinking about how you can use these techniques in your projects? It’s much simpler than it first appears, so don’t hesitate to try it out!

Feel free to drop your questions or experiences in the comments. Wouldn’t it be great to nail this skill if you haven’t already? Happy querying!

Curious to test this out yourself? With our AI-powered sql online compiler, you can instantly write, run, and test your code. It’s interactive and gives you instant results―making learning SQL a breeze! Isn’t that handy for practice?

Best Practices for Managing NULL Values in SQL

Handling NULL values efficiently ensures data accuracy, consistency, and reliable query results. Below are key strategies to manage NULLs effectively in SQL databases.

1️⃣ Set Default Values

Prevent NULL values by setting default values when creating tables. This ensures that if no value is provided, a meaningful default is used instead.

✅ Example: Setting Default Values

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2) NOT NULL,
    Bonus DECIMAL(10,2) DEFAULT 0  -- Prevents NULL values
);

🔹 Now, if a new row is inserted without a Bonus value, it defaults to 0 instead of NULL.

2️⃣ Use NOT NULL Constraints

Restrict columns from allowing NULL values if they must always have data.

✅ Example: Enforcing NOT NULL Constraint

ALTER TABLE Employees MODIFY Salary DECIMAL(10,2) NOT NULL;

🔹 This ensures Salary always contains a value.

3️⃣ Handling NULLs in Queries

NULL values can affect query results, especially with filtering and calculations.

✅ Use IS NULL / IS NOT NULL to filter records with or without NULLs:

SELECT * FROM Employees WHERE Bonus IS NULL;
SELECT * FROM Employees WHERE Bonus IS NOT NULL;

✅ Use COALESCE() to Replace NULLs
The COALESCE() function replaces NULLs with a default value:

SELECT Name, COALESCE(Bonus, 0) AS AdjustedBonus FROM Employees;

🔹 Converts NULL Bonus values to 0 instead.

✅ Use NULLIF() to Prevent Errors
NULLIF() returns NULL if two values are equal, preventing division errors:

SELECT Salary / NULLIF(Bonus, 0) FROM Employees;

🔹 Avoids division by zero errors by returning NULL instead.

4️⃣ Anticipate NULLs in Database Design

Before designing a database, consider scenarios where NULLs might occur:

✔ Should a missing phone number be NULL or a placeholder like 'N/A'?
✔ Should an optional discount be NULL or default to 0.00?
✔ Should date fields use NULL or a default like '0000-00-00'?

Defining clear rules prevents issues later when querying data.

5️⃣ Handle NULLs in Aggregate Functions

✔ COUNT(*) counts all rows, including NULLs.

✔ COUNT(column) ignores NULLs.
✔ SUM, AVG, MAX, MIN ignore NULLs unless handled explicitly.

✅ Example: Avoid Misleading Averages

SELECT AVG(COALESCE(Bonus, 0)) FROM Employees;

🔹 Ensures NULLs don’t distort the average.

Final Tip: Always Test for NULLs

Before running queries, check for NULLs to avoid unexpected results:

SELECT COUNT(*), COUNT(Bonus) FROM Employees;

🔹 Compare both counts to see how many rows have NULL Bonus values.

Conclusion

Handling NULL values in SQL is crucial for clean and effective database management. By mastering these techniques, you’re well-equipped to tackle data integrity issues. For more detailed tutorials and courses, explore Newtum. Keep learning and practicing—your SQL skills will only grow stronger!

Edited and Compiled by

This blog was compiled and edited by @rasikadeshpande, who has over 4 years of experience in content creation. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author

Rasika Deshpande

Rasika Deshpande, a passionate writer with over 4 years of experience in the EdTech industry. At Newtum, she writes, edit, and compile engaging blog and other content. Writing is not just a profession for her; it’s a passion that fuels creativity and dedication to the field of writing.

See author's posts

Understanding NULL in SQL

What is NULL in SQL?

When Do NULL Values Occur?

Implications of NULL Values in Queries

1️⃣ NULL and Comparison Operators

Example: Why = Doesn’t Work with NULL

2️⃣ NULL in Logical Conditions (AND, OR, NOT)

Example: NULL with AND & OR

3️⃣ NULL in Aggregations and Functions

Example: NULL Impact on AVG()

Handling NULL in Queries

Testing for NULL Values in SQL

Using IS NULL and IS NOT NULL

Example: Using IS NULL

Example: Using IS NOT NULL

Handling NULLs in Functions and Expressions

1️⃣ NULL in Arithmetic Operations

Example: NULL in Calculations

2️⃣ Handling NULL with COALESCE()

Example: Replacing NULL Bonus with 0

3️⃣ Handling NULL with NULLIF()

Example: Avoiding Division by Zero Errors

Best Practices for Handling NULL

Using IS NULL and IS NOT NULL

Example: Using IS NULL

Example: Using IS NOT NULL

Handling NULLs in Functions and Expressions

Example: NULL in Calculations

NULLs in Aggregate Functions

How Aggregate Functions Handle NULLs

Example: COUNT(*) vs. COUNT(column_name)

Example: NULL in SUM and AVG

Example: NULL in MIN and MAX

Real-Life Applications of Handling Null Values in SQL

Best Practices for Managing NULL Values in SQL

1️⃣ Set Default Values

2️⃣ Use NOT NULL Constraints

3️⃣ Handling NULLs in Queries

4️⃣ Anticipate NULLs in Database Design

5️⃣ Handle NULLs in Aggregate Functions

Final Tip: Always Test for NULLs

Conclusion

Edited and Compiled by

About The Author

Rasika Deshpande

You Might Also Like

Convert String to Int in Java

Mastering Basics: Convert Decimal to Binary in JavaScript

C++ Basic Syntax: A Beginner’s Journey

Example: Why `=` Doesn’t Work with NULL

Using `IS NULL` and `IS NOT NULL`

Example: Using `IS NULL`

Example: Using `IS NOT NULL`

Using `IS NULL` and `IS NOT NULL`

Example: Using `IS NULL`

Example: Using `IS NOT NULL`

**Example: COUNT(*) vs. COUNT(column_name)**