MySQL exists and not exists

博文目录

---

sql中exists,not exists的用法

CREATE TABLE `a`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `b`  (
  `id` int(11) NOT NULL,
  `aid` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `c`  (
  `id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  `date` date NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `a` VALUES (1, 'a1');
INSERT INTO `a` VALUES (2, 'a2');
INSERT INTO `a` VALUES (3, 'a3');

INSERT INTO `b` VALUES (1, 1, 'b1');
INSERT INTO `b` VALUES (2, 2, 'b2');
INSERT INTO `b` VALUES (3, 2, 'b3');

INSERT INTO `c` VALUES (1, '苹果', '水果', 10, '2011-07-01');
INSERT INTO `c` VALUES (1, '橘子', '水果', 20, '2011-07-02');
INSERT INTO `c` VALUES (1, '香蕉', '水果', 15, '2011-07-03');
INSERT INTO `c` VALUES (2, '白菜', '蔬菜', 12, '2011-07-01');
INSERT INTO `c` VALUES (2, '青菜', '蔬菜', 29, '2011-07-02');

exists : 强调的是是否返回结果集，不要求知道返回什么

-- 获取在b表里面也存在的a表的数据, 相当于从a表一条条的拿数据到b表里面去找是否存在(b中是否存在某条/某些数据的aid字段值和当前对比的a的一条数据的id一样)
select * from a where exists (select * from b where a.id = b.aid);
-- 
select * from a where exists (select * from b where 1 = b.aid);

select * from a where exists (select * from b where 2 = b.aid);

select * from a where exists (select * from b where 3 = b.aid);

-- 获取在b表里面不存在的a表的数据, 相当于从a表一条条的拿数据到b表里面去找是否不存在, 是就显示出来(b中没有aid字段值和当前对比的a的一条数据的id一样)
select * from a where not exists (select * from b where a.id = b.aid);

-- 选出 Id唯一,Date最近的一条数据
-- 一条一条判断
-- 苹果, id是1,date是01, c里面id和它相同的有3条, 这3条里面date比它大的有两个, 所以结果是存在, 所以notexists是false, 所以苹果不符合条件
-- 橘子, id是1,date是02, c里面id和它相同的有3条, 这3条里面date比它大的有一个, 所以结果是存在, 所以notexists是false, 所以橘子不符合条件
-- 香蕉, id是1,date是03, c里面id和它相同的有3条, 这3条里面date比它大的没有, 所以结果是不存在, 所以notexists是true, 所以香蕉符合条件
select * from c a where not exists (select * from c where id = a.id and date > a.date);