SQL: "IN" Function
The IN function helps reduce the need to use multipleORconditions.
译:IN函数有助于减少OR条件的复合使用。
The syntax for the IN function is:
译:IN函数的语法:
SELECT columnsFROM tablesWHERE 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 supplierWHERE 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 supplierWHERE 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 ordersWHERE 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 ordersWHERE order_id = 10000OR order_id = 10001OR order_id = 10003OR order_id = 10005;
Example #3 - "NOT IN"
The IN function can also be combined with the NOT operator.
译:IN函数可以和NOT操作符连用
For example,
SELECT *FROM supplierWHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');
This would return all rows where the supplier_name isneitherIBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you donotwant, as opposed to the values that you do want.
译:这将返回supplier_name不是IBM, Hewlett Packard,及Microsoft的所有记录。有时,与你想要的数据相反,这样可以更有效的例出你不需要的值。
如果文章对你用,请支持万事如意网址导航。