exists和not exists的嵌套使用及求关系的除法

（用 exists 和 not exists可以求集合的交集(intersection)和差集(except)，请参考另一份如名文档）


（本文中所有SQL语句均在Mysql v5.1中验证）


初学SQL，遇到exist和not exist，看了一会弄明白了，结果发现还有他们的嵌套使用，终于又懵了，看了很长时间才摸出来个大概，下面用几个例子分析一下，
便于以后忘记时帮助理解。








题外：首先说一下not exists的用法。
exists是存在一个即可返回真，not exists是全都不存在才返回真
mysql> select * from t1;
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)


mysql> select * from t2;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |     10 |    100 |
|  2 |   NULL |    100 |
|  3 |      5 |     80 |
|  4 |     11 |     11 |
|  6 |      5 |     80 |
|  7 |      5 |      5 |
+----+--------+--------+
6 rows in set (0.00 sec)


mysql> select * from t1 where not exists (select * from t2 where value1=t1.value1);
+----+--------+
| id | value1 |
+----+--------+
|  2 |   NULL |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)






进入正题：
example 1：（参考 http://blog.csdn.net/mtawaken/article/details/6573122，加以修改，变成适合自己理解的方式）


关系模式：
student(学号stuid，姓名stdname，年龄age)
course (课程号cid，课程名cname，学时hour)
choosed(学号stuid，课程号cid，成绩score)：代表学号为stuid的学生选了课程cid，成绩为score


mysql> select * from student;
+-------+---------+------+
| stuid | stdname | age  |
+-------+---------+------+
|     1 | Bob     |   23 |
|     2 | Jessy   |   24 |
|     3 | Ivy     |   24 |
|     4 | Grace   |   25 |
|     5 | Candise |   26 |
|     6 | Angela  |   25 |
|     7 | Tracy   |   27 |
|     8 | Sun     |   26 |
|     9 | Kevin   |   26 |
|    10 | Richard |   26 |
+-------+---------+------+
10 rows in set (0.00 sec)


mysql> select * from course;
+-----+----------+------+
| cid | cname    | hour |
+-----+----------+------+
| 101 | English  |  108 |
| 102 | Math     |  136 |
| 103 | Chinese  |  110 |
| 104 | Music    |   48 |
| 105 | Painting |   56 |
+-----+----------+------+
5 rows in set (0.00 sec)


mysql> select * from choosed;
+-------+------+-------+
| stuid | cid  | score |
+-------+------+-------+
|     1 |  101 |    80 |
|     1 |  102 |    95 |
|     1 |  103 |    88 |
|     1 |  104 |    70 |
|     1 |  105 |    98 |
|     2 |  103 |    84 |
|     2 |  105 |    79 |
|     3 |  101 |    95 |
|     3 |  102 |    84 |
|     3 |  103 |    91 |
|     3 |  104 |    85 |
|     3 |  105 |    79 |
|     4 |  101 |    94 |
|     4 |  104 |    86 |
|     6 |  101 |    85 |
|     6 |  102 |    82 |
|     6 |  103 |    91 |
|     6 |  104 |    79 |
|     6 |  105 |    81 |
|     7 |  103 |    92 |
|    10 |  102 |    87 |
|    10 |  104 |    68 |
+-------+------+-------+
22 rows in set (0.00 sec)






1)所有选了课（至少一节）的人的姓名 exists -> exists
mysql> select stuid, stdname from student where exists (select * from course where exists (select * from choosed where stuid=student.stuid and cid=course.cid));
+-------+---------+
| stuid | stdname |
+-------+---------+
|     1 | Bob     |
|     2 | Jessy   |
|     3 | Ivy     |
|     4 | Grace   |
|     6 | Angela  |
|     7 | Tracy   |
|    10 | Richard |
+-------+---------+
7 rows in set (0.00 sec)


2)所有没选课（一节课都没选）的人的姓名  not exists -> exists
mysql> select stuid, stdname from student where not exists (select * from course where exists (select * from choosed where stuid=student.stuid and cid=course.cid));
+-------+---------+
| stuid | stdname |
+-------+---------+
|     5 | Candise |
|     8 | Sun     |
|     9 | Kevin   |
+-------+---------+
3 rows in set (0.00 sec)


3)所有没选全部课程的人的姓名 exists -> not exists
mysql> select stuid, stdname from student where exists (select * from course where not exists (select * from choosed where stuid=student.stuid and cid=course.cid));
+-------+---------+
| stuid | stdname |
+-------+---------+
|     2 | Jessy   |
|     4 | Grace   |
|     5 | Candise |
|     7 | Tracy   |
|     8 | Sun     |
|     9 | Kevin   |
|    10 | Richard |
+-------+---------+
7 rows in set (0.00 sec)


