SQL: "IN" Function

 

SQL: "IN" Function

---

The IN function helps reduce the need to use multiple OR conditions.

译：IN函数有助于减少OR条件的复合使用。

The syntax for the IN function is:

译：IN函数的语法：

SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);

This SQL statement will return the records where column1 is value1, value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.

译：该SQL语句将返回column1的值是value1, value2..., 或者value_n的记录。IN函数可以用于任何合法的SQL语句中－select, insert, update, or delete。

Example #1

The following is an SQL statement that uses the IN function:

译：下面是一个使用IN函数的SQL语句

SELECT *
FROM supplier
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the supplier table would appear in the result set.

译：这将返回supplier_name为IBM, Hewlett Packard, 或者 Microsoft的所有记录。因为在SELECT中使用了*，supplier表中所有的字段都会显示在结果集中。

It is equivalent to the following statement:

译：与下面的SQL语句相同：

SELECT *
FROM supplier
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the IN function makes the statement easier to read and more efficient.

译：正如你所看到的，使用IN函数使语句更容易读并且有更高的执行效率。

Example #2

You can also use the IN function with numeric values.

译：你也可以同数字使用IN函数

SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);

This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

译：将返回所有order_id是10000, 10001, 10003, 或者10005的记录

It is equivalent to the following statement:

译：与下面的SQL语句相同：

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

 

Example #3 - "NOT IN"

The IN function can also be combined with the NOT operator.

译：IN函数可以和NOT操作符连用

For example,

SELECT *
FROM supplier
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

译：这将返回supplier_name不是IBM, Hewlett Packard,及Microsoft的所有记录。有时，与你想要的数据相反，这样可以更有效的例出你不需要的值。