SQL ISNULL function
SQL ISNULL function
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. Now, let’s replace data type of parameter @ID from integer to datatime2. In the data precedence table, the precedence of datatime2 data type is higher than the timestamp data type. 12 DECLARE @ID DateTime2SELECT ISNULL(@ID, CURRENT_TIMESTAMP);
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolSQL ISNULL function
May 10, 2019 by Rajendra Gupta This article explores the SQL ISNULL function to replace NULL values in expressions or table records with examples.Introduction
We define the following parameters while designing a table in SQL Server Data types for a particular column Allow NULL or Not Null values in SQL Server 123456 CREATE TABLE table_name( column1 datatype [ NULL], column2 datatype [NOT NULL ], ...); If we do not provide any value for column allow NULL values, SQL Server assumes NULL as default value. 12345 CREATE TABLE Employee(EmployeeID INT IDENTITY(1, 1) NOT NULL, EmployeeName VARCHAR(50) NOT NULL, EmployeeSalary INT NULL); Let’s insert a few records in the Employee table. 12345678910 INSERT INTO Employee(EmployeeName, EmployeeSalary)VALUES('Rajendra', 55645);INSERT INTO Employee(EmployeeName)VALUES('Rajendra'); View the records in the table, and we can see a NULL value against EmployeeID 2 because we did not insert any value for this column. We might have a requirement to replace NULL values with a particular value while viewing the records. We do not want to update values in the table. We can do this using SQL ISNULL Function. Let’s explore this in the upcoming section.SQL Server ISNULL Function overview
We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow. SQL Server ISNULL (expression, replacement) Expression: In this parameter, we specify the expression in which we need to check NULL values Replacement: We want to replace the NULL with a specific value. We need to specify replacement value here The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL. SQL Server converts the data type of replacement to data type of expression. Let’s explore SQL ISNULL with examples.Example 1 SQL Server ISNULL function in an argument
In this example, SQL ISNULL function returns the second argument value because the first argument is NULL: 1 SELECT ISNULL(NULL, 100) result; In the following examples, we can see the following. If the first argument is NULL, it returns the value of the second argument. If the first argument is NOT NULL, it returns the first argument value as output. 12 SELECT ISNULL(NULL, 'SQLServer') result; SELECT ISNULL('SQLShack', 'SQLServer') result;Example 2 SQL Server ISNULL to replace a value in existing column values
At the beginning of this article, we created the Employee table and inserted NULL values in it. We can use SQL ISNULL to replace existing NULL values with a specific value. For example, we want to return Employee salary 10,000 if it is NULL in the Employee table. In the following query, we used SQL ISNULL function to replace the value. 123 SELECT Employeeid, ISNULL(EmployeeSalary, 10000) EmployeeSalary FROM Employee; Let’s update the NULL value in the Employee table using the following update statement. 1 Update Employee set EmployeeSalary =65656 where EmployeeID =2 We do not have any NULL value in the table now; therefore if we run the query with SQL Server ISNULL, it returns actual values of the rows.Example 3 SQL Server ISNULL with aggregate functions
We can use SQL ISNULL with aggregate functions such as SUM, AVG as well. Suppose we want to perform sum of EmployeeSalary present in Employee table. If EmployeeSalary is NULL, it should be replaced with 10000 before adding the salaries. Before we move, update the EmployeeSalary as NULL for EmployeeID 2 using the following query. 1 Update Employee set EmployeeSalary =NULL where EmployeeID =2 In the following query, we replaced the NULL value with value 10000 first and then performed SUM on it. You can visualize it with the following screenshot. 12 SELECT SUM(ISNULL(EmployeeSalary, 10000))FROM Employee; Similarly, we can use SQL ISNULL function to replace NULL values and calculate the average value with AVG() function. 12 SELECT AVG(ISNULL(EmployeeSalary, 10000))FROM Employee;Example 4 Difference between SQL Server ISNULL with IS NULL
You might confuse between SQL Server ISNULL and IS NULL. We use IS NULL to identify NULL values in a table. For example, if we want to identify records in the employee table with NULL values in the Salary column, we can use IS NULL in where clause. 123 SELECT *FROM EmployeeWHERE EmployeeSalary IS NULL; In the following screenshot, we cannot use SQL Server ISNULL to find NULL values. We use it to replace NULL values with a specific value. In the second part, we can see the employee having a NULL salary.Example 5 SQL Server ISNULL to replace the NULL value with a custom message
Consider the example in which we have NULL values in DeliveryAddressLine2 column. We want to print a message in [DeliveryAddressLine2] if the actual values from DeliveryAddressLine2 have NULL values. 12345678 Select[CustomerName], [AccountOpenedDate], [DeliveryAddressLine1], [DeliveryAddressLine2], ISNULL([DeliveryAddressLine2], 'Address Same as DeliveryAddressLine1') AS DeliveryAddressLine2, [DeliveryPostalCode] from customers You can see the message in DeliveryAddressLine2 column.Implicit conversion of data type in SQL Server ISNULL
If we have multiple data types in an expression, SQL Server converts the lower precedence data types into a higher precedence data type. SQL Server ISNULL also performs similarly. If SQL ISNULL is not able to escalate the data type, it returns an error message. As per MSDN, SQL Server uses the following precedence order for data types: user-defined data types (highest) sql_variantXML datetimeoffset datetime2 DateTime smalldatetime date time float real decimal money smallmoney bigint int smallint tinyint bit ntext text image timestamp uniqueidentifier nvarchar (including nvarchar(max) ) nchar varchar (including varchar(max) ) char varbinary (including varbinary(max) ) binary (lowest) In the following image, we can all possible implicit and explicit allowed data type conversions. ( Image Reference: Microsoft Docs) Let’s understand data type precedence with SQL NULL using the following example. In the above table, we can see that the precedence of INT is higher than the timestamp data type. Execute the following query. In this query, we defined a variable @ID of integer type. We try to replace the NULL value in this parameter with Current timestamp. 1 DECLARE @ID int We get the following error message. It cannot convert data type from datetime to integer. We can use SQL Convert functions to convert appropriate data type. Msg 257, Level 16, State 3, Line 2Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. Now, let’s replace data type of parameter @ID from integer to datatime2. In the data precedence table, the precedence of datatime2 data type is higher than the timestamp data type. 12 DECLARE @ID DateTime2SELECT ISNULL(@ID, CURRENT_TIMESTAMP);
Conclusion
In this article, we explored the SQL ISNULL function and its usage in replacing NULL values with a specified value or string. Feel free to ask question or provide comments in the feedback below Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022