MySQl 子查询 (ANY，SOME，ALL， IN 的运用)

清疚 2022-01-21 05:21 345阅读 0赞

## 引言 ##

##### 子查询概述： #####

*  子查询（Subquery）是指出现在其他 SQl 语句内的 SELECT 子句。
 *  子查询指嵌套在查询内部，且必须始终出现在圆括号内；
 *  子查询可以包含多个关键字或条件  
    如： DISTINCT ， GROUP BY ， ORDER BY ，LIMIT， 函数等；
 *  子查询的外层查询可以是：  
    SELECT ，INSERT， UPDATE，DELETE， SET 或 DO；
 *  子查询返回值  
    子查询可以返回标量，一行，一列或子查询

##### 例如： #####

SELECT * FROM table_name1(表名) WHERE col1(字段名) = (SELECT col2(字段名) FROM table_name2(表名) )
    
    注：
    其中 SELECT * FROM table_name1(表名) 称为 Outer Query/Outer Statement
    SELECT col2(字段名) FROM table_name2(表名) 称为 Subquery

## 子查询 ##

#### 1. 使用比较运算符的子查询 ####

*  比较运算符：  
    =、>、<、>= 、<= 、!= 、<>
 *  语法结构：  
    operand comparison\_operator subquery

例如：

*  创建表
    
        CREATE TABLE subquery (
        	id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
        	price float(6,4) NOT NULL,
        	PRIMARY KEY (id)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 *  插入数据
    
        INSERT INTO subquery (price) VALUES
        ('50.5'),('20.23'),('34.32'),('56.78'),('55.55'),('30.55');
    
    ![lchh][]
 *  嵌套子查询
    
        SELECT * FROM subquery WHERE price > (SELECT AVG(price) FROM subquery);
    
    ![lchh][lchh 1]
 *  出现的问题：
    
        SELECT * FROM subquery WHERE price > (SELECT price FROM subquery WHERE id > 3);
    
    ![lchh][lchh 2]  
    这个时候会报错， 因为子查询返回并不是一个值，而是一个集合值，所以这个时候我们就需要用到下面的ANY，SOME， ALL 或 IN；

#### 2. 用 ANY，SOME，ALL 修饰的比较运算符 ####

*  关键字：  
    ANY，SOME，ALL
 *  语法结构：  
    operand comparison\_operator ANY(subquery)  
    operand comparison\_operator SOME(subquery)  
    operand comparison\_operator ALL(subquery)

例如：  
还是同样的表，同样的数据

*  查询  
    这里把上面报错的语句修改一下
    
        SELECT * FROM subquery WHERE price > ANY(SELECT price FROM subquery WHERE id > 3);
    
    ![lchh][lchh 3]
 *  修饰的比较运算符的作用
    
    <table> 
     <thead> 
      <tr> 
       <th></th> 
       <th>ANY</th> 
       <th>SOME</th> 
       <th>ALL</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>&gt;, &gt;=</td> 
       <td>最小值</td> 
       <td>最小值</td> 
       <td>最大值</td> 
      </tr> 
      <tr> 
       <td>&lt;, &lt;=</td> 
       <td>最大值</td> 
       <td>最大值</td> 
       <td>最小值</td> 
      </tr> 
      <tr> 
       <td>=</td> 
       <td>任意值</td> 
       <td>任意值</td> 
       <td></td> 
      </tr> 
      <tr> 
       <td>!= , &lt;&gt;</td> 
       <td></td> 
       <td></td> 
       <td>任意值</td> 
      </tr> 
     </tbody> 
    </table>

解释： （ANY 修饰 >） 等价于 （查询出来的集合里只要外查询的字段值> 集合里的最小值）就满足情况。

*  测试：
    
        SELECT price FROM subquery WHERE id > 3;
    
    内部子查询结果：  
    ![lchh][lchh 4]
    
        SELECT * FROM subquery WHERE price > ANY(SELECT price FROM subquery WHERE id > 3);
    
    外部查询结果：  
    ![lchh][lchh 3]  
    如图所示, （ANY 修饰 >） 取出 子查询当中的最小值 30.5500 ， 表中只要price > (最小值 30.5500） 的行 满足情况。

#### 3. 使用 \[NOT\] IN 的子查询 ####

*  关键字：  
    IN， NOT IN
 *  语法结构：  
    operand comparison\_operator \[NOT\] IN(subquery)
 *  **=ANY 与 IN 等效**
 *  **!=ALL 或 <>ALL 与 NOT IN 等效**

还是那张表，那些数据

*  测试
    
        SELECT * FROM subquery WHERE price IN (SELECT price FROM subquery WHERE id > 3);
    
        SELECT * FROM subquery WHERE price = ANY (SELECT price FROM subquery WHERE id > 3);
    
    ![lchh][lchh 5]
    
        SELECT * FROM subquery WHERE price NOT IN (SELECT price FROM subquery WHERE id > 3);
    
        SELECT * FROM subquery WHERE price != ALL(SELECT price FROM subquery WHERE id > 3);
    
    ![lchh][lchh 6]

[lchh]: /images/20220121/9fc0ff9a937d4605b504f3906cfaaa54.png
[lchh 1]: /images/20220121/a9c7b043148445e19f2b9c8477375af2.png
[lchh 2]: /images/20220121/59ae59020448478c8d207d4911e0386f.png
[lchh 3]: /images/20220121/11950f0022954378a7e99c931d523ab3.png
[lchh 4]: /images/20220121/2f20fbc1adc14a29b1d0c42fd70f010e.png
[lchh 5]: /images/20220121/82ad93b9db364196a8eb7a5577512c30.png
[lchh 6]: /images/20220121/91a001f6d53742d1b26a9c69ee7d4cd6.png