Sql 简明教程

SQL - IN Operator

The SQL IN Operator

SQL IN Operator 用于在 WHERE 子句中指定多个值或子查询。它返回其中指定的列与列表中的一个值匹配的所有行。值或子查询列表必须用括号指定，例如 IN (select query) 或 IN (Value1, Value2, Value3, …) 。

The SQL IN Operator is used to specify multiple values or sub query in the WHERE clause. It returns all rows in which the specified column matches one of the values in the list. The list of values or sub query must be specified in the parenthesis e.g. IN (select query) or IN (Value1, Value2, Value3, …).

在某些情况下，我们可以使用多个 OR 语句来在 SELECT、DELETE、UPDATE 或 INSERT 语句中包含多个条件。或者，我们可以使用 IN 运算符而不是多个 OR 语句。

In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. Alternatively, we can use the IN operator instead of multiples OR statements.

IN 运算符可用于 SQL 中的任何数据类型。它用于根据指定的值从数据库表中筛选数据。

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values.

Syntax

SQL IN 运算符指定多个值的语法如下所示 −

The basic syntax of the SQL IN operator to specify multiple values is as follows −

WHERE column_name IN (value1, value2, value3, ...);

其中，

Where,

value1, value2, value3, … are the values in the list to be tested against the expression. The IN operator returns TRUE if any of these values is found in the list, and FALSE if it is not.

IN Operator with SELECT Statement

我们可以使用 SQL IN 运算符在 WHERE 子句中指定多个值，还可以使用它在 SELECT 语句中检索与任何指定值匹配的数据。

We can use the SQL IN operator to specify multiple values in a WHERE clause, and we can also use it in a SELECT statement to retrieve data that matches any of the specified values.

在此，我们使用 IN 运算符在 SELECT 语句中指定多个值。

Here, we are using the IN operator to specify multiple values in SELECT statement.

Example

在此示例中，我们使用 IN 运算符在 SELECT 语句中指定多个值，考虑了 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水等，如下所示 −

In this example, we are using the IN operator to specify multiple values in SELECT statement 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

假设基于上述表，我们想要显示 NAME 等于“Khilan”、“Hardik”和“Muffy”（字符串值）的记录。这可以使用 IN 运算符实现，如下所示 −

Suppose based on the above table we want to display records with NAME equal to 'Khilan', 'Hardik' and 'Muffy'(string values). This can be achieved using IN operator as follows −

SELECT * FROM CUSTOMERS
WHERE NAME IN ('Khilan', 'Hardik', 'Muffy');

Output

获得的结果如下 −

The result obtained is as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Example

上面的查询也可以使用 OR 运算符完成。下面是一个示例 −

The above query can also be done using OR operator. Following is an example −

SELECT * FROM CUSTOMERS
WHERE NAME = 'Khilan' OR NAME = 'Hardik' OR NAME = 'Muffy';

Output

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

IN Operator with UPDATE Statement

我们还可以在 UPDATE 语句中使用 SQL IN 运算符来更新与 WHERE 子句中任何指定值匹配的行。UPDATE 语句用于修改数据库表中的现有数据。

We can also use the SQL IN operator in an UPDATE statement to update rows that match any of the specified values in a WHERE clause. The UPDATE statement is used to modify existing data in a database table.

Example

在此，我们使用 IN 运算符在 UPDATE 语句中指定多个值并更新之前创建的 CUSTOMERS 表。在此，我们更改年龄为“25”或“27”的客户的记录，并将年龄值更新为“30” −

Here, we are using the IN operator to specify multiple values in the UPDATE statement and updating the CUSTOMERS table previously created. Here, we are changing the records of the customers with age '25' or '27' and updating the age value to '30' −

UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27);

Output

我们得到以下结果。我们可以观察到 3 位客户的年龄已修改 −

We get the following result. We can observe that the age of 3 customers has 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	Khilan	30	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	30	Mumbai	6500.00
5	Hardik	30	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

正如我们在上表中看到的，“Khilan”、“Chaitali”和“Hardik”的 AGE 已更新为“30”。

As we can see in the above table, the AGE of 'Khilan', 'Chaitali' and 'Hardik' has been updated to '30'.

IN Operator with NOT

要否定一个条件，我们使用 NOT 运算符。SQL IN 运算符可以与 NOT 运算符结合使用，以在 WHERE 子句中排除特定值。换句话说，将检查特定表达式中是否缺少列表。

To negate a condition, we use the NOT operator. The SQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause. In other words, the absence of a list from an expression will be checked.

Syntax

接下来是 NOT IN 运算符的基本语法：

Following is the basic syntax of NOT IN operator −

WHERE column_name NOT IN (value1, value2, ...);

Example

现在，我们正在从 CUSTOMERS 表中显示所有记录，其中 AGE 不等于“25”、“23”和“22”

Now, we are displaying all the records from the CUSTOMERS table, where the AGE is NOT equal to '25', '23' and '22' −

SELECT * FROM CUSTOMERS WHERE AGE NOT IN (25, 23, 22);

Output

我们获得的结果如下：

We obtain the result as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

IN Operator with Column Name

我们还可以使用带列名的 SQL IN 运算符将一列的值与另一列进行比较。它用于选择特定值存在于给定列中的行。

We can also use the SQL IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.

Example

在下面的查询中，我们正在选择 SALARY 列中值为“2000”的行：

In the below query, we are selecting the rows with the value '2000' in the SALARY column −

SELECT * FROM CUSTOMERS WHERE 2000 IN (SALARY);

Output

这将产生以下结果 -

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00

Subquery with IN Operator

我们可以使用 IN 运算符的子查询来返回单列中的记录。这意味着 SELECT 列列表中的多列不能包含在子查询中。

We can use the subquery with the IN operator that is used to return records from the single column. This means that more than one column in the SELECT column list cannot be included in the subquery.

Syntax

IN 运算符指定子查询的基本语法如下：

The basic syntax of the IN operator to specify a subquery is as follows −

WHERE column_name IN (subquery);

其中，

Where,

Subquery − This is the SELECT statement that has a result set to be tested against the expression. The IN condition evaluates to true if any of these values match the expression.

Example

在下面给出的查询中，我们正在从 CUSTOMERS 表中显示所有记录，其中客户的 NAME 可以得到大于 2000 的 SALARY

In the query given below, we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −

SELECT * FROM CUSTOMERS
WHERE NAME IN (SELECT NAME FROM CUSTOMERS WHERE SALARY > 2000);

Output

这会产生以下结果 −

This will produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00