4)所有选了全部课程的人的姓名 not exists -> not exists
mysql> select stuid, stdname from student where not exists (select * from course where not exists (select * from choosed where stuid=student.stuid and cid=course.cid));
+-------+---------+
| stuid | stdname |
+-------+---------+
|     1 | Bob     |
|     3 | Ivy     |
|     6 | Angela  |
+-------+---------+
3 rows in set (0.00 sec)




1)首先分析一下简单的情况（双exists嵌套）：
mysql> select stuid, stdname from student where exists (select * from course where exists (select * from choosed where stuid=student.stuid and cid=course.cid));


i.所有被选过的课程的数据集：
select * from course where exists(select * from choosed where cid=course.cid);


ii.所有被某位学号为 @stuid 的学生选过的课程的记录集（@stuid学生的已选课程）：
select * from course where exists(select * from choosed where stuid=@stuid and cid=course.cid);
请注意，多出了学号的筛选即，stuid=@stuid


iii.遍历每一个主查询的学号，每一个学号都按第二筛选方法筛选出：已选课的学生的学号。
请注意：用主查询中的 student.stuid 代替了@stuid。


select stuid, stdname from student where exists( (1)
select * from course where exists( (2)
select * from choosed where stuid=student.stuid and cid=course.cid)) (3)


这已经是整个查询语句，可以看出子查询中用student.stuid替换了第2步中的 @stuid，（没忘了吧，第二步求的是选过了课程的某个学生）。


实际上此句的功能就是，针对每个student中的学生s，把s和每个course中的课程c组成元组对(s,c)，把(s,c)放到已选课程choosed中进行匹配，如果存在记录c'，则说明学生s选择了某门课程c'，
于是，对这个确定的学生s，结果集(select * from choosed...)会返回一条包含元组(s,c')的记录，从而(2)处的exists会返回真，
则对这个确定的学生s，结果集(select * from course...)会因为(2)处的where为真，而返回这个学生s所选择的所有课程，如果课程不为空的话，(1)处的exists返回真，否则为假。
当(1)处的exists为真时，最后的结果集(select student.stdname from student...)中就应该出现学生s的记录，代表这个学生选择了至少一门课。


于是，根据这个分析，可以把这个双exists嵌套的语句改写成如下方式：
mysql> select distinct stuid, stdname from student, course where (student.stuid, course.cid) in (select stuid, cid from choosed);
+-------+---------+
| stuid | stdname |
+-------+---------+
|     1 | Bob     |
|     2 | Jessy   |
|     3 | Ivy     |
|     4 | Grace   |
|     6 | Angela  |
|     7 | Tracy   |
|    10 | Richard |
+-------+---------+
7 rows in set (0.00 sec)


或者改成更为简单的方式：
mysql> select distinct student.stuid, stdname from student, choosed where student.stuid=choosed.stuid;
+-------+---------+
| stuid | stdname |
+-------+---------+
|     1 | Bob     |
|     2 | Jessy   |
|     3 | Ivy     |
|     4 | Grace   |
|     6 | Angela  |
|     7 | Tracy   |
|    10 | Richard |
+-------+---------+
7 rows in set (0.00 sec)


或者改成使用自然连接的方式：
mysql> select distinct stuid, stdname from student natural join choosed;
+-------+---------+
| stuid | stdname |
+-------+---------+
|     1 | Bob     |
|     2 | Jessy   |
|     3 | Ivy     |
|     4 | Grace   |
|     6 | Angela  |
|     7 | Tracy   |
|    10 | Richard |
+-------+---------+
7 rows in set (0.00 sec)






2)再分析一下比较复杂的情况（双 not exists 嵌套）：
select stuid, stdname from student where not exists (select * from course where not exists (select * from choosed where stuid=student.stuid and cid=course.cid));


i.所有没被选过的课程的数据集：
select * from course where not exists(select * from choosed where cid=course.cid);


ii.所有没被某位学号为 @stuid 的学生选过的课程的记录集（@stuid学生的未选课程）：
select * from course where not exists(select * from choosed where stuid=@stuid and cid=course.cid);
请注意，多出了学号的筛选即，stuid=@stuid


iii.遍历每一个主查询的学号，每一个学号都按第二筛选方法筛选出：已选全部课的学生的学号。
请注意：用主查询中的 student.stuid 代替了@stuid。


