用 exists 和 not exists求集合的交集(intersection)和差集(except)及除法
(本文中所有SQL语句均在Mysql v5.1中验证)
求交集intersection和差集except可以用 (A in B 或 A exists B 或 =some)和 (A not in B 或 A not exists B 或 <>all)处理:如下
mysql> select * from a;
+------+------+---------------------+
| id | name | Date |
+------+------+---------------------+
| 1 | A1 | 2011-07-01 00:00:00 |
| 2 | A2 | NULL |
| 3 | A3 | NULL |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from b;
+------+------+------+
| id | aid | name |
+------+------+------+
| 1 | 1 | B1 |
| 2 | 2 | B2 |
| 3 | 2 | B3 |
+------+------+------+
3 rows in set (0.00 sec)
用exists求表a的id列和表b的aid列的交集(a.id ∩ b.aid)。
mysql> select id from a where exists (select * from b where a.id=b.aid);
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
用exists求表a的id列和表b的aid列的差集(a.id - b.aid):
mysql> select id from a where not exists (select * from b where a.id=b.aid);
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
求除法请参考另外一份文档:exists和not exists的嵌套使用及求关系的除法.txt
(本文中所有SQL语句均在Mysql v5.1中验证)
求交集intersection和差集except可以用 (A in B 或 A exists B 或 =some)和 (A not in B 或 A not exists B 或 <>all)处理:如下
mysql> select * from a;
+------+------+---------------------+
| id | name | Date |
+------+------+---------------------+
| 1 | A1 | 2011-07-01 00:00:00 |
| 2 | A2 | NULL |
| 3 | A3 | NULL |
+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from b;
+------+------+------+
| id | aid | name |
+------+------+------+
| 1 | 1 | B1 |
| 2 | 2 | B2 |
| 3 | 2 | B3 |
+------+------+------+
3 rows in set (0.00 sec)
用exists求表a的id列和表b的aid列的交集(a.id ∩ b.aid)。
mysql> select id from a where exists (select * from b where a.id=b.aid);
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
用exists求表a的id列和表b的aid列的差集(a.id - b.aid):
mysql> select id from a where not exists (select * from b where a.id=b.aid);
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
求除法请参考另外一份文档:exists和not exists的嵌套使用及求关系的除法.txt