MySQL子查询：ALL、ANY、SOME、EXISTS、IN、比较运算符查询

最新推荐文章于 2024-01-04 16:37:42 发布

董哥的黑板报

最新推荐文章于 2024-01-04 16:37:42 发布

阅读量1.4k

点赞数 1

分类专栏： MySQL 文章标签：子查询

本文链接：https://blog.csdn.net/qq_41453285/article/details/89376355

版权

MySQL 专栏收录该内容

49 篇文章 8 订阅

订阅专栏

本文用到的样例表

vendors表：存储销售产品的供应商。供应商ID（vend_id）列用来匹配产品和供应商。
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
products表：包含产品目录，每行一个产品。每个产品有唯一的ID（prod_id），通过vend_id关联到它的供应商。
prod_id为主键。
vend_id为外键，关联到vendors的vend_id。
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
customers表：存储所有顾客的信息。每个顾客有唯一的ID（cust_id）。
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
orders表：存储顾客订单（但不是订单详细）。每个订单有唯一的编号（order_num）。
order_num为主键，且为自动增量字段。
cust_id为外键，关联到customers的cust_id。
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;
orderitems表：存储每个订单中的实际物品，每个订单的每个物品占一行。orders表中的每一行，对应orderitems表中的一行或多行、每个订单物品由订单号加订单物品作为唯一标识（主键）。
order_num和order_item组合作为主键。
order_num为外键，关联到orders的order_num。
prod_id为外键，关联到products的prod_id。
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
上面这些样例表的定义及数据的相关sql请参阅https://github.com/dongyusheng/csdn-code/tree/master/mysql/order，下载之后导入数据库即可：

一、子查询总体概述

将一个查询语句嵌套在另一个查询语句内部的查询。
子査询可以返回各种不同类型的信息：
- 标量子査询返回一个值。
- 列子査询返回一个由一个值或多个值构成的列。
- 行子査询返回一个由一个值或多个值构成的行。
- 表子査询返回一个由一个行或多个行构成的表，而行则由一个或多个列构成。
子査询结果的测试方法各不相同：
- 可以用诸如"="或"<"之类的关系比较运算符来评估标量子査询的结果。
- 可以用运算符IN和NOT IN来测试某给定值是否包含在子査询的结果集里。
- 可以用运算符ALL、ANY和SOME把某给定值与子査询的结果集进行比较。
- 可以用运算符EXISTS和NOT EXISTS来测试子査询的结果是否为空。
标量子査询是最严格的，因为它只会产生一个值。正因如此，标量子査询的适用范围也最大。从理论上讲，标量子査询可以出现在任何允许使用标量操作数的地方，如出现在某个表达式里、作为函数参数或者放置在输出列的列表里。列、行和表这3类子査询会返回更多的信息，因此不能用在要求必须使用单个值的环境里。
子査询要不要相关都可以。也就是说，子査询既可以引用和依赖外层査询里的值，也可以不这样做。
除了SELECT语句，子査询还可以用在其他语句里。不过，如果把子查询用在一条会改变表内容的语句里(如DELETE、INSERT、REPLACE、UPDATE、LOADDATA)，那么MySQL会强行限制这个子査询，不允许它查询正被修改的那个表。
有些子査询可以改写为连接操作。你会发现掌握子査询的改写技术很有用，通过这门技术可以看出MySQL优化器在使用连接的情况下，是否比使用等价的子査询表现得更好。

列必须匹配

在where字句中使用子查询，应该保证select语句具有与where子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

子查询和性能

在编程中，嵌套的子查询的数目没有限制，不过实际使用时由于性能的限制，不能嵌套太多的子查询。
在联结表查询时会进一步介绍。

二、子查询的基本使用

场景：列出订购物品为TNT2（prod_id）的所有客户的顾客名（cust_name）和顾客联系名（cust_contact）。

不使用子查询实现

首先在orderitems表中查找prod_id为“TNT2”的订单编号（order_num）：
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
再到orders表中查找订单编号（order_num）为“20005和20007”的顾客id（cust_id）：
SELECT cust_id FROM orders WHERE order_num IN(20005, 20007);
然后根据顾客的id去customers表中查找顾客的顾客名（cust_name）和顾客联系名（cust_contact）
SELECT cust_name, cust_contact from customers WHERE cust_id IN(10001, 10004);

使用子查询实现

