sql如何處理null值

前言 (Preface)

A friend who has recently started learning SQL asked me about NULL values and how to deal with them. If you are new to SQL, this guide should give you insights into a topic that can be confusing to beginners.

最近開始學習SQL的一個朋友問我有關NULL值以及如何處理它們的信息。 如果您不熟悉SQL，則本指南應為您提供一個可能使初學者感到困惑的主題的見解。

什么是NULL？ (What is NULL?)

NULL is used in SQL to indicate that a value doesn’t exist in the database.It’s not to be confused with an empty string or a zero value.

SQL中使用NULL表示數據庫中不存在任何值，請勿將其與空字符串或零值混淆。

While NULL indicates the absence of a value, the empty string and zero both represent actual values. To use an analogy, just as the lack of an answer doesn’t necessarily mean the answer is “no”, the lack of a value doesn’t mean it is zero.

NULL表示缺少值，而空字符串和零都表示實際值。 用類推，就像缺少答案并不一定意味著答案是“否”一樣，缺少值并不意味著它就是零。

Misunderstanding how NULL works in SQL can result in unexpected errors and output, so without further ado, let’s see how we can tackle it.

誤解NULL在SQL中的工作方式會導致意外的錯誤和輸出，因此，不費吹灰之力，讓我們看看如何解決它。

For this exercise, we will work with the tblSouthPark table.

對于本練習，我們將使用tblSouthPark表。

SELECT *FROM tblSouthPark

1. IS NULL和IS NOT NULL運算符 (1. IS NULL and IS NOT NULL Operators)

We cannot use the comparison operators, =,<,>,<>, to test for NULL values. Instead, we have to use IS NULL and IS NOT NULL predicates.

我們不能使用比較運算符=,<,>,<>來測試NULL值。 相反，我們必須使用IS NULL和IS NOT NULL 謂詞 。

• IS NULL: Return rows that contain NULL values

  IS NULL ：返回包含NULL值的行

Syntax: expression IS NULL

語法：表達式IS NULL

SELECT
ID,
Student,
Email1,
Email2FROM tblSouthParkWHERE Email1 IS NULL AND Email2 IS NULL
ORDER BY ID

The above query yields all records where both Email1 and Email2 columns are NULL.

上面的查詢將產生所有記錄，其中Email1和Email2列均為NULL。

• IS NOT NULL: As the name suggests, it is the inverse of IS NULL.

  IS NOT NULL：顧名思義，它與IS NULL相反。

Syntax: expression IS NOT NULL

語法：表達式不為空

SELECT
ID,
Student,
Email1,
Email2FROM tblSouthPark
WHERE Email1 IS NOT NULL AND Email2 IS NOT NULL
ORDER BY ID

The above query yields all records where both Email1 and Email2 columns are NOT NULL.

上面的查詢將產生所有記錄，其中Email1和Email2列都不為空。

2. ISNULL()函數 (2. ISNULL() Function)

The ISNULL function returns the specified value if the given expression is NULL. Otherwise, if the expression is not NULL, it returns the expression itself.

如果給定表達式為NULL，則ISNULL函數將返回指定值。 否則，如果表達式不為NULL，它將返回表達式本身。

Syntax: ISNULL(expression, value)

語法： ISNULL(表達式，值)

Let’s understand this by running a simple query on our SouthPark table.

讓我們通過在SouthPark表上運行一個簡單的查詢來了解這一點。

SELECT
ID,
Student,
ISNULL(Father,'Missing') AS FatherFROM tblSouthParkORDER BY ID

This query yields the following result:

該查詢產生以下結果：

Since the expression in the case of Eric Cartman evaluated to NULL, the ISNULL function returned the value, Missing. For other students, the expression was not NULL so it returned the expression itself.

由于在Eric Cartman的情況下表達式的計算結果為NULL，因此ISNULL函數返回值Missing 。 對于其他學生，該表達式不是NULL，因此它返回了表達式本身。

3. COALESCE()函數 (3. COALESCE() Function)

