sql exists_SQL Exists运算符–终极指南

sql exists

Hey, folks! Hope you all are doing well. In this article, we will be focusing on SQL Exists Operator in detail.

嘿伙计！ 希望大家一切都好。 在本文中，我们将重点关注SQL Exists运算符 。

---

---

存在SQL的工作 (Working of SQL Exists)

SQL Exists is an SQL Operator works with a subquery and enables us to check for the presence of particular records in that subquery. Further, it checks whether the subquery returns some records from the table depending upon the condition.

SQL Exists是一个SQL Operator与子查询一起使用，使我们能够检查该子查询中特定记录的存在。 此外，它根据条件检查子查询是否从表中返回一些记录。

Let us have a look at the below example to the operator.

让我们来看下面的示例给操作员。

Consider an Online Portal where the owner decides to offer a discount of $20 to every customer whose bills are higher than $350.

考虑一个在线门户，所有者决定为账单高于350美元的每个客户提供20美元的折扣。

For the same, the developer team at the backend would segregate the database and use the Exists operator to check for the presence of such condition in the various tables.

同样，后端的开发人员团队将隔离数据库，并使用Exists运算符检查各种表中是否存在这种情况。

Thus, SQL Exists executes a certain query, upon the presence of data returned by the subquery.

因此，SQL Exists在子查询返回的数据存在时执行特定查询。

Let us now understand the Syntax of Exists Operator in detail.

现在让我们详细了解“存在运算符”的语法。

---

---

存在运算符的语法 (Syntax of the Exists Operator)

The Exists Operator works well with SQL Update, Delete, Select and Insert query.

Exists运算符与SQL Update，Delete，Select和Insert查询配合使用很好。

SQL Update/Insert/Delete/Select query
WHERE EXISTS
(sub-query);

EXISTS evaluates to TRUE, if and only if the subquery gets executed and returns the specified records.

当且仅当子查询被执行并返回指定的记录时，EXISTS的计算结果才为TRUE。

The Sub query can be used to query another database too if the column values are comparable.

如果列值可比，则Sub查询也可用于查询另一个数据库。

Having understood the working and structure of Exists Operator, let us now focus on the implementation of the same in the upcoming section.

了解了Exists的工作原理和结构后，现在让我们在接下来的部分中重点介绍相同操作的实现。

---

---

通过示例实现SQL EXISTS (Implementing SQL EXISTS through examples)

Let us first create the table in SQL that we would be using in the below examples. Using some random names of places here for demonstration.

让我们首先在下面的示例中使用SQL创建表。 在此处使用一些随机的地点名称进行演示。

create table Info(id integer, Cost integer, city varchar(20));
insert into Info(id, Cost, city) values(1, 100, Pune);
insert into Info(id, Cost, city) values(2, 50, Satara);
insert into Info(id, Cost, city) values(3, 65, Pune);
insert into Info(id, Cost, city) values(4, 97, Mumbai);
insert into Info(id, Cost, city) values(5, 12, USA);

Output:

输出：

id	Cost	city
1	100	Pune
2	50	Satara
3	65	Pune
4	97	Mumbai
5	12	USA

ID	成本	市
1个	100	浦那
2	50	萨塔拉
3	65	浦那
4	97	孟买
5	12	美国

create table Supply(id integer, Cost integer);
insert into Supply(id, Cost) values(1, 100);
insert into Supply(id, Cost) values(2, 200);
insert into Supply(id, Cost) values(3, 300);
insert into Supply(id, Cost) values(4, 400);
insert into Supply(id, Cost) values(5, 12);

Output:

输出：

SQL Exists–Supply Table SQL存在-供应表

As mentioned above, we have created the tables — ‘Info’ and ‘Supply’ using SQL Create table query and have added data to it using SQL Insert command.

如上所述，我们已经使用SQL Create表查询创建了表-“ Info”和“ Supply”，并使用SQL Insert命令向其中添加了数据。

---

---

SQL存在选择查询 (SQL Exists with Select query)

In the below example, we have used SQL Select query along with the Exists Operator. The below query selects and displays row values of ‘id’ and ‘City’ only if ‘Cost’ has data values greater than 50.

在下面的示例中，我们将SQL Select查询与Exists运算符一起使用。 仅当“成本”的数据值大于50时，以下查询才选择并显示“ id”和“城市”的行值。

SELECT id,City
  FROM Info
 WHERE EXISTS
       (SELECT Cost
          FROM Info
         WHERE Cost>50);

Output:

输出：

SQL Exists–SELECT Statement SQL存在– SELECT语句

---

---

SQL存在删除查询 (SQL Exists with Delete query)

Now, we have implemented SQL Delete query with SQL Exists operator to perform the operation as follows–

现在，我们使用SQL Exists运算符实现了SQL Delete查询 ，以执行以下操作–

The below query would delete a data row from the table ‘Info’ if and only if, the column ‘id’ from both the tables(Info and Supply) is the same and there exists a value for ‘Cost’ as 12 in the Supply table.

当且仅当以下两个表（“信息”和“供应”）中的“ id”列相同且“供应”中存在“成本”的值为12时，以下查询才会从“信息”表中删除数据行表。

DELETE 
FROM Info
WHERE EXISTS (SELECT *
              FROM Supply
              WHERE Info.id = Supply.id
              AND Supply.Cost = 12);

Output:

输出：

SQL Exists–DELETE Statement SQL存在–删除语句

---

---

SQL存在更新查询 (SQL Exists with Update query)

In this example, we have used the SQL Update query along with SQL Exists Operator. All the data values of the column ‘Cost’ would be set to 20 if and only if the value for column ‘id’ of both the table happens to be equal.

在此示例中，我们将SQL Update查询与SQL Exists运算符一起使用。 当且仅当两个表的“ id”列的值恰好相等时，“ Cost”列的所有数据值都将设置为20。

Update Info
set Cost = 20
WHERE EXISTS (SELECT *
              FROM Supply
              WHERE Info.id = Supply.id
              );

Output:

输出：

SQL Exists–UPDATE Statement SQL存在-UPDATE语句

---

---

结论 (Conclusion)

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any doubt.

至此，我们到了本主题的结尾。 如果您有任何疑问，请在下面发表评论。

For more such posts related to SQL, please do visit SQL JournalDev.

有关与SQL有关的更多此类帖子，请访问SQL JournalDev 。

---

---

参考资料 (References)

• SQL Exists Operator — Documentation
  SQL Exists运算符-文档

翻译自: https://www.journaldev.com/42152/sql-exists-operator

sql exists