7 种 join

佚名 6年前 (2019-04-11) 随笔 1189人围观抢沙发百度已收录

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

 
DROP TABLE IF EXISTS `test_student`;
CREATE TABLE `test_student` (
  `id` int(20) NOT NULL COMMENT '学号',
  `sex` int DEFAULT '0' COMMENT '性别 0-男 1-女',
    `name` varchar(255) DEFAULT NULL COMMENT '姓名', 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
 
DROP TABLE IF EXISTS `test_score`;
CREATE TABLE `test_score` ( 
    `s_id` int(20)  COMMENT '学号',
  `score` int NOT NULL COMMENT '分数',
    `level` int COMMENT '成绩 0-不及格 1-及格 2-优良 3-优秀' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
 
-- 初始化学生
INSERT INTO test_student VALUES(1, 0, '张三'), (2, 0, '李四'), (3, 1, '王X芳'), (4, 0, '新来的');
-- 初始化成绩
INSERT INTO test_score VALUES(1, 10, 0), (2, 20, 0), (3, 100, 3), (5, 10, 0);

SELECT * FROM test_student ts inner JOIN test_score tc ON ts.id = tc.s_id ;-- 1. inner

SELECT * FROM test_student ts LEFT  JOIN test_score tc ON ts.id = tc.s_id ;-- 2. LEFT outer join

SELECT * FROM test_student ts LEFT  JOIN test_score tc ON ts.id = tc.s_id where tc.s_id is null;-- 3. 

SELECT * FROM test_student ts right JOIN test_score tc ON ts.id = tc.s_id ;-- 4. right outer join

SELECT * FROM test_student ts right JOIN test_score tc ON ts.id = tc.s_id where ts.id   is null;-- 5.

-- 6. full outer join
SELECT * FROM test_student ts LEFT  JOIN test_score tc ON ts.id = tc.s_id ;
UNION ALL
SELECT * FROM test_student ts right JOIN test_score tc ON ts.id = tc.s_id ;

-- 7. full outer join ... is null
SELECT * FROM test_student ts LEFT  JOIN test_score tc ON ts.id = tc.s_id where tc.s_id is null ;
UNION ALL
SELECT * FROM test_student ts right JOIN test_score tc ON ts.id = tc.s_id where ts.id   is null;

select id from `test_student` order by rand() limit 1000; -- 随机抽样
-- 可优化为：
select id from `test_student` t1 
inner join 
(select rand() * (select max(id) from `test_student`) as nid) t2 
on t1.id > t2.nid 
limit 1000;


-- 解析：
select id from `test_student` t1 
inner join 
(  select rand() *2  as nid) t2 
on t1.id > t2.nid 
limit 1000;

select id from `test_student` t1 
inner join 
(  select rand() *2  as nid) t2 
on t1.id > 0 
limit 1000;