SQL 中的in、exists、all、any用法与区别
2019-01-12 16:48:56 
阿炯
在使用数据库SQL遇到比较复杂的情况时，我们需要使用嵌套查询。在嵌套查询是，我们经常会使用到exists、in、any、all等，在这里就来辨析一下它们之间的区别和关系。
any表示有任何一个满足就返回true，all表示全部都满足才返回true。
> select min(hits),max(hits) from content where pid=516;
+-----------+-----------+
| min(hits) | max(hits) |
+-----------+-----------+
| 61 | 1965 |
+-----------+-----------+
> select min(hits),max(hits) from content where pid=515;
+-----------+-----------+
| min(hits) | max(hits) |
+-----------+-----------+
| 1 | 1673 |
+-----------+-----------+
> select cid,issue_dt,pid,hits from content where pid=516 and hits > all (select hits from content where pid=515);
+------+---------------------+-----+------+
| cid | issue_dt | pid | hits |
+------+---------------------+-----+------+
| 1274 | 2009-09-26 18:25:23 | 516 | 1965 |
+------+---------------------+-----+------+
相当于：select cid,issue_dt,pid,hits from content where pid=516 and hits > (select max(hits) from content where pid=515);
如果换成any的话，pid=516下的全部都能出来，因为pid=515中的最小值为1，无论怎样，516中的每一条记录的最小值都大于此值。
相当于：select cid,issue_dt,pid,hits from content where pid=516 and hits > (select min(hits) from content where pid=515);
exists和in：
exists和in都适用于连接子查询的，什么叫子查询呢？就是我们在where语句中嵌套一个select语句。他们之间的区别在于：
1. 使用in的时候，会把子句中的查询作为结果缓存下来，然后对主查询中的每个记录进行查询。
2.使用exists的时候，不在对子查询的结果进行缓存，子查询的返回的结果并不重要。使用exists的时候，我们使先对主查询进行查询，然后根据子查询的结果是否为真来决定是否返回。在使用exists的时候，如果子查询返回结果集的时候为真，主查询返回结果。在使用not exists的时候，如果子查询不返回结果集，那么主查询为真，返回结果。
在网上可以看到这样的结论：in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。另外关于我们在使用not in 和 not exists的时候(当查询的表中有null的情况出现的时候)，有的时候出现查询结果会不同。下文还有具体的讲解。
any和some：
在SQL中any和some是同义词，所以下面介绍的时候只使用any，some的用法和功能和any一模一样。和IN运算符不同，any必须和其他的比较运算符共同使用，而且必须将比较运算符放在any关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是any在英文中所表示的意义。
也就是说"=any"等价于IN 运算符，而"<>any"则等价于NOT IN 运算符。除了等于运算符，any运算符还可以和大于(>)、小于(<)、大于等于(>=)、小于等于(<=)等比较运算符共同使用。
注意，和in运算符不同，any 运算符不能与固定的集合相匹配，比如下面的SQL 语句是错误的：
select cid,title,pid,hits from content where pid = any (512,513);
不过这个限制并不会妨碍功能的实现，因为没有对固定的集合进行any匹配的必要，因为待匹配的集合是固定的，所以上面的SQL语句完全可以用下面的语句来代替：
select cid,title,pid,hits from content where pid <= 514;
可以用 all 或 any 关键字修改引入子查询的比较运算符，some 是 SQL92 标准的 any 的等效物。以 > 比较运算符为例，>all 表示大于每一个值；换句话说，大于最大值，例如，>all (1, 2, 3) 表示大于 3。>any 表示至少大于一个值，也就是大于最小值，因此 >any (1, 2, 3) 表示大于 1。
all 父查询中的结果集大于子查询中每一个结果集中的值，则为真
any,some 父查询中的结果集大于子查询中任意一个结果集中的值，则为真
=any 与子查询in相同
<>any 与not in相同
<>any
--or作用 父查询中的结果集不等于子查询中的a或者b或者c，则为真
not in
--and作用 父查询中的结果集不等于子查询中任意一个结果集中的值，则为真
some：表示满足一个条件即可，就像英语一样，some一般适用于 = 的情况
any：这里的any不能直译为任何，就像英文中一样，这里的any和some一样表示满足一个条件，只是any常用于非"="的情况，例如<>any、>any，好比英文中，any用于疑问句
all：货真价实的所以，要满足子查询中所有的情况
几个常见的等价：
in 和 =any 即满足一个即可，上文已述
not in 和 <> all 即不等于所有，每个都不相等。但是<>any 只要有一个不相等即为true
再谈exists和in：
exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：select name from student where sex = 'm' and mark exists(select 1 from grade where ...)，只要exists引导的子句有结果集返回，那么exists这个条件就算成立了，大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。
而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。
如下面的表fruits：
fid fname fclass fcnt fdate
1 苹果 水果 10 2011-7-1
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 白菜 蔬菜 12 2011-7-1
2 青菜 蔬菜 19 2011-7-2
create table fruits (fid int2,fname char(32),fclass char(32),fcnt int2,fdate date);
insert into fruits values (1,'苹果','水果',10,'2011-7-1'),(1,'桔子','水果',20,'2011-7-2'),(1,'香蕉','水果',15,'2011-7-3'),(2,'白菜','蔬菜',12,'2011-7-1'),(2,'青菜','蔬菜',19,'2011-7-2');
SELECT t.* FROM fruits t
WHERE (NOT EXISTS (SELECT * FROM fruits WHERE fid = t.fid AND fdate > t.fdate));
结果：
1 香蕉 水果 15 2011-07-03
2 青菜 蔬菜 19 2011-07-02
如果用distinct得不到这个结果，因为distinct是作用与所有列的。
如果用Group by也得不到需要的结果，因为Group by 要和聚合函数共同使用，所以对于fname,fclass和fcnt列要么使用Group by要么使用聚合函数，如果写成：SELECT fid, fname, fclass, fcnt, MAX(fdate) FROM fruits GROUP BY fid, fname, fclass, fcnt;
或改写成：SELECT fid, MAX(fname), MAX(fclass), MAX(fcnt), MAX(fdate) FROM fruits GROUP BY fid;
1 | 香蕉| 水果| 20 | 2011-07-03
2 | 青菜| 蔬菜| 19 | 2011-07-02
如果用in有时候也得不到结果(有的时候可以得到，如果fdate都不相同(没有重复数据)，或者是下面得到的Max(fdate)只有一个值)
SELECT DISTINCT fid, fname, fclass, fcnt, fdate FROM fruits
WHERE (fdate IN (SELECT MAX(fdate) FROM fruits GROUP BY fid))
得到的结果是：(因为MAX(fdate)有两个值2011-7-2，2011-7-3)
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 青菜 蔬菜 19 2011-7-2
注意in只允许有一个字段返回。
有一种方法可以实现：
SELECT DISTINCT fid, fname, fclass, fcnt, fdate FROM fruits t WHERE (fdate = (SELECT MAX(fdate) FROM fruits WHERE fid = t.fid))
in和exists的区别分析
select * from A where id in (select id from B);
select * from A where exists (select 1 from B where A.id=B.id);
对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。
IN()语句内部工作原理
IN()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。它的查询过程类似于以下过程：
List resultSet={};
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次
例1：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。
例2：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。
结论：IN()适合B表比A表数据小的情况
EXISTS()语句内部工作原理
exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。它的查询过程类似于以下过程：
List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回
resultSet.add(A[i]);
}
}
return resultSet;
当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。
例1：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
例2：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。
例3：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。
结论：EXISTS()适合B表比A表数据大的情况。
当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录：
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);
EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，但要看实际情况具体使用：
IN适合于外表大而内表小的情况；
EXISTS适合于外表小而内表大的情况。
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。EXISTS 指定一个子查询，检测行的存在。
语法： EXISTS subquery
参数： subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
结果类型：Boolean 如果子查询包含行，则返回 TRUE ，否则返回 FLASE 。
结论：select * from A where exists (select 1 from B where A.id=B.id)
EXISTS(包括 NOT EXISTS )子句的返回值是一个boolean值。EXISTS内部有一个子查询语句(SELECT ... FROM...)。其查询语句返回一个结果集，EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字，如果找不到则返回到SELECT找字段解析，如果找到WHERE，则分析其中的条件，完成后再回到SELECT分析字段。最后形成一张我们要的虚表。
WHERE关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)。同理WHERE后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。
Exists后面的语句执行的结果是不是有记录，只要有记录，则主查询语句就成立。它代表'存在'，用来引领嵌套查询的子查询，它不返回任何数据，只产生逻辑真值'true'与逻辑假值'false'。由EXISTS引出的子查询，其目标列表达式通常都用*(用null也可以)，因为带有EXISTS的子查询只返回真值或假值，给出列名没有实际意义。
exists的优点是：只要存在就返回了，这样的话很有可能不需要扫描整个表。in需要扫描完整个表，并返回结果。所以在表比较小的情况下，扫描全表和部分表基本没有差别；但在大表情况下，exists就会有优势。
对于not in和 not exists的性能区别：
not in只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in，另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in并使用anti hash join。如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists，另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null
NOT IN在基于成本的应用中较好
比如:
select ..... from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);
改成(关联查询)
select ......
from title T, rollup R
where R.source_id = T.Title_id(+)
and T.Title_id is null;
或者(佳)
sql>select /*+ HASH_AJ */ ...
from rollup R
where ource_id NOT IN ( select ource_id
from title T
where ource_id IS NOT NULL )
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用exists(或not exists)通常将提高查询的效率。举例：
(低效)select ... from table1 t1 where t1.id > 10 and pno in (select no from table2 where name like 'www%');
(高效)select ... from table1 t1 where t1.id > 10 and exists (select 1 from table2 t2 where t1.pno = t2.no and name like 'www%');
用not exists替代not in：
在子查询中，not in子句将执行一个内部的排序和合并。无论在哪种情况下，not in都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用not in，我们可以把它改写成外连接(Outer Joins)或not exists。
用exists替换distinct：
当提交一个包含一对多表信息的查询时，避免在select子句中使用distinct，一般可以考虑用exists替换，举例：
(低效)select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
(高效)select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp e where d.dept_no = e.dept_no);
exists使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后立刻返回结果。
用表连接替换exists：
通常来说，采用表连接的方式比exists更有效率。举例：
(低效)select ename from emp e where exists (select 1 from dept where dept_no = e.dept_no and dept_cat = 'W');
(高效)select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = 'W';
对NULL的处理情况
查询的表中有null的情况出现的时候会出现查询结果不同。Null即为我们常说的空，null和任何值比较包括null都是false，一般对null的判断是is null或is not null。需要明确下in与null值判断是返回false的。下面用示例来说明：
create table ta (id int2,name varchar(256));
insert into ta values(1,'a');
insert into ta values(2,'b');
insert into ta values(3,'c');
insert into ta(name) values('d');
create table tb (id int2,name varchar(256));
insert into tb values(1,'aa');
insert into tb values(2,'bb');
我们查询ta表中id值不在tb表中出现的记录，即在主查询中会出现null。
如果使用not in：# select * from ta where ta.id not in (select id from tb);
id | name
----+------
3 | c
如果使用not exists：# select * from ta where not exists (select * from tb where ta.id=tb.id);
id | name
----+------
3 | c
| d
not exists可以理解为：对于A中的 (null,'d') 这条记录，我们在select子句中，由于null和tb.id比较的结果为false，所以子句没有返回结果集；当子句不返回结果集的时候，这时外部的not exists为真，所以返回该条记录。
而对于in，由于null和子句结果中的tb.id相比均为false，所以不返回。同理，我们查询tb表中id值不在ta表中出现的记录，即在子查询中会出现null。
先在tb表中加一条数据：insert into tb values(4,'cc');
使用not in：# select * from tb where tb.id not in (select id from ta);
id | name
----+------
使用not exists：# select * from tb where not exists (select id from ta where ta.id=tb.id);
id | name
----+------
4 | cc
使用not in：这次主查询中的结果是(1,2,4)，子查询是(1,2,3,null)所以当我们对4进行查询的时候，由于4和(1,2,3,null)中的null和4判断的结果，到时该语句返回false，所以4无法返回。
使用not exists：这次主查询中的结果是(1,2,4)，子查询是(1,2,3,null)，当我们对4进行查询的时候，由于4和(1,2,3,null)中的null和4判断的结果，到时该语句返回false，not exists 为真，返回4。
# 小结 #
1、in查询在内部表和外部表上都可以使用到索引；
2、exists查询仅内部表上可以使用到索引，外表会全表扫描；当子查询结果集很大，而外部表较小的时候，exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，查询效率会优于in；
3、当子查询结果集较小，而外部表很大的时候，exists的Block嵌套循环优化效果不明显，in的外表索引优势占主要作用，此时in的查询效率会优于exists。
子查询结果集越大用exists，子查询结果集越小用in。
any表示有任何一个满足就返回true，all表示全部都满足才返回true。
> select min(hits),max(hits) from content where pid=516;
+-----------+-----------+
| min(hits) | max(hits) |
+-----------+-----------+
| 61 | 1965 |
+-----------+-----------+
> select min(hits),max(hits) from content where pid=515;
+-----------+-----------+
| min(hits) | max(hits) |
+-----------+-----------+
| 1 | 1673 |
+-----------+-----------+
> select cid,issue_dt,pid,hits from content where pid=516 and hits > all (select hits from content where pid=515);
+------+---------------------+-----+------+
| cid | issue_dt | pid | hits |
+------+---------------------+-----+------+
| 1274 | 2009-09-26 18:25:23 | 516 | 1965 |
+------+---------------------+-----+------+
相当于：select cid,issue_dt,pid,hits from content where pid=516 and hits > (select max(hits) from content where pid=515);
如果换成any的话，pid=516下的全部都能出来，因为pid=515中的最小值为1，无论怎样，516中的每一条记录的最小值都大于此值。
相当于：select cid,issue_dt,pid,hits from content where pid=516 and hits > (select min(hits) from content where pid=515);
exists和in：
exists和in都适用于连接子查询的，什么叫子查询呢？就是我们在where语句中嵌套一个select语句。他们之间的区别在于：
1. 使用in的时候，会把子句中的查询作为结果缓存下来，然后对主查询中的每个记录进行查询。
2.使用exists的时候，不在对子查询的结果进行缓存，子查询的返回的结果并不重要。使用exists的时候，我们使先对主查询进行查询，然后根据子查询的结果是否为真来决定是否返回。在使用exists的时候，如果子查询返回结果集的时候为真，主查询返回结果。在使用not exists的时候，如果子查询不返回结果集，那么主查询为真，返回结果。
在网上可以看到这样的结论：in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。另外关于我们在使用not in 和 not exists的时候(当查询的表中有null的情况出现的时候)，有的时候出现查询结果会不同。下文还有具体的讲解。
any和some：
在SQL中any和some是同义词，所以下面介绍的时候只使用any，some的用法和功能和any一模一样。和IN运算符不同，any必须和其他的比较运算符共同使用，而且必须将比较运算符放在any关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是any在英文中所表示的意义。
也就是说"=any"等价于IN 运算符，而"<>any"则等价于NOT IN 运算符。除了等于运算符，any运算符还可以和大于(>)、小于(<)、大于等于(>=)、小于等于(<=)等比较运算符共同使用。
注意，和in运算符不同，any 运算符不能与固定的集合相匹配，比如下面的SQL 语句是错误的：
select cid,title,pid,hits from content where pid = any (512,513);
不过这个限制并不会妨碍功能的实现，因为没有对固定的集合进行any匹配的必要，因为待匹配的集合是固定的，所以上面的SQL语句完全可以用下面的语句来代替：
select cid,title,pid,hits from content where pid <= 514;
可以用 all 或 any 关键字修改引入子查询的比较运算符，some 是 SQL92 标准的 any 的等效物。以 > 比较运算符为例，>all 表示大于每一个值；换句话说，大于最大值，例如，>all (1, 2, 3) 表示大于 3。>any 表示至少大于一个值，也就是大于最小值，因此 >any (1, 2, 3) 表示大于 1。
all 父查询中的结果集大于子查询中每一个结果集中的值，则为真
any,some 父查询中的结果集大于子查询中任意一个结果集中的值，则为真
=any 与子查询in相同
<>any 与not in相同
<>any
--or作用 父查询中的结果集不等于子查询中的a或者b或者c，则为真
not in
--and作用 父查询中的结果集不等于子查询中任意一个结果集中的值，则为真
some：表示满足一个条件即可，就像英语一样，some一般适用于 = 的情况
any：这里的any不能直译为任何，就像英文中一样，这里的any和some一样表示满足一个条件，只是any常用于非"="的情况，例如<>any、>any，好比英文中，any用于疑问句
all：货真价实的所以，要满足子查询中所有的情况
几个常见的等价：
in 和 =any 即满足一个即可，上文已述
not in 和 <> all 即不等于所有，每个都不相等。但是<>any 只要有一个不相等即为true
再谈exists和in：
exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：select name from student where sex = 'm' and mark exists(select 1 from grade where ...)，只要exists引导的子句有结果集返回，那么exists这个条件就算成立了，大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。
而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。
如下面的表fruits：
fid fname fclass fcnt fdate
1 苹果 水果 10 2011-7-1
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 白菜 蔬菜 12 2011-7-1
2 青菜 蔬菜 19 2011-7-2
create table fruits (fid int2,fname char(32),fclass char(32),fcnt int2,fdate date);
insert into fruits values (1,'苹果','水果',10,'2011-7-1'),(1,'桔子','水果',20,'2011-7-2'),(1,'香蕉','水果',15,'2011-7-3'),(2,'白菜','蔬菜',12,'2011-7-1'),(2,'青菜','蔬菜',19,'2011-7-2');
SELECT t.* FROM fruits t
WHERE (NOT EXISTS (SELECT * FROM fruits WHERE fid = t.fid AND fdate > t.fdate));
结果：
1 香蕉 水果 15 2011-07-03
2 青菜 蔬菜 19 2011-07-02
如果用distinct得不到这个结果，因为distinct是作用与所有列的。
如果用Group by也得不到需要的结果，因为Group by 要和聚合函数共同使用，所以对于fname,fclass和fcnt列要么使用Group by要么使用聚合函数，如果写成：SELECT fid, fname, fclass, fcnt, MAX(fdate) FROM fruits GROUP BY fid, fname, fclass, fcnt;
或改写成：SELECT fid, MAX(fname), MAX(fclass), MAX(fcnt), MAX(fdate) FROM fruits GROUP BY fid;
1 | 香蕉| 水果| 20 | 2011-07-03
2 | 青菜| 蔬菜| 19 | 2011-07-02
如果用in有时候也得不到结果(有的时候可以得到，如果fdate都不相同(没有重复数据)，或者是下面得到的Max(fdate)只有一个值)
SELECT DISTINCT fid, fname, fclass, fcnt, fdate FROM fruits
WHERE (fdate IN (SELECT MAX(fdate) FROM fruits GROUP BY fid))
得到的结果是：(因为MAX(fdate)有两个值2011-7-2，2011-7-3)
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 青菜 蔬菜 19 2011-7-2
注意in只允许有一个字段返回。
有一种方法可以实现：
SELECT DISTINCT fid, fname, fclass, fcnt, fdate FROM fruits t WHERE (fdate = (SELECT MAX(fdate) FROM fruits WHERE fid = t.fid))
in和exists的区别分析
select * from A where id in (select id from B);
select * from A where exists (select 1 from B where A.id=B.id);
对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。
IN()语句内部工作原理
IN()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。它的查询过程类似于以下过程：
List resultSet={};
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次
例1：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。
例2：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。
结论：IN()适合B表比A表数据小的情况
EXISTS()语句内部工作原理
exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。它的查询过程类似于以下过程：
List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回
resultSet.add(A[i]);
}
}
return resultSet;
当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。
例1：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
例2：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。
例3：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。
结论：EXISTS()适合B表比A表数据大的情况。
当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录：
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);
EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，但要看实际情况具体使用：
IN适合于外表大而内表小的情况；
EXISTS适合于外表小而内表大的情况。
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。EXISTS 指定一个子查询，检测行的存在。
语法： EXISTS subquery
参数： subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
结果类型：Boolean 如果子查询包含行，则返回 TRUE ，否则返回 FLASE 。
结论：select * from A where exists (select 1 from B where A.id=B.id)
EXISTS(包括 NOT EXISTS )子句的返回值是一个boolean值。EXISTS内部有一个子查询语句(SELECT ... FROM...)。其查询语句返回一个结果集，EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字，如果找不到则返回到SELECT找字段解析，如果找到WHERE，则分析其中的条件，完成后再回到SELECT分析字段。最后形成一张我们要的虚表。
WHERE关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)。同理WHERE后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。
Exists后面的语句执行的结果是不是有记录，只要有记录，则主查询语句就成立。它代表'存在'，用来引领嵌套查询的子查询，它不返回任何数据，只产生逻辑真值'true'与逻辑假值'false'。由EXISTS引出的子查询，其目标列表达式通常都用*(用null也可以)，因为带有EXISTS的子查询只返回真值或假值，给出列名没有实际意义。
exists的优点是：只要存在就返回了，这样的话很有可能不需要扫描整个表。in需要扫描完整个表，并返回结果。所以在表比较小的情况下，扫描全表和部分表基本没有差别；但在大表情况下，exists就会有优势。
对于not in和 not exists的性能区别：
not in只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in，另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in并使用anti hash join。如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists，另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null
NOT IN在基于成本的应用中较好
比如:
select ..... from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);
改成(关联查询)
select ......
from title T, rollup R
where R.source_id = T.Title_id(+)
and T.Title_id is null;
或者(佳)
sql>select /*+ HASH_AJ */ ...
from rollup R
where ource_id NOT IN ( select ource_id
from title T
where ource_id IS NOT NULL )
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用exists(或not exists)通常将提高查询的效率。举例：
(低效)select ... from table1 t1 where t1.id > 10 and pno in (select no from table2 where name like 'www%');
(高效)select ... from table1 t1 where t1.id > 10 and exists (select 1 from table2 t2 where t1.pno = t2.no and name like 'www%');
用not exists替代not in：
在子查询中，not in子句将执行一个内部的排序和合并。无论在哪种情况下，not in都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用not in，我们可以把它改写成外连接(Outer Joins)或not exists。
用exists替换distinct：
当提交一个包含一对多表信息的查询时，避免在select子句中使用distinct，一般可以考虑用exists替换，举例：
(低效)select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
(高效)select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp e where d.dept_no = e.dept_no);
exists使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后立刻返回结果。
用表连接替换exists：
通常来说，采用表连接的方式比exists更有效率。举例：
(低效)select ename from emp e where exists (select 1 from dept where dept_no = e.dept_no and dept_cat = 'W');
(高效)select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = 'W';
对NULL的处理情况
查询的表中有null的情况出现的时候会出现查询结果不同。Null即为我们常说的空，null和任何值比较包括null都是false，一般对null的判断是is null或is not null。需要明确下in与null值判断是返回false的。下面用示例来说明：
create table ta (id int2,name varchar(256));
insert into ta values(1,'a');
insert into ta values(2,'b');
insert into ta values(3,'c');
insert into ta(name) values('d');
create table tb (id int2,name varchar(256));
insert into tb values(1,'aa');
insert into tb values(2,'bb');
我们查询ta表中id值不在tb表中出现的记录，即在主查询中会出现null。
如果使用not in：# select * from ta where ta.id not in (select id from tb);
id | name
----+------
3 | c
如果使用not exists：# select * from ta where not exists (select * from tb where ta.id=tb.id);
id | name
----+------
3 | c
| d
not exists可以理解为：对于A中的 (null,'d') 这条记录，我们在select子句中，由于null和tb.id比较的结果为false，所以子句没有返回结果集；当子句不返回结果集的时候，这时外部的not exists为真，所以返回该条记录。
而对于in，由于null和子句结果中的tb.id相比均为false，所以不返回。同理，我们查询tb表中id值不在ta表中出现的记录，即在子查询中会出现null。
先在tb表中加一条数据：insert into tb values(4,'cc');
使用not in：# select * from tb where tb.id not in (select id from ta);
id | name
----+------
使用not exists：# select * from tb where not exists (select id from ta where ta.id=tb.id);
id | name
----+------
4 | cc
使用not in：这次主查询中的结果是(1,2,4)，子查询是(1,2,3,null)所以当我们对4进行查询的时候，由于4和(1,2,3,null)中的null和4判断的结果，到时该语句返回false，所以4无法返回。
使用not exists：这次主查询中的结果是(1,2,4)，子查询是(1,2,3,null)，当我们对4进行查询的时候，由于4和(1,2,3,null)中的null和4判断的结果，到时该语句返回false，not exists 为真，返回4。
# 小结 #
1、in查询在内部表和外部表上都可以使用到索引；
2、exists查询仅内部表上可以使用到索引，外表会全表扫描；当子查询结果集很大，而外部表较小的时候，exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，查询效率会优于in；
3、当子查询结果集较小，而外部表很大的时候，exists的Block嵌套循环优化效果不明显，in的外表索引优势占主要作用，此时in的查询效率会优于exists。
子查询结果集越大用exists，子查询结果集越小用in。