mysql中关键词exists的用法实例详解

CREATE TABLE `t_student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '学生姓名',
  `age` int NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';

INSERT INTO `t_student` (`id`, `name`, `age`)
VALUES
   (1, '小张', 10),
   (2, 'chenille', 13),
   (3, '小王', 15),
   (4, '小米', 11),
   (5, 'dong', 13),
   (6, 'xi', 12),
   (7, 'chenille', 13),
   (8, '小王地方', 15),
   (9, '米来', 11),
   (10, 'dong', 13),
   (11, '呵呵', 12),
   (12, 'chenille', 13),
   (13, '小赵', 15),
   (14, '小米-0', 11),
   (15, 'bei', 13),
   (16, 'xi-xx', 12),
   (17, 'chenille', 13),
   (18, '小王-hehe', 15),
   (19, '小米-qian', 11),
   (20, 'dong', 13),
   (21, 'xi', 12),
   (22, 'chenille', 13),
   (23, '小王-1', 15),
   (24, '小米-2', 11),
   (25, 'dong-3', 13),
   (26, 'xi-0', 12),
   (27, 'chenille-4', 13),
   (28, '小王-4', 15),
   (29, '小米-7', 11),
   (30, 'dong-1', 13),
   (31, 'xi-5', 12),
   (32, '貔貅', 10),
   (33, '耄耋', 12),
   (34, '饕餮', 9),
   (35, '龙', 13),
   (36, '青牛', 12);

CREATE TABLE `t_class_student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL COMMENT '学生ID',
  `class_id` int NOT NULL COMMENT '班号',
  `class_name` varchar(100)  DEFAULT '' COMMENT '班级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='班级学生表';

 INSERT INTO `t_class_student` (`id`, `student_id`, `class_id`, `class_name`)
VALUES
    (1, 1, 1, '一年级1班'),
    (2, 2, 1, '一年级1班'),
    (3, 3, 1, '一年级1班'),
    (4, 4, 1, '一年级1班'),
    (5, 5, 1, '一年级1班'),
    (6, 6, 1, '一年级1班'),
    (7, 7, 1, '一年级1班'),
    (8, 8, 1, '一年级1班'),
    (9, 9, 1, '一年级1班'),
    (10, 10, 1, '一年级1班'),
    (11, 11, 2, '一年级2班'),
    (12, 12, 2, '一年级2班'),
    (13, 13, 2, '一年级2班'),
    (14, 14, 2, '一年级2班'),
    (15, 15, 2, '一年级2班'),
    (16, 16, 2, '一年级2班'),
    (17, 17, 2, '一年级2班'),
    (18, 18, 2, '一年级2班'),
    (19, 19, 2, '一年级2班'),
    (20, 20, 2, '一年级2班'),
    (21, 21, 3, '二年级2班'),
    (22, 22, 3, '二年级2班'),
    (23, 23, 3, '二年级2班'),
    (24, 24, 3, '二年级2班'),
    (25, 25, 3, '二年级2班'),
    (26, 26, 3, '二年级2班'),
    (27, 27, 3, '二年级2班'),
    (28, 28, 3, '二年级2班'),
    (29, 29, 3, '二年级2班'),
    (30, 30, 3, '二年级2班'),
    (31, 31, 4, '三年级1班');
    (32, 32, 4, null);

select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id);

select * from t_student as s where not exists (select student_id from t_class_student where student_id = s.id);

select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id and class_id = 4);

select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id and class_id in (1, 2) );

select * from t_student as s where exists (select class_name from t_class_student where student_id = s.id and class_id = 4);

select * from t_student as s where exists (select student_id from t_class_student where 1=1);

select * from t_student as s where age > 10 and exists (select student_id from t_class_student where student_id = s.id and class_id = 4);

select * from t_student as s where id in (select student_id from t_class_student where student_id = s.id);

select * from t_student as s where id not in (select student_id from t_class_student where student_id = s.id);