mysql 子关联查询之 EXISTS 和 NOT EXISTS

MySQL EXISTS 和 NOT EXISTS 子查询语法如下：

SELECT … FROM table WHERE EXISTS (subquery)
  
  

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

 

范例一：

下面以实际的例子来理解 EXISTS 子查询。下面是原始的数据表：

article 文章表:

aid	title	content	uid
1	文章1	文章1正文内容…	1
2	文章2	文章2正文内容…	1
3	文章3	文章3正文内容…	2
4	文章4	文章4正文内容…	4

user 用户表:

uid	title	content
1	admin	admin@5idev.com
2	小明	xiao@163.com
3	Jack	jack@gmail.com

我们要查出 article 表中的数据，但要求 uid 必须在 user 表中存在。SQL 语句如下：

SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)
  
  

返回查询结果如下：

aid	title	content	uid
1	文章1	文章1正文内容…	1
2	文章2	文章2正文内容…	1
3	文章3	文章3正文内容…	2

从语句执行结果可以看出，article 表中第 4 条记录没有被保留，原因就是该条记录的数据在子查询中返回的结果是 FALSE 。 
当上面的 SQL 使用 NOT EXISTS 时，查询的结果就是 article 表中 uid 不存在于 user 表中的数据记录。

 

范例二：

我们先介绍下使用的3个数据表：

student数据表：

sno 学号	sname	ssex	sage
20161181	Altair	男	20
20161182	Desmond	男	18
20161183	Ezio	男	22
20161184	Christina	女	19

course数据表：

cno 课程编号	cname 课程名
1	C语言
2	数据结构
3	信号与系统
4	模拟电子技术
5	高数

sc数据表：

sno 学号	cno 课程编号	grade 成绩
20161181	1	99
20161182	2	98
20161181	2	97
20161181	3	95
20161184	3	92
20161181	4	90
20161181	5	88
20161183	5	58

EXISTS

EXISTS代表存在量词∃。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑假值“false”。

 

查询选修了课程”信号与系统“的同学 

其他方式：

               还可以通过三表联查的方式查询（join）

  
  

    
    

     
     

    
    
    
    

     
     

      
      SELECT s.Sname 
      
      FROM student s
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      WHERE 
      
      EXISTS  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      (
      
      SELECT * 
      
      FROM sc, course c 
      
      WHERE sc.sno = s.sno 
      
      AND sc.cno = c.cno 
      
      AND c.cname = 
      
      '信号与系统')
     
     
    
    
  
  

使用存在量词EXISTS后，若内层查询结果为非空，则外层的WHERE子句返回值为真，否则返回值为假。

在本例中，首先分析最内层的语句：

SELECT * FROM sc, course c WHERE sc.sno = s.sno AND sc.cno = c.cno AND c.cname = '信号与系统'
  
  

本例中的子查询的查询条件依赖于外层父查询的某个属性值（本例中的是Student的Sno值），这个相关子查询的处理过程是：

首先取外层查询中（student）表的第一个元组，根据它与内层查询相关的属性值（Sno值）处理内层查询，若外层的WHERE返回为真，则取外层查询中该元组的Sname放入结果表；

然后再取（student）表的下一组，重复这一过程，直至外层（Student）表全部检查完毕。

查询结果表：

Sname
Altair
Christina

NOT EXISTS

与EXISTS谓词相对的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若对应查询结果为空，则外层的WHERE子语句返回值为真值，否则返回假值。

 查询没有选修课程”信号与系统“的同学

  
  

    
    

     
     

    
    
    
    

     
     

      
      SELECT s.Sname 
      
      FROM student s
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      WHERE 
      
      NOT 
      
      EXISTS  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      (
      
      SELECT * 
      
      FROM sc, course c 
      
      WHERE sc.sno = s.sno 
      
      AND sc.cno = c.cno 
      
      AND c.cname = 
      
      '信号与系统')
     
     
    
    
  
  

使用NOT EXISTS之后，若内层查询结果为非空，则对应的NOT EXISTS不成立，所以对应的WHERE语句也不成立。

范例中李勇同学对应的记录符合内层的select语句的，所以返回该记录数据，但是对应的NOT EXISTS不成立，WHERE语句也不成立，表示这不是我们要查询的数据。

查询结果表：

Sname
Desmond
Ezio

 

查询选修了全部课程的学生姓名：（这是一个用NOT EXISTS表示全称量词的例子）

其他方式：

               可以查询sc数据表  group by sno 学号 having count (sno 学号) >  course数据表全部课程的数量来查询

  
  

    
    

     
     

    
    
    
    

     
     

      
      SELECT Sname  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      FROM Student   
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      WHERE 
      
      NOT 
      
      EXISTS  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      (
      
      SELECT * 
      
      FROM Course 
      
      WHERE 
      
      NOT 
      
      EXISTS  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
           (
      
      SELECT * 
      
      FROM SC 
      
      WHERE Sno=Student.Sno 
      
      AND Cno=Course.Cno)  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      );  
     
     
    
    
  
  

这个算是一个比较复杂的sql语句了，两个EXISTS和三个WHERE。

这个sql语句可以分为3层，最外层语句，最内层语句，中间层语句。

我们很关心最外层语句，因为结果表中的数据都是最外层的查询的表中的数据，我们更关心最内层的数据，因为最内层的数据包含了全部的判断语句，决定了student表中的那一条记录是我们查询的记录。

我们由内而外进行分析：

最外层的student表中的第一条记录是Altair同学对应的记录，然后中间层的course表的第一条记录是数据库对应的记录，然后对该数据进行判断（最内层的WHERE语句），结果返回真，则内层的NOT EXISTS为假， 
然后继续对course表中的下一条记录进行判断，返现NOT EXISTS的值也为假，直到遍历完course表中的所有的数据，内层的NOT EXISTS的值一直都是假，所以中间层的WHERE语句的值也一直都是假。 
对应student的Altair记录，course表中的所有的记录对应的中间层的返回值为假，所以最外层的NOT EXISTS对应的值为真，最外层的WHERE的值也为真，则Altair对应的记录符合查询条件，装入结果表中。 
然后继续对student表中的下一条记录进行判断，直达student表中的所有数据都遍历完毕。

 

先取一条student记录，进入中层，再取一条course的记录，进入内层，此时student的记录和course的记录，作为内层判断的条件，比如此时我取的第一条记录是Altair，那么我里面的Sql就可以写成

  
  

    
    

     
     

    
    
    
    

     
     

      
      SELECT * 
      
      FROM Course 
      
      WHERE 
      
      NOT 
      
      EXISTS  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
           (
      
      SELECT * 
      
      FROM SC 
      
      WHERE Sno = 
      
      '20161181' 
      
      AND Cno=Course.Cno)  
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      )
     
     
    
    
  
  

此处 sno 20161181即Altair的学号，这条sql的意思是选出没有被Altair选择的课程，如果不存在，则返回false，再跟最外层的NOT EXISTS关联，负负得正。每一条循环的意思就是指，筛选出的每一个学生都不存在没有被他选取的那门课，即选了所有课。

最终查询结果：

Sname
Altair

参考：https://blog.csdn.net/qq_27571221/article/details/53090467