SQL中的NULL值和NULL函数

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值 。 如下例所示：

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)

    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