SQL中的NULL值和NULL函数

川长思鸟来 2023-03-05 12:50 14阅读 0赞

As you all know that **NULL value** means 0 (Zero), Empty cell, empty, missing or unknown data. If we do not fill any data in any field if it is not required or we can say that if that field is optional then by default, a table column hold **NULL value**.

众所周知， **NULL值**表示0(零)，单元格为空，数据为空，丢失或未知。 如果我们不需要在任何字段中填充任何数据(如果不需要)，或者可以说如果该字段是可选的，那么默认情况下，表列将保留**NULL值** 。

Sometime, **NULL values** are treated differently from other values. It is used as a placeholder for unknown or inapplicable values and It is not possible to test for **NULL values** with comparison operators ( =, <, or <>).

有时， **NULL值**与其他值的处理方式有所不同。 它用作未知或不适用值的占位符，并且无法使用比较运算符( = ， <或<> )测试**NULL值** 。

**Example:**

**例：**

In this table you can see as an example of **NULL VALUES**. Here, enroll is a primary key and S\_name these two column are required field that is why these two column cannot be NULL and Address & Contact columns in a Student is optional so it can create a **NULL VALUES**. As shown in example below:

在此表中，您可以看到**NULL VALUES**的示例。 在这里，enroll是主键， S\_name这两个列是必填字段，这就是为什么这两列不能为NULL的原因，而Student中的Address＆Contact列是可选的，因此它可以创建**NULL值** 。 如下例所示：

![NULL values example 1][]

If, you want to find out the NULL VALUE then we can use “ IS “ operator:

如果要查找空值，则可以使用“ IS”运算符：

Like: here, we want to find out the NULL VALUE in Address column then:

像：在这里，我们要在“地址”列中查找空值，然后：

SELECT Enroll, S_name FROM Student WHERE Address IS NULL;

Similarly, if you want to find out the NOT NULL VALUE in Address column then:

同样，如果要在“地址”列中查找“非空值”，则：

SELECT S_name FROM Student WHERE Address IS NOT NULL;

## 空函数 **(**Null functions**)** ##

![NULL values example 2][]

SELECT Dept_name,Dept_Info*(Admission+New_Student) FROM University;

We can also use NVL(), IFNULL(), and COALESCE() functions to find IS NULL or NOT NULL VALUES.

我们还可以使用NVL() ， IFNULL()和COALESCE()函数查找IS NULL或NOT NULL值。

**Different database platform uses different syntax and function:**

**不同的数据库平台使用不同的语法和功能：**

*  **MS Access**
    
    **MS Access**

SELECT S_name, Dept_info*(Admission+IIF(New_student),0,New_student)) 
        FROM University;

*  **SQL Server**
    
    **SQL服务器**
 *      SELECT s_name, Dept_info*(Admission+ISNULL(New_student,0)) 
            FROM University;
 *  **Oracle**
    
    **Oracle**
    
    Oracle does not have an
    
    Oracle没有
    
    ISNULL() function. However, we can use the NVL() function to achieve the same result:
    
    ISNULL()函数。 但是，我们可以使用NVL()函数获得相同的结果：
 *      SELECT S_name, Dept_info*(Admission+NVL(New_student,0)) 
            FROM University;
 *  **MySQL**
    
    **MySQL**
    
    MySQL does have an
    
    MySQL确实有一个
    
    ISNULL() function. That's why we use these IFNULL function:
    
    ISNULL()函数。 这就是为什么我们使用以下IFNULL函数：
 *      SELECT S_name, Dept_info*(Admission+IFNULL(New_student,0)) 
            FROM University;
        
        or we can use the COALESCE() function, like this:
    
        SELECT s_name, Dept_info*(Admission+COALESCE(New_student,0)) 
            FROM University;
    
    **Conclusion:**
    
    **结论：**
    
    In this article, we have learnt **NULL VALUES and its different function on different database**. I hope you understand the concept behind it. If you have any doubt/query, feel free to ask in comment box.
    
    在本文中，我们了解了**NULL值及其在不同数据库上的不同功能** 。 希望您了解其背后的概念。 如果您有任何疑问/疑问，请随时在评论框中提问。
    
    > 翻译自: [https://www.includehelp.com/sql/NULL-VALUES-and-NULL-FUNCTIONS.aspx][https_www.includehelp.com_sql_NULL-VALUES-and-NULL-FUNCTIONS.aspx]

[NULL values example 1]: /images/20230208/f938a339c5254f528a7fb777e1b7d335.png
[NULL values example 2]: /images/20230208/d593c146877f41c785ab2a32df792956.png
[https_www.includehelp.com_sql_NULL-VALUES-and-NULL-FUNCTIONS.aspx]: https://www.includehelp.com/sql/NULL-VALUES-and-NULL-FUNCTIONS.aspx