MySQL子查询：ANY、SOME、All、EXISTS、IN

表数据如下

sutdent

student2

1、ANY、SOME查询任何一个满足条件

select age from student WHERE age > ANY( select age from student2);
select age from student WHERE age > SOME( select age from student2);

结果相同

2、使用All查询满足所有条件

select age from student WHERE age > ALL( select age from student2);

结果，56大于student2的所有age

3、EXISTS查询存在

条件不满足，返回空
select age from student WHERE EXISTS( select age from student2 WHERE age >25);
条件满足，返回student所有的数据
select age from student WHERE EXISTS( select age from student2 WHERE age >15);

4、in查询

select age from student WHERE age in ( select age from student2);

结果

 

附上建表语句sql

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '21');
INSERT INTO `student` VALUES ('2', '李四', '56');
INSERT INTO `student` VALUES ('3', '王五', '18');
INSERT INTO `student` VALUES ('4', '张一', '13');


CREATE TABLE `student2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student2
-- ----------------------------
INSERT INTO `student2` VALUES ('1', '卡特', '14');
INSERT INTO `student2` VALUES ('2', '帝皇侠', '22');
INSERT INTO `student2` VALUES ('3', '蜘蛛侠', '25');
INSERT INTO `student2` VALUES ('4', '炎龙侠', '18');