MySQL子查询IN、EXISTS、ANY、ALL用法

概述

MySQL中以下几个个常用子句及其用法

IN子句

EXISTS子句

ANY子句

ALL子句

用例表

-- 创建一张用例表 drop table if exists tb_user; create table tb_user( id int auto_increment, name char(10), age int, sex char(2), primary key(id) )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 auto_increment=1 collate=utf8mb4_general_ci; -- 设置auto_increment自动增长初始值，默认1，此处无需设置 INSERT INTO `tb_user` VALUES (3, '何宇', 7, '男'); INSERT INTO `tb_user` VALUES (7, '张玉', 8, '女'); INSERT INTO `tb_user` VALUES (8, '张强', 32, '男'); INSERT INTO `tb_user` VALUES (22, '夏雪', 18, '女'); INSERT INTO `tb_user` VALUES (31, '何洁', 24, '女'); INSERT INTO `tb_user` VALUES (34, '赵晓', 21, '女'); INSERT INTO `tb_user` VALUES (35, '赵密', 34, '女'); select * from tb_user;

1.IN子句

假设IN子句所得集合A(21,22,23,24)， 则整个句子将查询所有包含在在集合A内的记录

select * from tb_user where id in (select id from tb_user where age>20 and age<25); -- 若 in子句为null，则整个查询结果为null select * from tb_user where id in (select id from tb_user where id<2); -- where子句"="包含子查询，需指定limit返回一条记录 select * from tb_user where id= ( select id from tb_user where age=20 limit 1);

2. EXISTS子句

EXISTS子句根据其内查询语句的结果集空或者非空，返回个布尔值 非空EXISTS子句为true，执行查询

select * from tb_user where exists (select * from tb_user where id=7); -- not exists 是exist的否定，若该子句中查询结果为null时，not exists判定为true，执行查询 select * from tb_user where not exists (select * from tb_user where id=100);

3. ANY子句

与some子句用法相同 假设ANY子句得到一个数据集合记为A(31,32,33,34), 则 id>ANY子句所得集合A中任意一个数即符合条件， 这样id取值(32,33,34,35,…)均符合条件

select * from tb_user where id>any ( select id from tb_user where id>30 and id<35); -- 若any子句为null，整个查询为null select * from tb_user where id>any ( select id from tb_user where id>1000); select * from tb_user where id>some ( select id from tb_user where id>30 and id<35);

4. ALL子句

假设ALL子句得到一个数据集合记为A(31,32,33,34), 则 id>ALL子句中所有的元素， 这样的id取值(35,36,37,…)均符合条件

select * from tb_user where id>all (select id from tb_user where id>30 and id<35); select * from tb_user where id>all (select id from tb_user where id>30 and id<35); -- 若all子句为null，则会查询所有 select * from tb_user where id>all (select id from tb_user where id>1000);