select stuid, stdname from student where not exists ( (1)
select * from course where not exists ( (2)
select * from choosed where stuid=student.stuid and cid=course.cid)); (3)


这已经是整个查询语句，可以看出子查询中用student.stuid替换了第2步中的 @stuid，（没忘了吧，第二步求的是没有选修全部课程的某个学生）。


实际上此句的功能就是，针对每个student中的学生s，把s和每个course中的课程c组成元组对(s,c)，把(s,c)放到已选课程choosed中进行匹配，如果不存在记录c'，则说明学生s没有选择某门课程c’。
于是，对这个确定的学生s，结果集(select * from choosed...)中不会存在一条包含元组(s,c’)的记录，即(s,c')使得(2)处的not exists会返回真，从而该课程会出现在(select * from course...)
的结果集中，表示该学生s没有选择该门课程c'；否则，若(2)处的not exists返回假，则说明学生s选择了该课程c'。
针对某个确定的学生s，对所有的课程c进行操作(2)，根据(select * from course...)的结果集以判断那些课程是学生s没选的。如果(select * from course...)的结果集为空，则说明该学生s没有任何
一门课程没选，即他选择了所有课程。同时(1)处的not exists会返回真，(select stuid, stdname from student)也会因为(1)处的not exists为真而把该学生s'放入其结果集中，代表该学生选择了所有课程。
于是对这个确定的学生s，结果集(select * from course...)会因为(2)处where的结果，而返回这个学生s没选择的所有课程，如果课程不为空的话，(1)处的not exists返回假，否则为真，“真”即代表学生s选择了所有课程。
最后再根据上述分析对所有学生进行如上操作，从而得出所有选择了所有课程的学生。


（注1：）针对此问题（双 not exists 嵌套），我还想出了下面的方式，但该方式不具有一般性，不过可以根据不同情况加以适当的修改，使其能用在别的地方。
该方法是先对choosed表中的数据按stuid分组，计算出每组所含有的数据数量Ci(i=1,2...)，然后再计算出course中数据的数量C，找出那些满足Ci=C的元组的stuid，然后根据这些stuid找出所有学生信息。
mysql> select distinct * from student where stuid in
(select distinct stuid 
from (select stuid, count(*) as amount from choosed group by stuid) as result
where amount=(select count(*) from course));










实际上，还可以根据sql中exist和not exist的使用，运用逻辑的方式得出，上述四种情况分别代表的含义，
exists -> exists 所有选了课（至少一节）的人的姓名 vs not exists -> exists 所有没选课（一节课都没选）的人的姓名 
exists -> not exists 所有没选全部课程的人的姓名 vs not exists -> not exists 所有选了全部课程的人的姓名 








更进一步，根据2)中的分析，可以看出来实际上关系代数中的除法可以用此法(双not exists嵌套)解决，原因如下：
首先看一下双not exists嵌套的sql语句
select * from student where not exists ( (1)
select * from course where not exists ( (2)
select * from choosed where stuid=student.stuid and cid=course.cid)); (3)
实际上该sql语句做的就是用表choosed（第3个select语句中指定的表）中的某些属性集A（第3个select语句中where后指定的属性）做被除数，
用表courese（第2个select语句中指定的表）中的某些属性集B做除数，这个属性集应该是A的真子集。
需要得到的结果是表student（第1个select语句中指定的表）中的某些属性集C。   即 A ÷ B => C


实际上就是要求出A中的某些实例，这些实例要满足条件：他们都所有干了B中的所有事情，而且已知对应的结果存在C中，反过来求A中满足条件的这些实例。


于是我们可以把上面的问题转化为一个除法问题，即已知：
a)选课信息表choosed，其中给出了所有已选课程(cid)和选择该课的学生的学号(stuid)的对应关系；
b)课程信息表course；
c)学生信息表student；
需要求出所有选了所有课程的学生的信息。
此时恰好可以用双not exists嵌套解决，sql就是上面的4)号语句。








说起来可能还是比较抽象，下面给出一个不是那么直观的但仍可以使用此法解决的例子2，
example 2：（参考：http://www.blogjava.net/decode360/archive/2009/06/12/282804.html，用不同于该文中的另一种方式说明解决方案）
（注2：）此问题就不能用example 1中的（注1）的方法解决。


已知关系模式：tt(person，event)
tt表中person代表人员编号，event代表事件编号。要求出tt表中所有"包含人员2所对应的所有事件"的人员编号。