将第一个子查询结果作为第2个子查询的结果，然后第2个子查询的结果再传递给主查询，就可以达到上面不使用子查询的相同结果：
SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN
(SELECT order_num FROM orderitems WHERE prod_id='TNT2'
));
备注：这个子查询比较复杂，在前面一篇文章中（https://blog.csdn.net/qq_41453285/article/details/89215645）介绍连接查询时，我们使用连接查询来代替这个子查询

逐渐增加子查询来建立查询

用子查询测试和调试查询很有技巧性，特使是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。
首先，建立和测试最内层的查询，然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个子查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性。

三、带关系比较运算符的子查询

运算符=、<>、>、>=、<和<=可用来对值之间的关系进行比较。当与标量子査询配合使用时，它们会将外层査询里所有与子査询的返回值有着特定关系的行找出来。
例如，想要査看'2012-09-23'那天的测验成绩，则可以先用一个标量子査询来确定该次测验的事件ID，然后再在外层的SELECT里，针对那个ID与score表的所有行进行匹配：

SELECT * FROM score WHERE event_id = (
SELECT event_id FROM grade_event WHERE date = '2012-09-23' AND category = 'Q'
);

在这种形式的语句里，子查询的前面有一个值和一个关系比较运算符，因此这个子査询一定是只产生一个值。也就是说，它一定是个标量子査询；如果它返回了多个值，那么整条语句将以失败告终。有时，为了满足只返回一个值的要求，也可以使用LIMIT 1来限制子査询的结果。
如果遇到的问题可以通过在WHERE子句里使用某个聚合函数来解决，那么可以考虑用带关系比较运算符的标量子査询。例如，想要知道在president表里的哪位总统出生得最早，则可能会试着编写出下面这条语句：

SELECT * FROM president WHERE birth = MIN(birth);

上面的SQL是行不通的，因为你不能在WHERE子句里使用聚合函数。(WHERE子句的用途是确定应该选取哪些行，但MIN()的值只有在选取行之后才能确定下来)。不过，你可以像下面这样用一个子査询来产生一个最小的出生日期：

SELECT * FROM president WHERE birth IN (
SELECT MIN(birth) FROM president
);

也可以用其他的聚合函数来解决类似的问题。下面这条语句使用了一个子査询来选取某次考试中高于平均分数的分数：

SELECT * FROM score WHERE event_id = 5
AND score > (SELECT AVG(score) FROM score WHERE event_id = 5);

行构造器

如果子査询返回的是一个行，那么可以用一个行构造器来实现一组值(即元组)与子査询结果的比较。
下面这条语句会返回多行与John Adams总统出生于同一个州和城市的那些总统的行：
SELECT last_name, first_name, city, state FROM president
WHERE (city, state) = (SELECT city, state FROM president WHERE last_name = 'Adams' AND first_name = 'John');
也可以使用ROW(city,state)表示法，它等价于(city,state)。两者都可用作行构造器。

演示案例

在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有供应商提供的水果的种类。

在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非供应商提供的水果的。

四、IN和NOT INT子查询

当子査询要返回多个行来与外层查询进行比较运算时，可以使用运算符IN和NOT IN。它们会测试某个给定的比较值是否存在于某一组值里。
下面两条语句分别使用IN和NOT IN来査找那些在absence表里有缺勤记录的学生和没有缺勤记录(也就是全勤)的学生：

SELECT * FROM student WHERE student_id IN(SELECT student_id FROM absence);
SELECT * FROM student WHERE student_id NOT IN(SELECT student_id FROM absence);

IN和NOT IN还可以用于会返回多个列的子查询。也就是说，可以把它们与表的子查询一起使用。此时，需要使用一个行构造器来指定与各列进行测试的比较值：

SELECT last_name, first_name, city, state FROM president
WHERE (city, state) IN (SELECT city, state FROM president WHERE last_name = 'Roosevelt');

IN和NOT IN实际上就是"= ANY"和"<> ALL"的同义词。在下面介绍。

演示案例

在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id：

上面的代码等同于：

在orderitems表中查询f_id为c0的订单号，并查询订单号不是这个订单的用户c_id：

五、ALL、ANY、SOME子查询

运算符ALL和ANY常与某个关系比较运算符结合在一起使用，以便测试列子査询的结果。它们会测试比较值与子查询返回的全部或部分值是否匹配。SOME是ANY的同义词。
例如，当比较值小于或等于子查询返回的每个值时，<=ALL的结果为真；当比较值小于或等于子查询返回的任意值时，<=ANY的结果为真。
只要满足内层子查询的任何一个比较条件，就返回一个结果作为外层查询的条件。
下面这条语句可以用来检索最早出生的总统，具体做法是选取出生日期小于或等于president表里所有出生日期(只有最早的那个出生日期才会满足这一条件)的那个行：

