Sql 简明教程

SQL - IS NULL

假设一个数据表的一些字段存在 NULL 值。这些字段表明其中没有值。SQL 允许用户创建新的记录或修改现有记录，而无需指定字段的值。如果未提供值，该字段将存储为 NULL 值。

Let’s assume a table with NULL values in some of its fields. These fields indicate that no values are present in them. SQL allows users to create new records or modify existing ones without specifying a value for a field. If no value is provided, the field is stored with a NULL value.

在 SQL 中，不可能使用比较运算符（如 =、< 或 <>）检查 NULL 值。相反，我们使用 IS NULL 和 IS NOT NULL（NULL 值的否定）运算符。

In SQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (negation of NULL values) operators.

The SQL IS NULL Operator

SQL IS NULL 运算符用于检查列中的值是否为 NULL。如果列值为 NULL，则返回 true；否则返回 false。

The SQL IS NULL operator is used to check whether a value in a column is NULL. It returns true if the column value is NULL; otherwise false.

NULL 是表示缺失或未知数据的值，IS NULL 运算符允许我们筛选在特定列中包含 NULL 值的记录。

The NULL is a value that represents missing or unknown data, and the IS NULL operator allows us to filter for records that contain NULL values in a particular column.

Syntax

以下是 IS NULL 运算符的语法 −

Following is the syntax of IS NULL operator −

SELECT column_name1, column_name2, column_name3, ... , column_nameN
FROM table_name
WHERE column_nameN IS NULL;

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −

Firstly, let us create a table named CUSTOMERS using the following query −

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

现在，使用如下所示的 INSERT 语句向此表中插入值：

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

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

该表将按如下方式创建：

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	NULL	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	NULL	10000.00

IS NULL with SELECT Statement

我们可以将 IS NULL 运算符与 SELECT 语句一起使用，以筛选具有 NULL 值的记录。

We can use the IS NULL operator with a SELECT statement to filter the records with NULL values.

Example

在以下查询中，我们正在从 ADDRESS 为 null 的 CUSTOMERS 表中检索所有记录 −

In the following query, we are retrieving all the records from the CUSTOMERS table where the ADDRESS is null −

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NULL;

Output

在执行以上查询时，它将生成如下所示的输出 −

On executing the above query, it will generate the output as shown below −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	NULL	1500.00
7	Muffy	24	NULL	10000.00

IS NULL with COUNT() Function

我们还可以将 IS NULL 运算符与 SQL 中的 COUNT() 函数一起使用，以计算特定列中具有 NULL 值的记录数。

We can also use the IS NULL operator with the COUNT() function in SQL to count the number of records with NULL values in a particular column.

Syntax

以下是 IS NULL 运算符与 COUNT() 函数的语法 −

Following is the syntax of IS NULL operator with the COUNT() function −

SELECT COUNT(column_name)
FROM table_name
WHERE condition IS NULL;

Example

以下查询返回 CUSTOMERS 表的 SALARY 列中有空白字段（NULL）的记录数 −

The following query returns the count of records have a blank field (NULL) in SALARY column of the CUSTOMERS table −

SELECT COUNT(*) FROM CUSTOMERS WHERE SALARY IS NULL;

Output

下面显示了产生的输出：

The output produced is as shown below −

COUNT(*)

IS NULL with UPDATE Statement

我们可以在 SQL 中使用 UPDATE 语句和“IS NULL”运算符，以更新特定列中具有 NULL 值的记录。

We can use the UPDATE statement with the "IS NULL" operator in SQL to update records with NULL values in a particular column.

Syntax

以下是 SQL 中 IS NULL 运算符与 UPDATE 语句的语法 −

Following is the syntax of the IS NULL operator with the UPDATE statement in SQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NULL;

Example

在以下查询中，我们将 AGE 列的空白（NULL）记录更新为 48 −

In the following query, we are updating the blank (NULL) records of the AGE column to a value of 48 −

UPDATE CUSTOMERS SET AGE = 48 WHERE AGE IS NULL;

Output

执行上面程序时，将获得如下所示的输出：

When we execute the program above, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

要检查表是否已更新，请执行下面的 SELECT 查询：

To check whether the table has been updated or not, execute the SELECT query below −

SELECT * FROM CUSTOMERS;

该表显示如下：

The table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	NULL	1500.00
3	Kaushik	48	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	48	Hyderabad	4500.00
7	Muffy	24	NULL	10000.00

IS NULL with DELETE Statement

我们还可以将 DELETE 语句与 IS NULL 运算符一起使用，以删除特定列中具有 NULL 值的记录。

We can also use the DELETE statement with IS NULL operator to delete records with NULL values in a particular column.

Syntax

以下是 SQL 中 IS NULL 运算符与 DELETE 语句的语法 −

Following is the syntax of the IS NULL operator with the DELETE statement in SQL −

DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NULL;

Example

在以下查询中，我们删除 CUSTOMERS 表的 SALARY 列中存在的空白（NULL）记录 −

In the following query, we are deleting the blank (NULL) records present in the SALARY column of CUSTOMERS table −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

我们获得以下结果 −

We get the following result −

Query OK, 2 rows affected (0.01 sec)

Verification

执行下面给出的 SELECT 查询，以检查表是否已更改 −

Execute the SELECT query given below to check whether the table has been changed or not −

SELECT * FROM CUSTOMERS;

如果我们编译并运行该程序，结果如下所示 −

If we compile and run the program, the result is produced as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	NULL	1500.00
3	Kaushik	NULL	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	NULL	10000.00