The COALESCEfunction returns the first non-NULL value in a given list. Unlike the ISNULL function, it can accept multiple expressions.

COALESCE函數返回給定列表中的第一個非NULL值。 與ISNULL函數不同，它可以接受多個表達式。

Syntax: COALESCE(expression[1…..n])

語法： COALESCE(表達式[1…..n])

For example, SELECT COALESCE(NULL, NULL, 'red', 'blue', NULL) returns red as it’s the first non-NULL value. If all the values are NULL, the COALESCE function will return NULL.

例如， SELECT COALESCE (NULL, NULL, 'red', 'blue', NULL)返回red因為它是第一個非NULL值。 如果所有值均為NULL，則COALESCE函數將返回NULL。

Let us use the COALESCE function on our SouthPark table:

讓我們在SouthPark表上使用COALESCE函數：

SELECT
ID,
Student,
COALESCE(Email1, Email2, 'N/A') AS Primary_EmailFROM tblSouthParkORDER BY ID

The above query yields the following result:

上面的查詢產生以下結果：

As expected, since both Email1 and Email2 are null in Kenny’s case, the COALESCE function returns N/A as the Primary_Email. For Stan, Email2 is returned as the Primary_Email as it is the first non-NULL value in the COALESCE function. For others, Email1 is returned as the Primary_Email.

不出所料，由于在Kenny的情況下Email1和Email2均為null，因此COALESCE函數將N/A作為Primary_Email返回。 對于Stan，Email2作為Primary_Email返回，因為它是COALESCE函數中的第一個非NULL值。 對于其他郵件，Email1作為Primary_Email返回。

4.案例表達 (4. CASE Expression)

We can also use the good old CASE expression to replace NULL values.

我們還可以使用良好的舊CASE表達式替換NULL值。

Here is the general syntax for the CASE expression:

這是CASE表達式的一般語法：

CASE
WHEN expression_1 THEN result_1
WHEN expression_2 THEN result_2
.
.
.
WHEN expression_n THEN result_n
ELSE else_expressionEND

We can generate the same output we got using COALESCE above by using CASE.

我們可以使用CASE生成與上面使用COALESCE得到的輸出相同的輸出。

The query will look like this:

查詢將如下所示：

SELECT
ID,
Student,
CASE
WHEN Email1 IS NOT NULL THEN Email1
WHEN Email2 IS NOT NULL THEN Email2
ELSE 'N/A'
END AS Primary_EmailFROM tblSouthParkORDER BY ID

This query yields the same output:

此查詢產生相同的輸出：

Note: The CASE expression is syntactically similar to the COALESCE function. In fact, COALESCE is like a shorthand for CASE. The former is short and simple, but the latter is more clear and easy to understand.

注意： CASE 表達式在語法上類似于 COALESCE 函數。 實際上， COALESCE 就像 CASE 的簡寫 。 前者簡短明了，但后者更清晰易懂。

5. NULLIF()函數 (5. NULLIF() Function)

The NULLIF function takes two expressions and returns NULL if the expressions are equal, or the first expression otherwise.

NULLIF函數采用兩個表達式，如果兩個表達式相等，則返回NULL，否則返回第一個表達式。

Syntax: NULLIF(expression_1, expression_2)

語法： NULLIF(表達式_1，表達式_2)

NULLIF('Red','Orange') -- Returns RedNULLIF(0,NULL) -- Returns 0NULLIF(0,0) -- Returns NULL

Where NULLIF comes in handy is in case of data that contains a mixture of null and empty strings in a column. Let’s understand this with an example.

如果數據在列中包含空字符串和空字符串的混合物， NULLIF會很方便。 讓我們通過一個例子來理解這一點。

We see that the Phone column in our table contains both NULL and empty strings.

我們看到表中的Phone列同時包含NULL和空字符串。

We can standardize this by changing the empty string to NULL using NULLIF:

我們可以通過使用NULLIF將空字符串更改為NULL來對此進行標準化：

SELECT
ID,
Student,
NULLIF(Phone,'') AS PhoneFROM tblSouthParkORDER BY ID

