Sql 简明教程

SQL - Null Functions

SQL NULL 函数用于对存储在数据库表中的 NULL 值执行操作。

SQL NULL functions are used to perform operations on NULL values that are stored in the database tables.

当数据不存在或所需的信息不可用时，NULL 值作为数据库中的占位符。它是一个不与任何特定数据类型关联的灵活值，并且可以用于各种数据类型的列中，包括字符串、int、varchar 等。

A NULL value serves as a placeholder in the database when data is absent or the required information is unavailable. It is a flexible value not associated to any specific data type and can be used in columns of various data types, including string, int, varchar, and more.

以下是 NULL 值的各种特性：

Following are the various features of a NULL value −

  1. The NULL value is different from a zero value or a field containing a space. A record with a NULL value is one that has been left empty or unspecified during record creation.

  2. The NULL value assists us in removing ambiguity from data. Thus, maintaining the uniform datatype across the column.

SQL NULL Functions

为了处理数据库表中的这些 NULL 值，SQL 提供了各种 NULL 函数。它们如下所示：

To handle these NULL values in a database table, SQL provides various NULL functions. They are listed as follows −

  1. ISNULL()

  2. COALESCE()

  3. NULLIF()

  4. IFNULL()

The ISNULL() Function

SQL ISNULL() 函数根据表达式是否为空返回 0 和 1。如果表达式为空，则此函数返回 1；否则，返回 0。

The SQL ISNULL() function returns 0 and 1 depending on whether the expression is null or not. If the expression is null, then this function returns 1; otherwise, it returns 0.

Syntax

以下是要点的语法 ISNULL() 功能 -

Following is the syntax for the ISNULL() function −

ISNULL(column_name)

Example

首先，让我们创建一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，使用以下查询-

First of all let us create a table named CUSTOMERS, containing the personal details of customers including their name, age, address and salary etc., using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，使用 INSERT INTO 语句将记录插入到此表中，如下所示 -

Now, insert records into this table using the INSERT INTO statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', NULL ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', NULL ),
(7, 'Indore', 24, 'Indore', 10000.00 );

该表将被创建为：

The table will be created as −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

NULL

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

NULL

7

Indore

24

Indore

10000.00

以下是查询以检查 SALARY 是否为 NULL -

Following is the query to check whether SALARY is NULL or not −

SELECT SALARY, ISNULL(SALARY) AS Null_value FROM CUSTOMERS;

Output

在执行上述查询时，我们得到“SALARY”和 Null_value 列。如果 SALARY 为 NULL，那么它们的空值为 1；否则为 0。-

On execution of the above query, we get the column "SALARY" and Null_value. If the SALARY is NULL, then their null value is 1; otherwise, it is 0. −

SALARY

Null_value

2000.00

0

1500.00

0

NULL

1

6500.00

0

8500.00

0

NULL

1

10000.00

0

The COALESCE() Function

SQL COALESCE() 函数返回其参数中首次出现的非空表达式。如果所有表达式都为 NULL，那么 COALESCE() 函数将返回 NULL。

The SQL COALESCE() function returns the first occurred NON-NULL expression among its arguments. If all the expressions are NULL, then the COALESCE() function will return NULL.

Syntax

以下是语法 COALESCE() 功能 -

Following is the syntax for the COALESCE() function −

COALESCE(expression_1, expression_2, expression_n);

Example

在以下查询中，我们返回首次出现的非空值 -

In the following query, we are returning the first occurred NON-NULL value −

SELECT COALESCE (NULL, 'welcome', 'tutorialspoint') AS Result;

Output

执行上述查询后，我们将得到“欢迎”作为结果，因为它是第一个非空值 -

On executing the above query, we get "welcome" as a result, because it is the first NON-NULL value −

Result

welcome

Example

在以下查询中，我们在 CUSTOMERS 表的 SALARY 和 AGE 列上使用 COALESCE() 函数。从这两列评估出的第一个非空值显示在另一个名为“Result”的列中。