mysql> select * from tt;
+--------+-------+
| person | event |
+--------+-------+
|      1 |     1 |
|      1 |     2 |
|      1 |     3 |
|      2 |     2 |
|      2 |     3 |
|      4 |     1 |
|      4 |     2 |
|      4 |     3 |
|      5 |     1 |
|      5 |     6 |
|      6 |     1 |
+--------+-------+
11 rows in set (0.00 sec)


实际上可以把该问题中涉及的表分成如下三个：
a)人员和事件的对应关系表tt（为了避免重名，将之改名为tt3）；
b)人员2所对应的所有事件表tt2 由（select event from tt where person='2'）而得，将之命名为tt2；
mysql> select event from tt where person='2';
+-------+
| event |
+-------+
|     2 |
|     3 |
+-------+
2 rows in set (0.00 sec)
c)包含所有人员的表tt1，由（select distinct person from tt）而得，将之命名为tt1；
mysql> select distinct person from tt;
+--------+
| person |
+--------+
|      1 |
|      2 |
|      4 |
|      5 |
|      6 |
+--------+
5 rows in set (0.00 sec)
于是，问题可以变成求 tt3 ÷ tt2 = tt1' (tt1'包含于tt1)，下面给出了3个解决此问题的sql语句，实际上都差不多，只是在后两个中加入了as命名，将问题修改成更容易直观看出来的除法问题了。
尤其在 method 3 中，实际上所执行的sql语句就是 
select * from tt1 where not exists (
select * from tt2 where not exists (
select * from tt3 where tt3.event=tt2.event and tt3.person=tt1.person));
正是上面分析的除法的形式。


method 1:
mysql> select distinct person from tt tt1 where not exists (select * from tt tt2 where person='2' and not exists (select * from tt tt3 where tt3.person=tt1.person and tt3.event=tt2.event));
+--------+
| person |
+--------+
|      1 |
|      2 |
|      4 |
+--------+
3 rows in set (0.00 sec)


method 2:
mysql> select distinct person from tt tt1 where not exists (select * from (select event from tt where person='2') as tt2 where not exists (select * from tt tt3 where tt3.event=tt2.event and tt3.person=tt1.person));
+--------+
| person |
+--------+
|      1 |
|      2 |
|      4 |
+--------+
3 rows in set (0.00 sec)


method 3:
mysql> select * from (select distinct person from tt) as tt1 where not exists (select * from (select event from tt where person='2') as tt2 where not exists (select * from tt tt3 where tt3.event=tt2.event and tt3.person=tt1.person));
+--------+
| person |
+--------+
|      1 |
|      2 |
|      4 |
+--------+
3 rows in set (0.00 sec)




example 3:
关系模式：
tt1(姓名name，年龄age，性别gender)
tt2(账户号account_id，资产asset，支行branch)
tt3(姓名name，账户号account_id)：代表姓名为name的客户拥有账户account_id


mysql> select * from tt1;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| Bob  |   30 | M      |
| Jack |   48 | M      |
| Ivy  |   27 | F      |
| Sun  |   19 | F      |
| Alex |   52 | M      |
+------+------+--------+
5 rows in set (0.00 sec)




mysql> select * from tt2;
+------------+-------+--------+
| account_id | asset | branch |
+------------+-------+--------+
| A1         |   520 | BJ     |
| A2         |   180 | TJ     |
| A3         |    34 | SH     |
| A4         |   178 | GZ     |
| A5         |    10 | NJ     |
| A6         |   101 | TJ     |
| A7         |    19 | TJ     |
| A8         |    32 | BJ     |
| A9         |   290 | GZ     |
| A10        |   111 | NJ     |
+------------+-------+--------+
10 rows in set (0.00 sec)


mysql> select * from tt3;
+------+------------+
| name | account_id |
+------+------------+
| Bob  | A1         |
| Bob  | A2         |
| Bob  | A3         |
| Bob  | A4         |
| Bob  | A5         |
| Bob  | A6         |
| Bob  | A7         |
| Bob  | A8         |
| Bob  | A9         |
| Bob  | A10        |
| Ivy  | A7         |
+------+------------+
11 rows in set (0.00 sec)




mysql> select distinct tt1.* from tt1,tt2 where (tt1.name, tt2.account_id) in (select * from tt3);
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| Bob  |   30 | M      |
| Ivy  |   27 | F      |
+------+------+--------+
2 rows in set (0.00 sec)




