Oracle中In函数的使用

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,?或者value_n的记录。IN函数可以用于任何合法的SQL语句中－select,?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,?或者?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,?or?10005.

译：将返回所有order_id是10000,?或者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‘,?‘Microsoft‘);

This?would?return?all?rows?where?the?supplier_name?is?neither?IBM,?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,及Microsoft的所有记录。有时，与你想要的数据相反，这样可以更有效的例出你不需要的值。

如果文章对你用，请支持万事如意网址导航。

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!