大数据预科班综合测试卷下 编写一段sql，查找a、b表都有的userid 查找a中有b中没有的userid

大数据预科班综合测试卷下 编写一段sql，查找a、b表都有的userid

笔记内容输出来源：拉勾教育Java大数据学科 训练营

select * from visdata.a  a  inner join visdata.b b on a.userid=b.userid

CREATE TABLE `a` (
  `userid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=778 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('111');
INSERT INTO `a` VALUES ('222');
INSERT INTO `a` VALUES ('333');
INSERT INTO `a` VALUES ('777');

 

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `userid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=445 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of b
-- ----------------------------
INSERT INTO `b` VALUES ('111');
INSERT INTO `b` VALUES ('333');
INSERT INTO `b` VALUES ('444');

 

        表a

+----+

| userid |

+----+

| 111 |

| 222 |

| 333 |

| 777|

+----+

 

表b

+----+

| userid |

+----+

| 111 |

| 444|

| 333|

+----+

 

1）大数据预科班综合测试卷下 编写一段sql，查找a、b表都有的userid（5分）

 

select * from visdata.a  a  inner join visdata.b b on a.userid=b.userid

2）编写一段sql，查找a中有b中没有的userid（5分）

方法1 select  userid  from  visdata.a  where userid not in (select  userid  from  visdata.b )
方法2 select visdata.a.userid from visdata.a left join visdata.b on visdata.a.userid=visdata.b.userid where visdata.b.userid is null
方法3 select * from  visdata.a where (select count(1) from visdata.b where visdata.a.userid=visdata.b.userid) = 0