Sql 简明教程

SQL - EXISTS Operator

The SQL EXISTS Operator

SQL EXISTS 运算符用于验证特定记录是否存在于 MySQL 表中。使用此运算符时，我们需要使用子查询指定记录（你需要检查其是否存在）。

The SQL EXISTS operator is used to verify whether a particular record exists in a MySQL table. While using this operator we need to specify the record (for which you have to check the existence) using a subquery.

EXISTS 运算符用于 WHERE clause 的 SELECT 语句中，根据另一表中相关记录的存在来筛选记录。

The EXISTS operator is used in the WHERE clause of a SELECT statement to filter records based on the existence of related records in another table.

It is a logical operator.
It returns a Boolean value TRUE or FALSE.
It returns TRUE if the subquery returns at least one record.
If the EXISTS operator returns TRUE, the outer query will get executed; otherwise not.
It can be used in SELECT, UPDATE, DELETE or INSERT statements.

在许多实际场景中，使用 EXISTS 运算符是过滤数据的高效方式，包括根据相关数据的是否存在过滤记录，根据相关记录的存在汇总数据以及优化查询。

The use of the EXISTS operator is an efficient way to filter data in many real-life scenarios, including filtering records based on the existence of related data, aggregating data based on the existence of related records, and optimizing queries.

Syntax

SQL EXISTS 运算符的基本语法如下所示 −

The basic syntax of the SQL EXISTS operator is as follows −

WHERE EXISTS (subquery);

其中， subquery 是使用的 SELECT 语句。如果子查询在其结果集中返回至少一条记录，则 EXISTS 运算符将评估为 TRUE；否则为 FALSE。

Where, the subquery used is the SELECT statement. The EXISTS operator will evaluate to TRUE if the subquery returns at least one record in its result set; otherwise FALSE.

EXISTS Operator with SELECT Statement

SQL中的SELECT语句用于从数据库中一张或多张表中检索数据。我们可以将 EXISTS 运算符与 SELECT 语句一起使用，以检查满足特定条件的行是否存在。

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. We can use the EXISTS operator with a SELECT statement to check for the existence of rows that meet a certain condition.

Example

为了更好地理解它，让我们考虑一下 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示：

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

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 语句向此表中插入值：

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

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

该表将按如下方式创建：

The table will be created as follows −

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

让我们使用以下查询创建另一个表 CARS ，其中包含客户的 id、姓名和汽车价格等详细信息 −

Let us create another table CARS, containing the details such as id of the customer, name and price of the car, using the following query −

create table CARS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   PRICE INT NOT NULL,
   PRIMARY KEY(ID)
);

使用 INSERT 语句，让我们将值插入此表 −

Using the INSERT statement, let us insert values into this table −

insert INTO CARS VALUES
(2, 'Maruti Swift', 450000),
(4, 'VOLVO', 2250000),
(7, 'Toyota', 2400000);

获得的“CARS”表如下 −

The 'CARS' table obtained is as follows −

ID	NAME	PRICE
2	Maruti Swift	450000
4	VOLVO	2250000
7	Toyota	2400000

现在，我们正在检索汽车价格大于 2,000,000 的客户列表 −

Now, we are retrieving the lists of the customers with the price of the car greater than 2,000,000 −

SELECT * FROM CUSTOMERS WHERE
EXISTS (
   SELECT PRICE FROM CARS
   WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000
);

Output

产生的结果如下 −

The result produced is as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

EXISTS Operator with UPDATE Statement

我们还可以将 SQL EXISTS 运算符与 UPDATE 语句一起使用。它可以帮助我们会根据另一张表中匹配行的存在来更新表中的行。

We can also use the SQL EXISTS operator with an UPDATE statement. It helps us to update rows in a table based on the existence of matching rows in another table.

Example

假设如果我们要更改之前创建的 CUSTOMERS 和 CARS 表中某些客户的姓名，则可以使用 UPDATE 语句来完成此操作。在此，我们正在修改所有 id 等于 CARS 表的 id 的客户的姓名“Kushal”，可以使用 EXISTS 运算符，如下所示 −

Suppose if we want to change the name of certain customers from the CUSTOMERS and CARS tables previously created, then this can be done using UPDATE statement. Here, we are modifying the name 'Kushal' of all the customers whose id is equal to the id of the CARS table, using the EXISTS operator, as follows −

