2023年7月13日发(作者：)

sqlisnull函数_SQLISNULL函数sql isnull函数This article explores the SQL ISNULL function to replace NULL values in expressions or table records with examples.本⽂探讨了⽤⽰例替换表达式或表记录中的NULL值SQL ISNULL函数。介绍 (Introduction)We define the following parameters while designing a table in SQL Server我们在SQL Server中设计表时定义以下参数Data types for a particular column特定列的数据类型Allow NULL or Not Null values in SQL Server在SQL Server中允许NULL或Not Null值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.如果我们没有为列允许NULL值提供任何值，则SQL Server会将NULL假定为默认值。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.让我们在Employee表中插⼊⼀些记录。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 forthis column.查看表中的记录，由于我们没有为该列插⼊任何值，因此我们可以看到EmployeeID 2为空值。We might have a requirement to replace NULL values with a particular value while viewing the records. We do not want toupdate values in the table. We can do this using SQL ISNULL Function. Let’s explore this in the upcoming section.在查看记录时，我们可能需要⽤特定值替换NULL值。 我们不想更新表中的值。 我们可以使⽤SQL ISNULL Function来做到这⼀点。 让我们在接下来的部分中对此进⾏探讨。SQL Server ISNULL函数概述 (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 ISNULLfunction is as follow.我们可以使⽤SQL Server ISNULL函数将NULL值替换为特定值。 SQL ISNULL函数的语法如下。SQL Server ISNULL (expression, replacement)SQL Server ISNULL（表达式，替换）Expression: In this parameter, we specify the expression in which we need to check NULL values 表达式：在此参数中，我们指定需要检查NULL值的表达式Replacement: We want to replace the NULL with a specific value. We need to specify replacement value here 替换：我们要⽤特定值替换NULL。 我们需要在此处指定替换值The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL. SQLServer converts the data type of replacement to data type of expression. Let’s explore SQL ISNULL with examples.如果第⼀个参数表达式的计算结果为NULL，则SQL Server ISNULL函数将返回替换值。 SQL Server将替换的数据类型转换为表达式的数据类型。 让我们⽤⽰例探索SQL ISNULL。⽰例1：参数中SQL Server ISNULL函数 (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:在此⽰例中，SQL ISNULL函数返回第⼆个参数值，因为第⼀个参数为NULL：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.如果第⼀个参数为NULL，则返回第⼆个参数的值。If the first argument is NOT NULL, it returns the first argument value as output.如果第⼀个参数为NOT NULL，它将返回第⼀个参数值作为输出。 SELECT ISNULL(NULL, 'SQLServer') result; SELECT ISNULL('SQLShack', 'SQLServer') result;⽰例2：SQL Server ISNULL替换现有列值中的值 (Example 2: SQL Server ISNULL to replace a value in existingcolumn values)At the beginning of this article, we created the Employee table and inserted NULL values in it. We can use SQL ISNULL toreplace existing NULL values with a specific value.在本⽂的开头，我们创建了Employee表并在其中插⼊了NULL值。 我们可以使⽤SQL ISNULL将现有的NULL值替换为特定值。For example, we want to return Employee salary 10,000 if it is NULL in the Employee table. In the following query, we usedSQL ISNULL function to replace the value.例如，如果Employee表中的NULL为NULL，我们想返回10,000。 在下⾯的查询中，我们使⽤SQL ISNULL函数替换该值。 SELECT Employeeid,

ISNULL(EmployeeSalary, 10000) EmployeeSalary FROM Employee;Let’s update the NULL value in the Employee table using the following update statement.让我们使⽤以下更新语句更新Employee表中的NULL值。Update Employee set EmployeeSalary =65656 where EmployeeID =2We do not have any NULL value in the table now; therefore if we run the query with SQL Server ISNULL, it returns actualvalues of the rows.现在表中没有任何NULL值； 因此，如果我们使⽤SQL Server ISNULL运⾏查询，它将返回⾏的实际值。⽰例3：具有聚合函数SQL Server ISNULL (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 ofEmployeeSalary present in Employee table. If EmployeeSalary is NULL, it should be replaced with 10000 before adding thesalaries.我们也可以将SQL ISNULL与SUM，AVG等聚合函数⼀起使⽤。 假设我们要执⾏Employee表中存在的EmployeeSalary的总和。 如果EmployeeSalary为NULL，则在添加薪⽔之前应将其替换为10000。Before we move, update the EmployeeSalary as NULL for EmployeeID 2 using the following query.在我们移动之前，使⽤以下查询将EmployeeID 2的EmployeeSalary更新为NULL。Update Employee set EmployeeSalary =NULL where EmployeeID =2In the following query, we replaced the NULL value with value 10000 first and then performed SUM on it. You can visualizeit with the following screenshot.在以下查询中，我们⾸先将NULL值替换为10000，然后对其执⾏SUM。 您可以使⽤以下屏幕截图对其进⾏可视化。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.同样，我们可以使⽤SQL ISNULL函数替换NULL值，并使⽤AVG（）函数计算平均值。SELECT AVG(ISNULL(EmployeeSalary, 10000))FROM Employee;⽰例4：SQL Server ISNULL与IS NULL之间的区别 (Example 4: Difference between SQL Server ISNULL with ISNULL )You might confuse between SQL Server ISNULL and IS NULL. We use IS NULL to identify NULL values in a table.您可能会在SQL Server ISNULL和IS NULL之间感到困惑。 我们使⽤IS NULL来识别表中的NULL值。For example, if we want to identify records in the employee table with NULL values in the Salary column, we can use IS NULLin where clause.例如，如果我们想⽤Salary列中的NULL值来标识employee表中的记录，则可以在where⼦句中使⽤IS NULL 。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 aspecific value. In the second part, we can see the employee having a NULL salary.在下⾯的屏幕快照中，我们⽆法使⽤SQL Server ISNULL查找NULL值。 我们使⽤它⽤特定值替换NULL值。 在第⼆部分中，我们可以看到该雇员的⼯资为NULL。⽰例5：SQL Server ISNULL以⾃定义消息替换NULL值 (Example 5: SQL Server ISNULL to replace the NULL valuewith 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.考虑以下⽰例，其中在DeliveryAddressLine2列中具有NULL值。 如果DeliveryAddressLine2中的实际值具有NULL值，我们想在[DeliveryAddressLine2]中打印⼀条消息。Select[CustomerName],

[AccountOpenedDate],

[DeliveryAddressLine1],

[DeliveryAddressLine2],

ISNULL([DeliveryAddressLine2], 'Address Same as DeliveryAddressLine1') AS DeliveryAddressLine2, [DeliveryPostalCode] from customers

You can see the message in DeliveryAddressLine2 column.您可以在DeliveryAddressLine2列中看到该消息。SQL Server ISNULL中数据类型的隐式转换 (Implicit conversion of data type in SQL ServerISNULL)If we have multiple data types in an expression, SQL Server converts the lower precedence data types into a higherprecedence data type. SQL Server ISNULL also performs similarly. If SQL ISNULL is not able to escalate the data type, itreturns an error message.如果表达式中有多个数据类型，则SQL Server会将较低优先级的数据类型转换为较⾼优先级的数据类型。 SQL Server ISNULL也执⾏类似的操作。 如果SQL ISNULL⽆法升级数据类型，它将返回错误消息。As per , SQL Server uses the following precedence order for data types:根据 ，SQL Server对数据类型使⽤以下优先级顺序：1. user-defined data types (highest)⽤户定义的数据类型（最⾼）2. sql_variantXMLsql_variantXML3. datetimeoffset⽇期时间偏移4. datetime2datetime25. DateTime约会时间6. smalldatetime⼩⽇期时间7. date⽇期8. time时间9. float浮动10. real真实11. decimal⼩数12. money钱13. smallmoney⼩钱14. bigint⽐⾦特15. int整型16. smallintSmallint17. tinyinttinyint18. bit⼀点19. ntext⽂字20. text⽂本21. image图⽚22. timestamp时间戳记23. uniqueidentifier唯⼀标识符24. nvarchar (including nvarchar(max) )nvarchar（包括nvarchar（max））25. ncharnchar26. varchar (including varchar(max) )varchar（包括varchar（max））27. char烧焦28. varbinary (including varbinary(max) )varbinary（包括varbinary（max））29. binary (lowest)⼆进制（最低）In the following image, we can all possible implicit and explicit allowed data type conversions. ( Image Reference: )在下图中，我们可以进⾏所有可能的隐式和显式允许的数据类型转换。 （图⽚参考： ）Let’s understand data type precedence with SQL NULL using the following example. In the above table, we can see that theprecedence of INT is higher than the timestamp data type.让我们使⽤以下⽰例了解SQL NULL的数据类型优先级。 在上表中，我们可以看到INT的优先级⾼于时间戳数据类型。Execute the following query. In this query, we defined a variable @ID of integer type. We try to replace the NULL value in thisparameter with Current timestamp.执⾏以下查询。 在此查询中，我们定义了整数类型的变量@ID。 我们尝试⽤当前时间戳替换此参数中的NULL值。DECLARE @ID intWe get the following error message. It cannot convert data type from datetime to integer. We can use SQL Convertfunctions to convert appropriate data type.我们收到以下错误消息。 它不能将数据类型从⽇期时间转换为整数。 我们可以使⽤SQL Convert函数来转换适当的数据类型。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.消息257，第16级，州3，第2⾏

不允许从数据类型datetime到int的隐式转换。 使⽤CONVERT函数运⾏此查询。Now, let’s replace data type of parameter @ID from integer to datatime2. In the data precedence table, the precedence ofdatatime2 data type is higher than the timestamp data type.现在，让我们将参数@ID的数据类型从整数替换为datatime2。 在数据优先级表中，datatime2数据类型的优先级⾼于时间戳数据类型。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 free to ask question or provide comments in the feedback below在本⽂中，我们探讨了SQL ISNULL函数及其在⽤指定值或字符串替换NULL值中的⽤法。 欢迎在下⾯的反馈中提问或发表评论翻译⾃:sql isnull函数