sql isnull函数_SQL ISNULL函数

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 for this column.

查看表中的记录，由于我们没有为该列插入任何值，因此我们可以看到EmployeeID 2为空值。

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.

在查看记录时，我们可能需要用特定值替换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 ISNULL function 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. SQL Server 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 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.

在本文的开头，我们创建了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 used SQL 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 =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.

现在表中没有任何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 of EmployeeSalary present in Employee table. If EmployeeSalary is NULL, it should be replaced with 10000 before adding the salaries.

我们也可以将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 =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.

在以下查询中，我们首先将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 IS NULL )

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 NULL in where clause.

例如，如果我们想用Salary列中的NULL值来标识employee表中的记录，则可以在where子句中使用IS NULL 。

SELECT *
FROM Employee
WHERE 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.

在下面的屏幕快照中，我们无法使用SQL Server ISNULL查找NULL值。 我们使用它用特定值替换NULL值。 在第二部分中，我们可以看到该雇员的工资为NULL。

示例5：SQL Server ISNULL以自定义消息替换NULL值 (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.

考虑以下示例，其中在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 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.

如果表达式中有多个数据类型，则SQL Server会将较低优先级的数据类型转换为较高优先级的数据类型。 SQL Server ISNULL也执行类似的操作。 如果SQL ISNULL无法升级数据类型，它将返回错误消息。

As per MSDN, SQL Server uses the following precedence order for data types:

根据MSDN ，SQL Server对数据类型使用以下优先级顺序：

1. user-defined data types (highest)
   用户定义的数据类型（最高）
2. sql_variantXML
   sql_variantXML
3. datetimeoffset
   日期时间偏移
4. datetime2
   datetime2
5. DateTime
   约会时间
6. smalldatetime
   小日期时间
7. date
   日期
8. time
   时间
9. float
   浮动
10. real
    真实
11. decimal
    小数
12. money
    钱
13. smallmoney
    小钱
14. bigint
    比金特
15. int
    整型
16. smallint
    Smallint
17. tinyint
    tinyint
18. bit
    一点
19. ntext
    文字
20. text
    文本
21. image
    图片
22. timestamp
    时间戳记
23. uniqueidentifier
    唯一标识符
24. nvarchar (including nvarchar(max) )
    nvarchar（包括nvarchar（max））
25. nchar
    nchar
26. 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: Microsoft Docs)

在下图中，我们可以进行所有可能的隐式和显式允许的数据类型转换。 （图片参考： 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.

让我们使用以下示例了解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 this parameter with Current timestamp.

执行以下查询。 在此查询中，我们定义了整数类型的变量@ID。 我们尝试用当前时间戳替换此参数中的NULL值。

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.

我们收到以下错误消息。 它不能将数据类型从日期时间转换为整数。 我们可以使用SQL Convert函数来转换适当的数据类型。

Msg 257, Level 16, State 3, Line 2
Implicit 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 of datatime2 data type is higher than the timestamp data type.

现在，让我们将参数@ID的数据类型从整数替换为datatime2。 在数据优先级表中，datatime2数据类型的优先级高于时间戳数据类型。

DECLARE @ID DateTime2
SELECT 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

在本文中，我们探讨了SQL ISNULL函数及其在用指定值或字符串替换NULL值中的用法。 欢迎在下面的反馈中提问或发表评论

翻译自: https://www.sqlshack.com/sql-isnull-function-2/

sql isnull函数