由于我是SQL新手,因此在理解EXISTS
和NOT EXISTS
时遇到了一些麻烦。
我有这些表:
表阿卢诺:
+----+----------------+--------------------+
| id | nome | email |
+----+----------------+--------------------+
| 1 | João da Silva | joao@dasilva.com |
| 2 | Frederico José | fred@jose.com |
| 3 | Alberto Santos | alberto@santos.com |
| 4 | Renata Alonso | renata@alonso.com |
| 5 | Paulo da Silva | paulo@dasilva.com |
+----+----------------+--------------------+
表母体
+----+----------+----------+---------------------+---------+
| id | aluno_id | curso_id | data | tipo |
+----+----------+----------+---------------------+---------+
| 1 | 1 | 1 | 2013-11-25 16:16:05 | PAGA_PF |
| 2 | 2 | 1 | 2013-05-25 16:16:25 | PAGA_PJ |
| 3 | 3 | 3 | 2013-07-21 16:16:30 | PAGA_PF |
| 4 | 4 | 4 | 2013-11-15 16:15:35 | PAGA_PK |
| 5 | 2 | 2 | 2012-01-04 00:00:00 | PAGA_PJ |
+----+----------+----------+---------------------+---------+
运行此查询:
select a.nome from Aluno a where not exists(select m.id from Matricula m where m.aluno_id = a.id and m.data < now() - interval 46 month);
我得到了这个结果:
+----------------+
| nome |
+----------------+
| João da Silva |
| Renata Alonso |
| Paulo da Silva |
+----------------+
问题是:如果子查询的 select 语句返回超过0
行,如何TRUE
子查询NOT EXISTS
?
NOT EXISTS
子查询对于 Frederico 和 Alberto 为真,但对于其他三个为假。这是正确的,因为 Matricula 表中没有一行既链接到 Aluno 1、4 或 5,又具有过去 46 个月的日期。