六、子查询——多行子查询

た入场券 2023-06-25 04:47 238阅读 0赞

## 六、子查询——多行子查询 ##

### 1.什么是多行子查询 ###

子查询结果为一列值，主查询与子查询之间使用in、not  in、any、all等连接。

### 2.多行子查询实例 ###

**(1)示例1**

查询有成绩的同学的基本信息

<1>从成绩表中查询所有学生成绩不为空的学号

select student_id from student_score where score is not null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70][]

<2>从学生表中查询所有基本信息【总的SQL语句】

select * from student 
    where student_id in (select student_id from student_score where score is not null);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1][]

**(2)示例2**

查询没有成绩的同学的基本信息

<1>从成绩表中查询所有学生成绩不为空的学号

select student_id from student_score where score is not null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70][]

<2>从学生表中查询所有基本信息【总的SQL语句】

select * from student 
    where student_id not in (select student_id from student_score where score is not null);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2][]

### 3.注意点 ###

尽量减少使用not in，效率不高。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019122522473527.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191225224800417.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191225225827750.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70