SQL Server ISNULL() Function

The ISNULL() function in SQL Server is a powerful tool for handling NULL values in our database queries. It allows us to replace NULL values with a specified replacement value, ensuring that your queries return meaningful results even when data is missing.

In this article, We will learn about the SQL Server ISNULL() Function by understanding various examples with output and explanation.

SQL Server ISNULL() Function

• The ISNULL() function in SQL Server is used to replace NULL values with a specified replacement value.
  
• It takes two arguments which are an expression to evaluate and the replacement value if the expression is NULL.

Syntax

SQL Server ISNULL() function syntax is:

ISNULL(expression, value)

Parameter :

This method accepts two parameters.

• expression: The specified expression is to be checked to see if it's NULL or not.
• value: The specified value to be returned, in case the expression is NULL.

Example of SQL Server ISNULL() Function

Let's look at some examples of the ISNULL function in SQL Server. Learning the ISNULL function with examples will help in understanding the concept better.

Example 1

Suppose we want to check if a specific string value is NULL and, if so then replace it with a default value.

SELECT ISNULL('gfg', 'Geeks');

Output:

gfg

Explanation: In the query SELECT ISNULL('gfg', 'Geeks');, since the first argument 'gfg' is not NULL, the function returns 'gfg' without using the replacement value 'Geeks'.

Example 2

Write a query to replace a NULL value with a specified string in SQL Server.

SELECT ISNULL(NULL, 'Geeks');

Output :

Geeks

Explanation: The query replaces the NULL value with the string 'Geeks', so the output will be 'Geeks'.

Example 3

Using ISNULL() function and getting the output using a variable.

DECLARE @exp VARCHAR(50);
SET @exp = 'geeksforgeeks';
SELECT ISNULL(@exp, 150);

Output :

geeksforgeeks

Example 4

Suppose we need to check if a variable containing a string value is NULL and return a specific value if it is.

DECLARE @exp VARCHAR(50);
DECLARE @val VARCHAR(50);
SET @exp = NULL;
SET @val = 'GFG';
SELECT ISNULL(@exp, @val);

Output:

GFG

Explanation:

The query declares a variable @exp with the value 'geeksforgeeks' and uses the ISNULL() function to check if @exp is NULL. Since @exp is not NULL, the original value 'geeksforgeeks' is returned, and the replacement value 150 is ignored.

Important Points About SQL Server ISNULL Function

• The ISNULL() function is used to return a specified value if the expression is NULL, otherwise it returns the expression itself.
• It is used to replace NULL values in expressions or table columns with a meaningful value.
• It is different from the IS NULL operator, which is used to check if a value is NULL.
• The ISNULL() function is useful for handling NULL values in queries and providing alternative values when NULL is encountered.

Conclusion

In conclusion, the ISNULL() function is an essential feature in SQL Server for dealing with NULL values. By providing a way to replace NULL with a predefined value, it helps maintain data integrity and ensures that your queries produce consistent and accurate results. Whether you're working with variables or table columns, mastering the ISNULL() function will enhance your ability to handle NULL values effectively in your SQL Server queries.

nidhi1352singh

Follow

Improve

Article Tags :

• SQL
• Databases
• DBMS-SQL
• SQL-Server