使用SQL Coalesce函数查询数据

We all know that a Null value is a field with no value. The statements that we are running daily will have to deal with Null values, especially when it comes to strings concatenation (adding strings together).

我们都知道Null值是没有值的字段。 我们每天运行的语句将必须处理Null值，尤其是在涉及字符串连接（将字符串加在一起）时。

Let’s start with the SQL Coalesce definition. The Coalesce SQL function returns first nonnull expression among its arguments. The rule of thumb here is that Null plus anything equals zero. It’s like zero times anything equals zero. For example, the big problem here is when working with things like addresses. To be more specific, it’s a problem when we have to combine e.g. a street name with a postal code, additional address line, etc. So, using the logic from above, if one of those has a Null value, the whole thing is going to be Null.

让我们从SQL Coalesce定义开始。 Coalesce SQL函数返回其参数中的第一个非空表达式。 经验法则是Null加任何东西等于零。 就像零乘以零一样。 例如，这里最大的问题是使用地址之类的东西时。 更具体地说，当我们必须将街道名称与邮政编码，附加地址行等结合使用时，这是一个问题。因此，使用上面的逻辑，如果其中一个具有Null值，那么整个过程就在进行为空。

Now, if you are not so familiar with the SQL Coalesce expression, it’s probably best to get familiar with the Case expression first and its formats because the Coalesce SQL expression is a syntactic shortcut for the Case expression. We will explain this using a simple example later on but feel free to check out the following article for more details on the Case expression: Querying data using the SQL Case statement

现在，如果您不太熟悉SQL Coalesce表达式，则最好先熟悉Case表达式及其格式，因为Coalesce SQL表达式是Case表达式的语法快捷方式。 稍后，我们将使用一个简单的示例对此进行解释，但是请随时查看以下文章以获取有关Case表达式的更多详细信息： 使用SQL Case语句查询数据

If we want to prove the Coalesce SQL definition from above, consider the following example that uses the “AdventureWorks2012” database:

如果要从上面证明Coalesce SQL定义，请考虑以下使用“ AdventureWorks2012”数据库的示例：

SELECT p.Title, 
       p.FirstName, 
       p.MiddleName, 
       p.FirstName+' '+p.LastName AS FirstLastName, 
       COALESCE(p.FirstName+' '+p.MiddleName+' '+p.LastName, p.FirstName+' '+p.LastName, p.FirstName, p.LastName) AS FullName
FROM Person.Person AS p;

Before we run this query, let’s take a look at the columns from Object Explorer. Note that both “FirstName” and “LastName” are not null which means that they cannot be empty:

在运行此查询之前，让我们看一下对象浏览器中的列。 请注意，“ FirstName”和“ LastName”都不为空，这意味着它们不能为空：

For the sake of the example simplicity, execute the script below to nullify the “LastName” column:

为了简单起见，请执行以下脚本以使“ LastName”列无效：

ALTER TABLE Person.Person ALTER COLUMN LastName NVARCHAR(50) NULL;
GO

Right-click the “Person” table from Object Explorer and choose the Edit Top 200 Rows command. Change the “LastName” value from “Sánchez” to Null of the very first record:

右键单击“对象资源管理器”中的“人员”表，然后选择“编辑前200行”命令。 将“ LastName”值从“Sánchez”更改为第一条记录的Null：

We just made sure that this record has the first name but not the last name. So, right now if we are to do just first name plus last name part of our initial query the result will be Null:

我们只是确保该记录具有名字而不是姓氏。 因此，现在，如果我们只执行初始查询的名字和姓氏部分，结果将为Null：

SELECT p.Title, 
       p.FirstName, 
       p.MiddleName, 
       p.FirstName+' '+p.LastName AS FirstLastName
FROM Person.Person AS p;

Here is the result:

结果如下：

As we mentioned at the beginning, this is a problem. But, if we run the query with the SQL Server Coalesce expression, by the Coalesce SQL definition it will return the first non-Null value:

正如我们一开始提到的，这是一个问题。 但是，如果我们使用SQL Server Coalesce表达式运行查询，则根据Coalesce SQL定义，它将返回第一个非null值：

As it can be seen from the query, we can do an expression one, comma, expression two, etc. We are basically saying add them all together using the SQL Coalesce expressions:

从查询中可以看出，我们可以执行表达式1，逗号，表达式2等。我们基本上是说要使用SQL Coalesce表达式将它们全部加在一起：

1. First name, middle name, last name – if any of those is Null, go to the next one 名，中间名，姓氏 -如果其中任何一个为Null，则转到下一个
2. First name, last name – same rules apply as above as well for below 名，姓 –上面和下面的规则相同
3. First name – this is where we hit the Coalesce and return the first non-Null value (Ken) 名字 –这是我们击中Coalesce并返回第一个非null值（肯）的地方
4. Last name – the last one will not get hit 姓氏 -姓氏不会被击中

We can even go an extra mile and say if all of the above is Null (first name, middle name, last name), provide a default value saying “No name”:

我们甚至可以加倍努力，说出上述所有内容是否都为Null（名字，中间名，姓氏），并提供一个默认值“ No name”：

SELECT p.Title, 
       p.FirstName, 
       p.MiddleName, 
       p.FirstName+' '+p.LastName AS FirstLastName, 
       COALESCE(p.FirstName+' '+p.MiddleName+' '+p.LastName, p.FirstName+' '+p.LastName, p.FirstName, p.LastName, 'No name') AS FullName
FROM Person.Person AS p;

And get something like this if the first, middle, and last name are Null:

如果名字，中间名和姓氏为Null，则得到如下所示的内容：

To wrap things up, hopefully you’ve seen how handy SQL Coalesce can be when you have to combine many fields and when some of them are Null. You can play with the output and basically see how it would look like if they are all there, what it would look like if a couple of them are there, and you can go all the way down the list to make all the possible combinations including the case when all of the fields have Null value and ensure that the result/output will not be a Null using the SQL Coalesce function.

总结一下，希望您已经了解了当必须合并多个字段并且其中一些字段为Null时SQL Coalesce可以多么方便。 您可以使用输出，基本上可以看到它们都在那里时的样子，如果其中有几个就可以看到它的样子，您可以一直沿列表向下进行以进行所有可能的组合，包括所有字段均具有Null值并使用SQL Coalesce函数确保结果/输出不会为Null的情况。

Furthermore, note that the SQL Server Coalesce expression and IsNull function have a similar purpose but behave differently. SQL Coalesce can be used on anything that can return a Null value and not just characters as shown in this article, but number too, etc. The IsNull function has only two parameters, where SQL Coalesce has X parameters and it can go for as long as you can keep adding expressions.

此外，请注意，SQL Server Coalesce表达式和IsNull函数具有相似的用途，但行为不同。 SQL Coalesce可以用于可以返回Null值的任何对象，不仅可以返回本文中显示的字符，还可以返回数字，等等。IsNull函数只有两个参数，其中SQL Coalesce具有X参数，并且可以使用很长时间因为您可以继续添加表达式。

I hope this has been informative for you and you have learned something about the Coalesce SQL function. Thanks for reading.

我希望这对您有所帮助，并且您已了解有关Coalesce SQL函数的知识。 谢谢阅读。

翻译自: https://www.sqlshack.com/querying-data-using-the-sql-coalesce-function/