演示demo表:
student表
DROP
score表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`stuid` varchar(16) NOT NULL,
`courseno` varchar(20) NOT NULL,
`scores` float DEFAULT NULL,
PRIMARY KEY (`stuid`,`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1001', 'C001', '67');
INSERT INTO `score` VALUES ('1001', 'C002', '87');
INSERT INTO `score` VALUES ('1001', 'C003', '83');
INSERT INTO `score` VALUES ('1001', 'C004', '88');
INSERT INTO `score` VALUES ('1001', 'C005', '77');
INSERT INTO `score` VALUES ('1001', 'C006', '77');
INSERT INTO `score` VALUES ('1002', 'C001', '68');
INSERT INTO `score` VALUES ('1002', 'C002', '88');
INSERT INTO `score` VALUES ('1002', 'C003', '84');
INSERT INTO `score` VALUES ('1002', 'C004', '89');
INSERT INTO `score` VALUES ('1002', 'C005', '78');
INSERT INTO `score` VALUES ('1002', 'C006', '78');
INSERT INTO `score` VALUES ('1003', 'C001', '69');
INSERT INTO `score` VALUES ('1003', 'C002', '89');
INSERT INTO `score` VALUES ('1003', 'C003', '85');
INSERT INTO `score` VALUES ('1003', 'C004', '90');
INSERT INTO `score` VALUES ('1003', 'C005', '79');
INSERT INTO `score` VALUES ('1003', 'C006', '79');
INSERT INTO `score` VALUES ('1004', 'C001', '70');
INSERT INTO `score` VALUES ('1004', 'C002', '90');
INSERT INTO `score` VALUES ('1004', 'C003', '86');
INSERT INTO `score` VALUES ('1004', 'C004', '91');
INSERT INTO `score` VALUES ('1004', 'C005', '80');
INSERT INTO `score` VALUES ('1004', 'C006', '80');
INSERT INTO `score` VALUES ('1005', 'C001', '71');
INSERT INTO `score` VALUES ('1005', 'C002', '91');
INSERT INTO `score` VALUES ('1005', 'C003', '87');
INSERT INTO `score` VALUES ('1005', 'C004', '92');
INSERT INTO `score` VALUES ('1005', 'C005', '81');
INSERT INTO `score` VALUES ('1005', 'C006', '81');
INSERT INTO `score` VALUES ('1006', 'C001', '72');
INSERT INTO `score` VALUES ('1006', 'C002', '92');
INSERT INTO `score` VALUES ('1006', 'C003', '88');
INSERT INTO `score` VALUES ('1006', 'C004', '93');
INSERT INTO `score` VALUES ('1006', 'C005', '82');
INSERT INTO `score` VALUES ('1006', 'C006', '82');
course表
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
`courseno` varchar(20) NOT NULL,
`coursenm` varchar(100) NOT NULL,
PRIMARY KEY (`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('C001', '大学语文');
INSERT INTO `courses` VALUES ('C002', '新视野英语');
INSERT INTO `courses` VALUES ('C003', '离散数学');
INSERT INTO `courses` VALUES ('C004', '概率论与数理统计');
INSERT INTO `courses` VALUES ('C005', '线性代数');
INSERT INTO `courses` VALUES ('C006', '高等数学(一)');
INSERT INTO `courses` VALUES ('C007', '高等数学(二)');
IN 语句:只执行一次
确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。
具体sql示例:
SQL语句执行顺序详见:https://blog.csdn.net/wqc19920906/article/details/79411854
select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)
执行结果:
select * from student s where s.stuid in(select stuid from score ss where ss.stuid <1005)
执行结果:
以上两个语句的执行流程:
首先会执行from语句找出student表,然后执行 in 里面的子查询,再然后将查询到的结果和原有的user表做一个笛卡尔积,再根据我们的student.stuid IN score.stuid的条件,将结果进行筛选(既比较stuid列的值是否相等,将不相等的删除)。最后,得到符合条件的数据。
EXISTS语句:执行student.length次
指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
具体示例:
select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)
这条sql语句的执行结果和上面的in的第一条执行结果是一样的。
但是,不一样的是它们的执行流程完全不一样:
使用exists关键字进行查询的时候,首先,我们先查询的不是子查询的内容,而是查我们的主查询的表,也就是说,我们先执行的sql语句是:
select * from student s
执行结果:
如果成立则返回true不成立则返回false。如果返回的是true的话,则该行结果保留,如果返回的是false的话,则删除该行,最后将得到的结果返回。
区别及应用场景
in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 ,另外IN时不对NULL进行处理。
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快