Sql 简明教程

SQL - IN vs EXISTS

在 SQL 中，我们使用 IN 运算符简化查询，减少对多个 OR 条件的需求。它允许我们根据值列表匹配值。另一方面，EXISTS 运算符检查一个或多个行是否存在于子查询中，并基于该条件返回 true 或 false。如果子查询找到至少一行，EXISTS 运算符返回 true；否则，它返回 false。

In SQL, we use the IN operator to simplify queries and reduce the need for multiple OR conditions. It allows us to match a value against a list of values. On the other hand, the EXISTS operator checks whether one or more rows exist in a subquery and returns either true or false based on this condition. If the subquery finds at least one row, the EXISTS operator returns true; otherwise, it returns false.

The SQL IN Operator

SQL 中的 IN 运算符用于检查特定值是否与给定集合中的任何值匹配。这组值可以单独指定或从子查询中获取。我们可以在 WHERE 子句中使用 IN 运算符简化查询，减少使用多个 OR 条件。

The IN operator in SQL is used to check if a particular value matches any within a given set. This set of values can be specified individually or obtained from a subquery. We can use the IN operator with the WHERE clause to simplify queries and reduce the use of multiple OR conditions.

假设我们有一个名为 CUSTOMERS 的表，并且我们想根据其 ID 检索客户详细信息。在这种情况下，我们可以在 WHERE 子句中使用 IN 运算符来获取这些特定 ID 的详细信息。

Suppose we have a table named CUSTOMERS and we want to retrieve customer details based on their IDs. In this scenario, we can use the IN operator with the WHERE clause to fetch the details of these specific IDs.

Syntax

以下是 SQL IN 运算符的语法−

Following is the syntax of the SQL IN operator −

SELECT column_name
FROM table_name
WHERE column_name
IN (value1, value2, valueN);

在上面的语法中，column_name 与每个值匹配 (value1, value2, … valueN)。如果匹配发生，则 IN 运算符返回 true；否则返回 false。

In the above syntax, the column_name matches every value (value1, value2, … valueN). If the matches occur, The IN operators returns true; otherwise, false.

Example

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

First of all, let us create a table named CUSTOMERS 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, add records into the above created table using the INSERT INTO statement as shown below −

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

以下查询为 ID 为 1、2 或 3 的行检索 CUSTOMERS 表中的 NAME 和 SALARY 列。

The following query retrieves the NAME and SALARY columns from the CUSTOMERS table for rows where the ID is 1, 2, or 3.

SELECT NAME, SALARY FROM CUSTOMERS WHERE ID IN(1, 2, 3);

Output

在执行上述查询后，输出显示为 −

On executing the query above, the output is displayed as −

Name

Salary

Ramesh

2000.00

Khilan

1500.00

Kaushik

2000.00

The SQL EXISTS Operator

EXISTS 运算符用于查找满足给定条件集的给定表中的行的存在性。它是一个布尔运算符，将子查询的结果与现有记录进行比较，并返回 true 或 false。

The EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria. It is a Boolean operator that compares the result of the subquery to an existing record and returns true or false.

如果子查询获取一个或多个记录，则返回的值为 true；如果没有匹配的记录，则返回 false。EXISTS 运算符遵循查询的效率特性，即当检测到第一个 true 事件时，它将自动停止进一步处理。

The returned value is true, if the subquery fetches single or multiple records; and false, if no record is matched. EXISTS operator follows the querys efficiency features, i.e. when the first true event is detected, it will automatically stop processing further.

我们可以在 SELECT 、 UPDATE 、 INSERT 和 DELETE 查询中使用 EXISTS 运算符。

We can use the EXISTS operator with the SELECT, UPDATE, INSERT and DELETE queries.

Syntax

以下是 SQL EXISTS 运算符的基本语法 −

Following is the basic syntax of SQL EXISTS operator −

SELECT column_name FROM table_name
WHERE EXISTS (
   SELECT column_name FROM table_name
   WHERE condition
);

Example

首先，考虑 CUSTOMERS 表，并使用以下查询创建另一个名为 EMPLOYEES 的表 −

First of all, consider the CUSTOMERS table, and create another table named EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   EID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   CITY CHAR (25),
   CONTACT INT,
   PRIMARY KEY (EID)
);

现在，让我们使用 INSERT INTO 语句将一些记录插入到 EMPLOYEES 表中，如下所示 −

Now, let us insert some records into the EMPLOYEES table using the INSERT INTO statement as shown below −

INSERT INTO EMPLOYEES VALUES
(1, 'Varun', 32, 'Ahmedabad', 12345),
(2, 'Mahesh', 22, 'Kashmir', 34235 ),
(3, 'Suresh', 43, 'Kerala', 12355 );

该表将按如下方式创建：

The table will be created as follows −

EID	NAME	AGE	CITY	CONTACT
1	Varun	32	Ahmedabad	12345
2	Mahesh	22	Kashmir	34235
3	Suresh	43	Kerala	12355

在以下查询中，我们使用 EXISTS 运算符来获取 CUSTOMERS 的姓名和年龄，其 AGE 与 EMPLOYEES 表中的 AGE 相同。

In the following query, we are using the EXISTS operator to fetch the names and ages of CUSTOMERS whose AGE is same as the AGE in the EMPLOYEES table.

SELECT NAME, AGE
FROM CUSTOMERS
WHERE EXISTS(
   SELECT * FROM EMPLOYEES
   WHERE CUSTOMERS.AGE = EMPLOYEES.AGE
);

Output

以下是以上查询的输出：

Following is the output of the above query −

NAME

AGE

Ramesh

Komal

IN vs EXISTS

下表总结了 IN 和 EXISTS 之间的所有差异 −

Following table summarizes all the differences between IN and EXISTS −

S.No.

EXISTS

It is applied to the SQL query to remove the multiple OR conditions.

It is used to find whether the data in the subquery truly exist.

It executes all values contained within the IN block.

If the value is matched, displays the details of the given value. It will terminate the further process if the condition is met.

It can be used for the comparison of a null value because it returns true, false, and a null value.

It cannot be used for the comparison of a null value because it returns only true and false values.

It can be used with subqueries as well as with values.

It can be used only with subqueries.

It executes faster when the subquery is smaller.

It executes faster when the subquery is larger. Because it is more efficient than IN and returns only a Boolean value.