Mastering NULL Value in SQL Server
Contents
- Introduction
- What is SQL NULL ?
- Table used in this article
- Functions to handle SQL NULL Value
- IsNull()
- Coalesce()
- Aggregating Data with NULL Value
- Handling NULL values in the report
- Examples on SQL NULL value
- Best Practices for handling NULL value
- Summary
Introduction
NULL in SQL is considered as the missing or unknown value which is not equal to 0 or space (”). It makes handling SQL NULL little complicated if the developer has not good knowledge of NULL concepts.
This article is going to explain some key concepts and best practices related to NULL values in SQL Server. This article also contains examples which will help in mastering the NULL value in SQL Server.
What is SQL NULL?
In SQL, NULL is defined as unknown value or yet to be define value. It’s important to handle NULL values appropriately to ensure data integrity and avoid unexpected results in queries. For example, if a user has appeared for 5 exams and he got result for 4 exams, but last exam result is still awaited. So, we can’t place 0 value in last exam column. In this case we put NULL value in the last column and later replace it with the actual marks.
Also, since NULL value is considered as unknown, we can’t compare one NULL value with another NULL value.
Checking for NULL Values
Use the IS NULL and IS NOT NULL operators to check for NULL values. Also, comparisons involving NULL values yield UNKNOWN result.
SELECT * FROM Employees WHERE ProjectID = NULL; -- Incorrect
SELECT * FROM Employees WHERE ProjectID IS NULL; -- Correct
Similarly,
SELECT * FROM Employees WHERE ProjectID <> NULL; -- Incorrect
SELECT * FROM Employees WHERE ProjectID IS NOT NULL; -- Correct
Table used in this article: –
In this article, we are going to use the table tbl_nulldemo containing the following records
col_id
5
5
5
5
5
NULL
Functions to handle SQL NULL
There are 2 function which we can use to handle SQL NULL values
1) ISNull()
ISNULL() function returns the specified value in case of the expression return NULL value else the expression value is returned.
For example:-
Select ISNULL( col_id ,0) FROM tbl_nulldemo
Above SQL query will gives the below output.
col_id
5
5
5
5
5
0
It will replace the NULL value with 0 value.
2) COALESCE()
COALESCE() function will return the first non-NULL expression in the list.
For example,
SELECT COALESCE(NULL, NULL, 5, NULL, 6)
will return the output as 5 as it is the 1st Not Null value.
Aggregating Data with NULL Value
Aggregate functions like COUNT, SUM, AVG, MAX, and MIN handle NULL values differently.
- COUNT (*) includes NULL values, while COUNT(column_name) does not.
- SUM, AVG, MAX, and MIN ignore NULL values.
Handling NULL values in the report.
Suppose we are creating a report that lists employees and their Projects, but some employees do not have a project. You can handle NULL values to ensure the report displays “No Project” for such employees.
SELECT EmployeeID, COALESCE (ProjectName, 'No Project') AS ProjectName FROM Employees e
In this example, the COALESCE function is used to handle NULL values by displaying “No Project” when an employee does not have a Project. This ensures the report is clear and user-friendly.
Examples on SQL NULL value
In the first section, I explain the NULL value in SQL Server. Now in this section, I will try to explain it with the help of examples.
In all the below question, I will use table tbl_nulldemo which I mentioned under the section table used. All the examples are going to use this same table tbl_nulldemo data.
Example 1: – What will be the outcome of the following queries?
SELECT COUNT (1) FROM tbl_nulldemo
Ans- 6 Explanation- Even NULL is considered as the unknown value, any row containing the NULL value is considered by the Count() function. So, the above query returns 6
Example 2: –
SELECT COUNT (*) FROM tbl_nulldemo
Ans – 6
Explanation- Even NULL is considered as the unknown value, any row containing the NULL value is considered by the Count() function. So, the above query returns 6
Example 3: –
SELECT COUNT (col_id) FROM tbl_nulldemo
Ans – 5
Explanation- When we pass the column name in the count function, it will consider only not null values. So, in this case, it will return the outcome as 5
Example 4: –
SELECT AVG (col_id) FROM tbl_nulldemo
Ans- 5
Explanation – AVG function will take all non-NULL values while calculating the AVG value. So, in this case, we have 5 not null values. 5,5,5,5,5. So AVG function will return 25/5=5 value
Example 5: –
SELECT col_id, COUNT (col_id) as cnt FROM tbl_nulldemo GROUP BY col_id HAVING (COUNT(col_id)>1)
Ans- This query will give the below outcome
col_id cnt
————————-
5 5
Explanation- Since the table contains value 5 (5 times), so we got the above outcome. Now one question arises What will happened if we have one more NULL value? Even then, the outcome will remain same because NULL is an unknown value so, even we have more than 1 NULL value in the column, these NULL values can’t be compared. So, each NULL value will be treated separately.
Example 7: –
SELECT DISTINCT col_id FROM tbl_nulldemo
Ans-
col_id
———–
NULL
Explanation- Here since the table contains value 5, 5 times and 1 NULL value, so the distinct command will return the above outcome.
Example 8: –
SELECT MIN(col_id) as min, MAX(col_id) as max FROM tbl_nulldemo
Ans-
min max
———– ———–
5 5
Explanation- Since Null value is unknown so can’t be compared. Therefore Min & Max function do not consider the Null value. So, since we have only 5 value is left in the table apart from Null value, so Min & Max function returns 5 as outcome.
Example 9: –
SELECT SUM(col_id) as Total FROM tbl_nulldemo
Ans-
Total
———–
25
Explanation- Like the above SQL query explanation, Sum value doesn’t consider the NULL value, so the SUM () function will return 25 as outcome.
Example 10: –
SELECT 5+5+5+5+5+NULL as totalvalue
Ans-
totalvalue
———–
NULL
Explanation- Here we are trying to add the 5 with Null, an unknown value. So, the outcome will be NULL, an unknown value.
Example 11: –
SELECT * FROM tbl_nulldemo WHERE col_id != NULL
Ans-
col_id
———–
Explanation-Since it is not possible to compare values of a column with NULL (an unknown value). So we can’t use the comparison operators such as =, <, or <>, with NULL value. So, if you want to get output which do not contains NULL value, we must use is NOT NULL command as shown below
SELECT * FROM tbl_nulldemo WHERE col_id is not NULL
col_id
———–
5
5
5
5
5
Example 12: –
SELECT * FROM tbl_nulldemo WHERE col_id = NULL
Ans-
col_id
———–
Explanation-Similar to the explanation given for the 10th example, since it is not possible to compare values of a column with NULL (an unknown value). So, we can’t use the comparison operators such as =, <, or <>, with NULL value. So, if you want to get output which contains NULL value, we must use is NULL command as shown below
SELECT * FROM tbl_nulldemo WHERE col_id is NULL
col_id
———–
NULL
Best Practices for handling NULL value
- Explicitly Handle NULL Values: Always consider how your application should handle NULL values and make it explicit in your SQL queries.
- Avoid NULL in Key Columns: Avoid using NULL values in primary keys, foreign keys, and unique constraints, as they can lead to data integrity issues.
- Document NULL Handling: Document how NULL values are handled in your database schema and application logic to ensure consistency.
- Use Default Values: Where appropriate, use default values to avoid NULL entries in the database.
Summary: –
This article explains key concept of NULL value in the SQL Server. It’s also mentioned the SQL function which are used for NULL value and how to compare it. It also explains how the aggregating SQL functions handle the NULL value with the help of examples.
NULL value also affect the outcome of the SQL JOIN queries. For details please refer to this SQL JOIN article
In the end, it also mentioned best practices related to handling of NULL values in SQL Server.
The post Mastering NULL value in SQL Server appeared first on Technology with Vivek Johari.