UPDATE CUSTOMERS SET NAME = 'Kushal'
WHERE EXISTS (
   SELECT NAME FROM CARS WHERE CUSTOMERS.ID = CARS.ID
);

Output

我们得到以下结果。我们可以观察到，已经修改了 3 行 −

We get the following result. We can observe that 3 rows have been modified −

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

Verification

我们可以使用 SELECT 语句检索表的內容，以此验证该更改是否反映在表中。下面是在 CUSTOMERS 表中显示记录的查询 −

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

该表显示如下：

The table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Kushal	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Kushal	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Kushal	24	Indore	10000.00

正如我们在上表中看到的，“Khilan”、“Chaitali”和“Muffy”的 NAME 已更新为“Kushal”。

As we can see in the above table, the NAME of 'Khilan', 'Chaitali' and 'Muffy' has been updated to 'Kushal'.

EXISTS Operator with DELETE Statement

EXISTS 运算符还可以与 DELETE 语句一起使用，以根据子查询返回的行是否存在来删除行。

The EXISTS operator can also be used with a DELETE statement to delete rows based on the existence of rows returned by a subquery.

Example

在此处，我们正在删除 CUSTOMERS 表中的行，该行的 id 等于 CARS 表的 id，且价格等于“2250000” −

In here, we are deleting the row in the CUSTOMERS table whose id is equal to the id of the CARS table having price equal to '2250000' −

DELETE FROM CUSTOMERS WHERE
EXISTS (
   SELECT * FROM CARS
   WHERE CARS.ID = CUSTOMERS.ID AND CARS.PRICE = 2250000
);

Output

我们得到以下结果。我们可以观察到，已经删除了 1 行 −

We get the following result. We can observe that 1 row has been deleted −

Query OK, 1 row affected (0.01 sec)

Verification

我们可以使用以下查询来纠正 CUSTOMERS 表中所做的更改：

We can rectify the changes done in the CUSTOMERS table using the following query −

SELECT * FROM CUSTOMERS;

该表显示如下：

The table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

正如我们在上表中所看到的，具有 NAME 'Chaitali' 的行已被删除，因为 Chaitali 在 CUSTOMERS 表中的 ID 是 '4'，这等于 CARS 表中的 ID，其价格等于 '2250000'。

As we can see in the above table, the row with the NAME 'Chaitali' has been deleted since the id of Chaitali in CUSTOMERS table was '4', which is equal to the id of the CARS table having price equal to '2250000'.

NOT Operator with EXISTS Operator

在 SQL 中，NOT EXISTS 运算符用于从一个表中选择不存在于另一个表中的记录。

In SQL, the NOT EXISTS operator is used to select records from one table that do not exist in another table.

Syntax

以下是 SQL 中 NOT EXISTS 运算符的基本语法：

Following is the basic syntax of NOT EXISTS operator in SQL −

WHERE NOT EXISTS (subquery);

其中，使用的 subquery 是 SELECT 语句。

Where, the subquery used is the SELECT statement.

Example

以下查询给出了未购买任何汽车的客户的名称：

The below query gives the names of the customers who have not bought any car −

SELECT * FROM CUSTOMERS WHERE NOT
EXISTS (
   SELECT * FROM CARS WHERE CUSTOMERS.ID = CARS.ID
);

Output

执行以上查询得到以下输出：

Following output is obtained by executing the above query −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

Usage of SQL EXISTS Operator

SQL 中的 EXISTS 运算符在现实场景中广泛用于根据另一表中相关数据的存在过滤记录。一些常见的用例包括：

The EXISTS operator in SQL is widely used in real-life scenarios to filter records based on the existence of related data in another table. Some common use cases include −

Checking for the existence of records in a many-to-many relationship − The EXISTS operator can be used to check whether a record exists in a join table for a many-to-many relationship, for example, finding all customers who have purchased a particular product.
Filtering records based on the existence of related records − The EXISTS operator can be used to filter records based on the existence of related records in another table. For example, finding all orders that have associated order details.
Aggregating data based on the existence of related records − The EXISTS operator can be used to aggregate data based on the existence of related records. For example, finding the number of customers who have placed an order.
Optimizing queries − The EXISTS operator can be used to optimize queries by only returning the necessary data. For example, finding the first order for each customer without using a self-join.

这些只是 EXISTS 运算符在现实场景中如何使用的几个示例。具体用例将取决于数据和查询的要求。

These are just a few examples of how the EXISTS operator can be used in real-life scenarios. The specific use case will depend on the data and the requirements of the query.