sql exists_SQL Exists运算符–终极指南

刺骨的言语ヽ痛彻心扉 2023-02-28 06:05 26阅读 0赞

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:**

**输出：**

<table> 
 <tbody> 
  <tr> 
   <td><strong>id</strong></td> 
   <td><strong>Cost</strong></td> 
   <td><strong>city</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
   <td>Pune</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>50</td> 
   <td>Satara</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>65</td> 
   <td>Pune</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>97</td> 
   <td>Mumbai</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>12</td> 
   <td>USA</td> 
  </tr> 
 </tbody> 
</table>

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:**

**输出：**

![Supply Table][]

**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表查询][SQL Create table query]创建了表-“ Info”和“ Supply”，并使用[SQL Insert命令][SQL Insert command]向其中添加了数据。

--------------------

### 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 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查询][SQL Delete query] ，以执行以下操作–

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 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 Update query]与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 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 Operator _ Documentation]
    
    [SQL Exists运算符-文档][SQL Exists Operator _ Documentation]

> 翻译自: [https://www.journaldev.com/42152/sql-exists-operator][https_www.journaldev.com_42152_sql-exists-operator]

sql exists

[Supply Table]: /images/20230209/9d75c935e934473d8f0478bb5482be2e.png
[SQL Create table query]: https://www.journaldev.com/24795/sql-create-table
[SQL Insert command]: https://www.journaldev.com/18270/sql-insert-query
[SQL Select]: https://www.journaldev.com/18262/sql-select
[SQL Exists--SELECT Statement]: /images/20230209/7a48a05fba624d04bda350e2c932b3f9.png
[SQL Delete query]: https://www.journaldev.com/18404/sql-delete-query
[SQL Exists--DELETE Statement]: /images/20230209/e06ccc316096413db008b53269080bc0.png
[SQL Update query]: https://www.journaldev.com/18285/sql-update-statement-query
[SQL Exists--UPDATE Statement]: http://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4uam91cm5hbGRldi5jb20vd3AtY29udGVudC91cGxvYWRzLzIwMjAvMDcvU1FMLUV4aXN0cy1VUERBVEUtc3RhdGVtZW50LnBuZw?x-oss-process=image/format,png
[SQL JournalDev]: https://www.journaldev.com/sql
[SQL Exists Operator _ Documentation]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sqlallproducts-allversions
[https_www.journaldev.com_42152_sql-exists-operator]: https://www.journaldev.com/42152/sql-exists-operator