The above query yields:

上面的查詢產生：

Another good use case for NULLIF is to prevent “division by zero” errors:

NULLIF另一個好用例是防止“被零除”錯誤：

var1 = 1
var2 = 0var1/var2 --This will generate a "division by zero" errorvar1/NULLIF(var2,0)--This doesn't trigger a "division by zero" error

In the second case, we do not get a “division by zero” error as NULL is returned in the denominator in place of 0.

在第二種情況下，由于分母返回NULL而不是0，所以我們沒有得到“被零除”的錯誤。

獎金提示 (Bonus Tips)

• TheCOALESCE function is standard ANSI, while ISNULL and NULLIF are not. This makes COALESCE more general and portable (across different SQL flavors and RDBMS) than ISNULL and NULLIF.

  COALESCE函數是標準ANSI，而ISNULL和NULLIF不是。 與ISNULL和NULLIF相比，這使COALESCE更具通用性和可移植性(適用于不同SQL風格和RDBMS)。

• NULL is the smallest value in the sorting order. If we order by a column that contains NULL values, then the rows with NULL values will sort at the top by default. Use the DESC sort order to sort in reverse.

  NULL是排序順序中的最小值。 如果我們按包含NULL值的列排序，則默認情況下，具有NULL值的行將排在最前面。 使用DESC排序順序進行反向排序。

Tip: To sort in alphabetical order (ASC) with NULL values at the end, you can use a CASE expression in the ORDER BY clause:ORDER BY
CASE WHEN column_name IS NULL THEN 1 ELSE 0 END,column_name;

• The aggregate functions in SQL (SUM, COUNT, AVG, MAX, MIN) do not handle NULL values and eliminate them before performing any calculations. The only exception to this is the COUNT(*) function — it returns the count of all rows, including those rows where all fields are NULL.

  SQL中的聚合函數( SUM ， COUNT ， AVG ， MAX ， MIN )不處理NULL值，并在執行任何計算之前將其消除。 唯一的例外是COUNT(*)函數-它返回所有行的計數，包括所有字段均為NULL的行。

• In the case of GROUP BY clause, if a column contains rows with NULL values, then those will be grouped into one group.

  對于GROUP BY子句，如果一列包含具有NULL值的行，則這些行將被分組為一組。

• You can chain COALESCE and NULLIF functions in cases where a column contains both NULL and empty strings and you need to create a consolidated column. For example:

  如果列同時包含NULL和空字符串，并且您需要創建一個合并的列， NULLIF可以鏈接COALESCE和NULLIF函數。 例如：

INPUT TABLE: Sample
+==============+==============+
| Work | Cell |
+==============+==============+
| | 717-735-6382 |
+--------------+--------------+
| 546-373-9363 | 493-353-3638 |
+--------------+--------------+
| NULL | 657-428-3639 |
+--------------+--------------+QUERYSELECT COALESCE(NULLIF(Work,''),Cell) AS Primary FROM SampleOUTPUT
+==============+
| Primary |
+==============+
| 717-735-6382 |
+--------------+
| 546-373-9363 |
+--------------+
| 657-428-3639 |
+--------------+We can also use a quick-and-dirty CASE expression to do the same:CASE WHEN Work <> '' THEN Work ELSE Cell END

結語 (Wrapping Up)

You should now have all you need to successfully tackle NULL values in SQL. Of course, we did not cover each and every edge case here, but this should be a good starting point for a beginner. If you get stuck, Stack Overflow is your friend. As always, practice is key!

現在，您應該擁有在SQL中成功處理NULL值所需的全部內容。 當然，我們沒有在這里介紹每一個邊緣情況，但這對于初學者來說應該是一個很好的起點。 如果您遇到困難， Stack Overflow是您的朋友。 與往常一樣，練習是關鍵！

翻譯自: https://medium.com/better-programming/how-to-deal-with-null-values-in-sql-the-right-way-69861f2debbf

sql如何處理null值

總結

以上是生活随笔為你收集整理的sql如何处理null值_如何正确处理SQL中的NULL值的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。