SELECT last_name, first_name, birth FROM president
WHERE birth <= ALL (SELECT birth FROM president);

下面这条语句的用处就不大了，它会返回所有的行，因为对于每个日期，都至少会有另一个日期(它自身)小于或等于它：

SELECT last_name, first_name, birth FROM president
WHERE birth <= ANY (SELECT birth FROM president);

当ALL、ANY或SOME与比较运算符配合使用时，子査询可以是表子査询。此时，需要使用一个行构造器来提供比较值。

SELECT last_name, first_name, city, state FROM president
WHERE (city, state) = ANY (SELECT city, state FROM president WHERE last_name = 'Roosevelt');

演示案例

创建两个表，并插入2行数据：

CREATE TABLE tab1(num1 INT NOT NULL);
CREATE TABLE tab2(num2 INT NOT NULL);
INSERT INTO tab1 VALUES(1),(5),(13),(27);
INSERT INTO tab2 VALUES(6),(14),(11),(20);

ANY返回任意符合条件的：

SELECT num1 FROM tab1 WHERE num1 > ANY(SELECT num2 FROM tab2);

ALL必须满足所有条件：

SELECT num1 FROM tab1 WHERE num1 > ALL(SELECT num2 FROM tab2);

六、EXISTS、NOT EXISTS子查询

EXISTS后面的参数是一个任意的子查询。其只会测试某个子査询是否返回了行。如果有返回，则EXISTS的结果为真，而NOT EXISTS的结果为假。
下面两条语句展示了这两个运算符的具体用法。如果absence表为空，那么第一条语句将返回0，第二条语句将返回1：

SELECT EXISTS (SELECT * FROM absence);
SELECT NOT EXISTS (SELECT * FROM absence);

系统对子查询进行运算以判断是否返回结果给外面的查询。如果内查询至少返回一行，那么EXISTS的结果为true，此时外层查询语句开始进行查询。如果子查询没有结果，则EXISTS的结果为false，此时外层查询不进行查询。
在使用EXISTS和NOT EXISTS时，子査询通常将用作输出列的列表。因为这两个运算符是根据子査询是否返回了行来判断真假的，并不关心行所包含的具体内容，所以没必要显式地列出列名。事实上，可以在子査询的列选取列表里编写任何东西，但如果想要确保在子査询成功时返回一个真值，则可以把它写成SELECT 1，而不要写成SELECT *。
EXISTS和NOT EXISTS实际上在相关子查询里比较常见。请参阅下面介绍。

演示案例

exists可以跟条件表达式一起使用

七、相关子查询（作为计算字段使用子查询）

子査询要不要相关都可以

不相关的子査询不会引用外层査询里的值，因此它自己可以作为一条的单独査询命令去执行。例如，下面这条语句里的子査询就是不相关的，它只引用了t1，而没有引用t2：
SELECT j FROM t2 WHERE j IN(SELECT i FROM t1);
相关子査询则引用了外层査询里的值，所以它也就依赖于外层査询。因为有了这种联系，所以相关子査询不能脱离外层查询作为一条独立的査询语句去执行。例如，对于下面这条语句里的子査询，其作用是把t2中j列的每一个值与t1中i列的值进行匹配：
SELECT j FROM t2 WHERE (SELECT i FROM t1 WHERE i = j);

相关子査询通常用在EXISTS和NOT EXISTS子査询里，这类子査询主要用于在某个表里査找在另一个表里有匹配行或没有匹配行的行。相关子査询的工作原理是：把值从外层査询传递到子査询，并检査它们是否满足子查询里指定的那些条件。因此，如果列名会引起歧义(在多个表里有同名列)，那么必须使用表名来限定这些列名。
下面的EXISTS子査询可以标识出两个表之间的匹配情况——即在两个表里都存在的那些值。整条语句的作用是将在absence表里至少有一条缺勤记录的学生査询出来：

SELECT student_id, name FROM student WHERE EXISTS 
(SELECT * FROM absence WHERE absence.student_id = student.student_id);

NOT EXISTS则会标识出那些无匹配的情况——即那些在一个表里有，但在另一个表里不存在的值。下面这条语句将把没有缺勤记录的学生査询出来：

SELECT student_id, name FROM student WHERE NOT EXISTS 
(SELECT * FROM absence WHERE absence.student_id = student.student_id);

