Null values in SQL play a crucial role in relational databases by acting as a marker for missing, unknown, or unrecorded data. Understanding what a NULL value in SQL is and how it differs from a blank space or zero is essential for writing accurate queries and maintaining data integrity.
In database management, learning how to handle missing values in SQL using proper operators, functions, and aggregate methods ensures your calculations, reporting, and data analyses remain precise and reliable.
What are null values in SQL?
In SQL, a NULL value represents missing, unknown, or inapplicable data in a database table. It is not the same as a zero, a blank space, or an empty text string, but rather a placeholder that says “no value has been recorded here.”
For example, if a customer signs up for an online account but skips entering their phone number, SQL stores that field as NULL to indicate that the information is currently missing.
Because NULL signifies an unknown state, any mathematical calculation or logical comparison made with a NULL value, such as asking if it is equal to another value, will also result in an unknown answer, requiring special SQL commands like IS NULL or IS NOT NULL to find and handle these missing entries properly.
What is the use of null values in database?
The main use of NULL values in a database is to give you a clean, accurate way to handle missing, unknown, or optional information without putting in false data. Instead of forcing a user to enter a fake value, like typing 0 for an unknown age or writing “N/A” for a missing phone number, a database uses NULL to explicitly state, “this data was not provided.”
This keeps your calculations correct, because tools like average or total calculations will automatically skip NULL entries rather than accidentally skewing your results with zeros. Ultimately, NULL values preserve data integrity by clearly separating a true zero or empty response from a value that simply hasn’t been recorded yet.
How to handle null values in SQL queries?
Handling NULL values in SQL queries requires specific functions and logical operators because normal comparison operators (like = or <>) do not work on unknown data.
Here are the main ways to handle NULL values in SQL. For examples, we are using the University database schema and tables:
Checking for NULL Values (IS NULL / IS NOT NULL)
Since NULL = NULL evaluates to unknown, SQL provides IS NULL to find missing values and IS NOT NULL to find non-missing values.
Finding NULL values (IS NULL): Find departments that currently do not have a department chair assigned.
SELECT Dept_ID, Dept_Name
FROM Department
WHERE Chair_Prof_ID IS NULL;
Filtering out NULL values (IS NOT NULL): Find departments that do have a chair assigned.
SELECT Dept_ID, Dept_Name, Chair_Prof_ID
FROM Department
WHERE Chair_Prof_ID IS NOT NULL;
Replacing NULL Values (COALESCE or IFNULL / ISNULL)
The COALESCE function accepts a list of values and returns the first non-NULL value. This is useful for displaying default placeholder text when a field is blank.
Example: Display department names along with their chair’s professor ID, displaying ‘No Chair Assigned’ if the ID is missing.
SELECT Dept_Name,
COALESCE(CAST(Chair_Prof_ID AS VARCHAR), 'No Chair Assigned') AS Chair_Status
FROM Department;
Handling NULL Values in Conditional Logic (CASE Statement)
You can use a CASE statement combined with IS NULL to custom-label missing information dynamically.
Example: Categorize departments based on leadership status.
SELECT Dept_Name,
CASE
WHEN Chair_Prof_ID IS NULL THEN 'Vacant Leadership'
ELSE 'Has Chair'
END AS Leadership_Status
FROM Department;
Handling NULL Values in Joins (LEFT JOIN)
When joining tables, using a LEFT JOIN keeps records from the left table even if there is no match in the right table, filling unmatched right-table columns with NULL. Combining this with WHERE … IS NULL allows you to find unlinked records.
Example: Find departments that do not have any phone numbers listed in the Department_Phone table.
SELECT d.Dept_ID, d.Dept_Name
FROM Department d
LEFT JOIN Department_Phone dp ON d.Dept_ID = dp.Dept_ID
WHERE dp.Phone_Number IS NULL;
Handling NULL Values in Aggregate Functions
Aggregate functions (like COUNT, AVG, SUM) automatically skip NULL values. If you want to count total rows regardless of NULLs, use COUNT(*). If you only want to count non-NULL entries in a column, use COUNT(Column_Name).
Example: Count total departments vs. departments with assigned chairs.
SELECT COUNT(*) AS Total_Departments,
COUNT(Chair_Prof_ID) AS Departments_With_Chairs
FROM Department;
Null values and aggregate operators
Aggregate functions (like COUNT, SUM, AVG, MIN, and MAX) perform calculations across multiple rows of data. By default, SQL aggregate functions completely ignore NULL values when carrying out their computations, with COUNT(*) being the only primary exception.
Here is how NULL values interact with each major aggregate operator, along with examples using the University database:
COUNT()
The COUNT() function behaves differently depending on whether you pass an asterisk or a specific column name.
- COUNT(*): Counts all rows in the table, regardless of whether columns contain NULL values.
- COUNT(Column_Name): Counts only rows where the specified column is NOT NULL.
Example: Calculate total departments versus departments with an assigned department chair from the Department table.
SELECT COUNT(*) AS Total_Departments,
COUNT(Chair_Prof_ID) AS Departments_With_Chairs
FROM Department;
Result: Total_Departments is 6 (all rows), while Departments_With_Chairs is 5 because Civil Engineering has a NULL Chair_Prof_ID and is ignored.
AVG()
The AVG() function calculates the mean of non-NULL numeric values. It automatically excludes NULL entries from both the total sum and the division count.
Example: Find the average age of dependents listed in the Dependent table.
SELECT AVG(Age) AS Average_Dependent_Age
FROM Dependent;
Result: It sums up all valid ages (10 + 8 + 12 + 5 + 15 + 17 = 67) and divides by 6 (the number of non-NULL age records). If any dependent had a NULL age, SQL would exclude that row from both the sum and the denominator count.
SUM()
The SUM() function adds up all non-NULL numbers in a column. If a column contains only NULL values, SUM() returns NULL rather than 0.
Example: Sum the ages of all dependents for Professor ID 1.
SELECT SUM(Age) AS Total_Dependent_Age
FROM Dependent
WHERE Prof_ID = 1;
Result: Adds Emma’s age (10) and Liam’s age (8) to return 18. Any missing/NULL values are simply skipped during addition.
MIN() and MAX()
The MIN() and MAX() functions find the smallest and largest values in a column, respectively. They ignore all NULL entries during evaluation.
Example: Find the youngest and oldest dependent ages in the Dependent table.
SELECT MIN(Age) AS Youngest_Dependent,
MAX(Age) AS Oldest_Dependent
FROM Dependent;
Result: Youngest_Dependent returns 5 and Oldest_Dependent returns 17.
Important Tip: Replacing NULL in Aggregations
If you want NULL values treated as 0 instead of being ignored during calculations, wrap the column with COALESCE().
Example:
SELECT AVG(COALESCE(Chair_Prof_ID, 0)) AS Avg_Chair_ID_Include_Nulls
FROM Department;
This forces the NULL value in the Department table to count as 0 in the average calculation.
Mr. P.S.Suryateja, also known as Suryateja Pericherla, is at present a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.
He has 14+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.
He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.
Leave a Reply