所有开了账户的人的资料   exists -> exists
mysql> select distinct * from tt1 where exists (select * from tt2 where exists (select * from tt3 where tt3.name=tt1.name and tt3.account_id=tt2.account_id));
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| Bob  |   30 | M      |
| Ivy  |   27 | F      |
+------+------+--------+
2 rows in set (0.00 sec)




所有没开账户的人的资料    not exists -> exists
mysql> select distinct * from tt1 where not exists (select * from tt2 where exists (select * from tt3 where tt3.name=tt1.name and tt3.account_id=tt2.account_id));
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| Jack |   48 | M      |
| Sun  |   19 | F      |
| Alex |   52 | M      |
+------+------+--------+
3 rows in set (0.00 sec)




所有没开所有账户的人的资料  exists -> not exists
mysql> select distinct * from tt1 where exists (select * from tt2 where not exists (select * from tt3 where tt3.name=tt1.name and tt3.account_id=tt2.account_id));
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| Jack |   48 | M      |
| Ivy  |   27 | F      |
| Sun  |   19 | F      |
| Alex |   52 | M      |
+------+------+--------+
4 rows in set (0.00 sec)




所有开了所有账户的人的资料  not exists -> not exists
mysql> select distinct * from tt1 where not exists (select * from tt2 where not exists (select * from tt3 where tt3.name=tt1.name and tt3.account_id=tt2.account_id));
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| Bob  |   30 | M      |
+------+------+--------+
1 row in set (0.00 sec)










有两个问题：
1)什么时候采用这种嵌套方式，如何能看出某问题可以采用这种方式解决；
2)有没有其他的替代方法；


总结一下，关于(not) exist的四种嵌套方式：
exists -> exists：这种方式所解决的问题其实可以用其他很多方式完成，而且往往遇到此类问题时，更容易想到的并不是双exists嵌套，而是类似于join的方式，可参考example 1中1)的分析。
not exists -> exists：由于它与exists -> exists所得结果是互为补集，所以可以用全集合exists -> exists的结果做差即可得到结果。
not exists -> not exists：这个是求关系代数中的除法最常用的方法，分析过程可参考example 2，所以遇到需要做除法的问题时可以用此not exists嵌套的方式解决。
not exists -> exists：由于它与not exists -> not exists所得结果是互为补集，所以可以用全集合not exists -> not exists的结果做差即可得到结果。
其实最简单的就是记住这四种方式所能求得的结果的含义，即：
exists -> exists 所有干了某事（干了至少一件事）的某物 vs not exists -> exists 所有没干某事（一件事都没干）的某物
exists -> not exists 所有没干全部事情的某物 vs not exists -> not exists 所有干了全部事情的某物 














一个比较简单的问题：将表t中数据按id分组，并求出每组中距离当前时间最近的所有数据。
（参考：http://www.cnblogs.com/mytechblog/articles/2105785.html 原文中给出的方法不太直观，个人选择了另一种方法，列举如下，比较简单，不加以说明了）




mysql> select * from t;
+------+------------+-------+---------------------+
| id   | name       | count | date                |
+------+------------+-------+---------------------+
|    1 | apple      |    10 | 2011-07-01 00:00:00 |
|    1 | orange     |    20 | 2011-07-02 00:00:00 |
|    1 | banana     |    15 | 2011-07-03 00:00:00 |
|    2 | white vege |    12 | 2011-07-01 00:00:00 |
|    2 | blue vege  |    19 | 2011-07-02 00:00:00 |
|    2 | red vege   |    18 | 2011-07-02 00:00:00 |
+------+------------+-------+---------------------+
6 rows in set (0.00 sec)


mehtod 1:
mysql> select * from t where (id, date) in (select id, max(date) from t group by id);
+------+-----------+-------+---------------------+
| id   | name      | count | date                |
+------+-----------+-------+---------------------+
|    1 | banana    |    15 | 2011-07-03 00:00:00 |
|    2 | blue vege |    19 | 2011-07-02 00:00:00 |
|    2 | red vege  |    18 | 2011-07-02 00:00:00 |
+------+-----------+-------+---------------------+
3 rows in set (0.00 sec)


method 2:
mysql> select * from t a where (not exists (select * from t where id=a.id and date>a.date));
+------+-----------+-------+---------------------+
| id   | name      | count | date                |
+------+-----------+-------+---------------------+
|    1 | banana    |    15 | 2011-07-03 00:00:00 |
|    2 | blue vege |    19 | 2011-07-02 00:00:00 |
|    2 | red vege  |    18 | 2011-07-02 00:00:00 |
+------+-----------+-------+---------------------+
3 rows in set (0.00 sec)