In the following query, we are using the COALESCE() function on the SALARY and AGE columns of CUSTOMERS table. The first NON-NULL values evaluated from these two columns are displayed in another column named "Result".

SELECT NAME, SALARY, AGE, COALESCE(SALARY, AGE) AS Result FROM CUSTOMERS;

Output

当您执行上述查询时，我们会得到以下表格作为结果 -

When you execute the above query, we get the following table as a result −

NAME

SALARY

AGE

Result

Ramesh

2000.00

32

2000.00

Khilan

1500.00

25

1500.00

Kaushik

NULL

23

23.00

Chaitali

6500.00

25

6500.00

Hardik

8500.00

27

8500.00

Komal

NULL

22

22.00

Indore

10000.00

24

10000.00

The NULLIF() Function

SQL NULLIF() 函数比较两个表达式。如果两个表达式相同，则返回 NULL。否则，它会返回第一个表达式。此函数可以直接与 SELECT、WHERE 和 GROUP BY 等子句一起使用。

The SQL NULLIF() function compares two expressions. If both expressions are the same, it returns NULL. Otherwise, it returns the first expression. This function can be used directly with clauses like SELECT, WHERE, and GROUP BY.

Syntax

以下是 NULLIF() 函数的语法-

Following is the syntax of NULLIF() function −

NULLIF(expression_1, expression_2);

Example

以下 SQL 查询使用 NULLIF() 函数来比较 CUSTOMERS 表中 NAME 和 ADDRESS 列中的值。如果 NAME 值与 ADDRESS 值匹配，则结果为 NULL；否则，它将返回 NAME 值。结果值存储在称为“Result”的另一列中。

The following SQL query uses NULLIF() function to compare values in NAME and ADDRESS columns of the CUSTOMERS table. If the NAME value matches the ADDRESS value, the result is NULL; otherwise, it returns the NAME value. The result values are stored in another column called "Result".

SELECT NAME, ADDRESS, NULLIF(NAME, ADDRESS) AS Result FROM CUSTOMERS;

Output

当您执行上述查询时，我们会得到以下表格作为结果 -

When you execute the above query, we get the following table as a result −

NAME

ADDRESS

Result

Ramesh

Ahmedabad

Ramesh

Khilan

Delhi

Khilan

Kaushik

Kota

Kaushik

Chaitali

Mumbai

Chaitali

Hardik

Bhopal

Hardik

Komal

Hyderabad

Komal

Indore

Indore

NULL

The IFNULL() Function

IFNULL() 函数将数据库表中的 NULL 值替换为特定值。此函数接受两个参数。如果第一个参数为 NULL 值，则用第二个参数替换它。否则，第一个参数按原样返回。

The IFNULL() function replaces the NULL values in a database table with a specific value. This function accepts two arguments. If the first argument is a NULL value, it is replaced with the second argument. Otherwise, the first argument is returned as it is.

如果两个参数都是 NULL，则此函数的结果也是 NULL。

If both the arguments are NULL, the result of this function is also NULL.

Syntax

以下是 IFNULL() 函数的语法-

Following is the syntax for IFNULL() function −

IFNULL(column_name, value_to_replace);

Example

以下查询计算 CUSTOMERS 表中 SALARY 列中的值。使用 IFNULL() 函数，我们用值 5500 替换此列（如果有）中的 NULL 值 -

The following query evaluates the values in SALARY column of the CUSTOMERS table. Using the IFNULL() function, we are replacing the NULL values in this column (if any) with the value 5500 −

SELECT NAME, SALARY, IFNULL(SALARY, 5500) AS Result FROM CUSTOMERS;

Output

以下是以上查询的输出：

Following is the output of the above query −

NAME

SALARY

Result

Ramesh

2000.00

2000.00

Khilan

1500.00

1500.00

Kaushik

NULL

5500.00

Chaitali

6500.00

6500.00

Hardik

8500.00

8500.00

Komal

NULL

5500.00

Indore

10000.00

10000.00