演示案例

例如，查询有过购物信息的客户的cust_name，cust_state以及各个用户的订单数目。
最外层select让每个客户返回3列。子查询的select检索出的每个客户执行一次。
SELECT cust_name, cust_state,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers order by cust_name;

相关子查询的概念

像上面这种设计外部查询的子查询称为“相关子查询”。
任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句号分割）。

八、FROM子句里的子查询

子査询可以用在FROM子句里，以生成某些值。此时，子査询的结果就像一个表。FROM子句里的子査询可以参与关联操作，其值可以在WHERE子句里进行测试，等等。
在使用这种类型的子査询时，必须提供一个表別名，用作子査询结果的名字。
例如：

SELECT * FROM (SELECT 1, 2) AS t1 INNER JOIN (SELECT 3, 4) AS t2;

九、将子查询改为连接

有相当一部分使用了子査询的査询命令，可以改写为连接，并且，在检査那些倾向于编写成子査询的那些査询时，不妨考虑这样做。连接的效率有时会比子査询更好些，因此如果某条使用子査询的SELECT语句需要花费很长时间才能执行完毕，那么可以尝试把它改写为一个连接，看看执行效果是否有所改善。

改写用来查询匹配值的子查询

下面这条示例语句包含有一个子査询，它只会把score表里的考试(不含测验)成绩査询出来：
SELECT * FROM score WHERE event_id IN(SELECT event_id FROM grade_event WHERE category = 'T');
在编写这条语句时，可以不使用子査询，而是把它转换为一个简单的连接：
SELECT score.* FROM score INNER JOIN grade_event ON score.event_id = grade_event.event_id WHERE grade_event.category = 'T';
再来看另一个示例。下面这条查询语句可以把女生们的考试成绩査询出来：
SELECT * FROM score WHERE student_id IN (SELECT student_id FROM student WHERE sex = 'F');
这条语句可以像下面这样转换成连接：
SELECT score.* FROM score INNER JOIN student ON score.student_id = student.student_id WHERE student.sex = 'F';
这里有一个模式。这些子査询语句都遵从这样一种形式：
SELECT * FROM table1 WHERE column1 IN(SELECT column2a FROM tables WHERE column2b = value);
这类査询都可以被转换为下面这种形式的连接査询：
SELECT table1.* FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2a WHERE table2.column2b = value;
在某些场合，子査询和关联査询可能会返回不同的结果：
当table2包含column2a的多个实例时，会发生这种情况。那种形式的子査询只会为每个column2a值生成一个实例；而连接操作会为所有值生成实例，并且其输出会包含重复行。
如果想要防止这种重复记录出现，就要在编写连接査询语句时使用SELECT DISTINCT，而不能使用SELECT。

改写用来查询非匹配（缺失）值的子查询

另一种常见的子查询语句类型是：把存在于某个表里，但在另一个表里并不存在的那些值查找出来。我们前面见过，与“哪些值不存在”有关的问题通常都可以用LEFTJOIN来解决。
下面是一个我们曾经见过的子査询，它用来测试哪些学生没有出现在absence表里(它可以把那全勤学生査找出来)：
SELECT * FROM student WHERE student_id NOT IN (SELECT student_id FROM absence);
这条査询语句可以像下面那样使用LEFT JOIN来改写：
SELECT student.* FROM student LEFT JOIN absence ON student.student_id = absence.student_id WHERE absence.student_id IS NULL;
通常情况下，如果子査询语句符合如下所示的形式：
SELECT * FROM table1 WHERE column1 NOT IN (SELECT column2 FROM table2);
那么可以把它改写为下面这样的连接査询：
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2 WHERE table2.column2 IS NULL;
这里需要假设table2.column2被定义成了NOT NULL的。
与LEFT JOIN相比，这个子査询的好处更直观。大部分人都可以毫无困难地理解“没被包含在......里面”的含义，因为它不是数据库编程技术带来的新概念。概念“左连接”有所不同，难以用自然语言直观地描述出它的含义。

董哥的黑板报

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL子查询：ALL、ANY、SOME、EXISTS、IN、比较运算符查询

本文要用到的样例表vendors表：存储销售产品的供应商。供应商ID（vend_id）列用来匹配产品和供应商 vend_id为主键，且为自动增量字段 products表：包含产品目录，每行一个产品。每个产品有唯一的ID（prod_id），通过vend_id关联到它的供应商 prod_id为主键 vend_id为外键，关联到vendors的vend_id custo...
复制链接

扫一扫