SQL Server ISNULL() Function

SQL Server ISNULL() function replaces the NULL values present in the table with specified values passed as the argument. The ISNULL in SQL takes two arguments, and if the first argument is found NULL at an instance, it returns the second argument.

The following is the syntax of the ISNULL in SQL:

The ISNULL in SQL accepts the following two as arguments:

• expr: The expr refers to the expression the ISNULL checks for NULL.
• exprReplacement: The exprReplacement refers to the replacement value that has to be returned if the provided expr has the value NULL.

SQL Server ISNULL() function examples

Using SQL Server ISNULL() function with the numeric value

The SQL Server ISNULL() function with a numeric value is used for handling NULL values within numeric data types.

Output:

Explanation:

• In this example, ISNULL() function is used to handle a NULL value.
• If the first argument is NULL, it returns the second argument. Otherwise, it returns the first argument.
• Here, the first argument is NULL, so ISNULL(NULL, 10) returns 10.

Using SQL Server ISNULL() function with character string data

• The SQL Server 'ISNULL()' method with character string data allows for seamless handling of NULL values within string fields.
• It enables developers to replace NULL occurrences with defined string alternatives,
• This method also ensures data consistency and improves query returns readability.

Query

Output:

Explanation:

• Similar to the previous example, but with character string data.
• If the first argument is NULL, it returns the second argument. Otherwise, it returns the first argument.
• Here, the first argument is NULL, so ISNULL(NULL, 'Hello') returns 'Hello'.

Using SQL Server ISNULL() function with variables

Using the SQL Server ISNULL() function with variables allows developers to easily manage NULL values within variable assignments

Output:

Explanation:

• Demonstrates the use of ISNULL() with variables.
• The value of @value is set to NULL.
• ISNULL(@value, 5) checks if @value is NULL. Since it is, it returns 5.

Using SQL Server ISNULL() function on Table

This feature is especially useful when displaying data to end users or performing calculations when NULL values could disrupt the desired results. Certainly! Here's an example demonstrating the usage of the SQL Server ISNULL() function on a table:

Consider a table named EmployeeDetails with columns EmployeeID and Department, where some records may have NULL values in the Department column. We can use the ISNULL() function to replace these NULL values with a default department name, such as 'Unknown'.

Output:

Explanation:

• In this example, the EmployeeDetails table is created with columns EmployeeID and Department.
• Sample data is inserted into the table, including some records with NULL values in the Department column.
• The SELECT query retrieves data from the EmployeeDetails table, replacing NULL values in the Department column with the string 'Unknown' using the ISNULL() function.
• As a result, the output displays all employee IDs along with their respective departments, where NULL values are replaced by 'Unknown', ensuring consistent data presentation.

Using SQL Server ISNULL() function with aggregate functions

• Using 'ISNULL()' within aggregate functions like SUM(), COUNT(), or AVG() allows developers to assure consistent calculations even when NULL data are present.

• This method speeds data analysis procedures and delivers precise insights into aggregated data sets, enhancing decision-making capabilities.

• Using ISNULL() with aggregate functions promotes strong data handling standards, which improves the overall integrity and dependability of SQL Server database operations.

  Certainly! Here's an example demonstrating the usage of the SQL Server ISNULL() function with aggregate functions:

Consider a table named StudentData with columns SerialID and Fees, where some records may have NULL values in the Fees column. We want to calculate the total fee amount, replacing any NULL values with 0 using the ISNULL() function.

Output:

Explanation:

• In this example, the StudentData table is created with columns SerialID and Fees.
• Sample data is inserted into the table, including some records with NULL values in the Fees column.
• The SELECT query retrieves the total fees from the StudentData table using the SUM() aggregate function.
• The ISNULL() function is applied to replace NULL values in the Fees column with 0.
• As a result, the output displays the total fees amount, ensuring that NULL values are handled appropriately and do not affect the aggregate calculation.

Learn More about NULL Value in SQL

To learn more about NULL value in SQL, please visit this link.