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

引言

子查询概述：

• 子查询（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');
  

  
• 嵌套子查询

  SELECT * FROM subquery WHERE price > (SELECT AVG(price) FROM subquery);
  

  
• 出现的问题：

  SELECT * FROM subquery WHERE price > (SELECT price FROM subquery WHERE id > 3);
  

  
  这个时候会报错， 因为子查询返回并不是一个值，而是一个集合值，所以这个时候我们就需要用到下面的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);
  

  

• 修饰的比较运算符的作用

  	ANY	SOME	ALL
  >, >=	最小值	最小值	最大值
  <, <=	最大值	最大值	最小值
  =	任意值	任意值
  != , <>			任意值

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

• 测试：

  SELECT price FROM subquery WHERE id > 3;
  

  内部子查询结果：
  

  SELECT * FROM subquery WHERE price > ANY(SELECT price FROM subquery WHERE id > 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);
  

  

  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);