7 种 join

 
 
DROP TABLE IF EXISTS `test_dept`;
CREATE TABLE `test_dept` ( 
  d_id int(20)  COMMENT 'id',
  d_name varchar(255) DEFAULT NULL COMMENT  '部门名称',
  PRIMARY KEY (`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';
  
  
DROP TABLE IF EXISTS `test_emp`;
CREATE TABLE `test_emp` (
  e_id int(20) NOT NULL COMMENT 'id',
  e_name varchar(255) DEFAULT NULL COMMENT '姓名', 
  d_id_fk int(11) default null COMMENT '部门表外键',
  PRIMARY KEY (`e_id`),
  key fk_dept_id (d_id_fk)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';
 
INSERT INTO test_dept VALUES(1, '部门1'),  (2, '部门2'),                  (4, '部门4');
INSERT INTO test_emp  VALUES(1, '李1', 1), (2, '李2', 2), (3, '李3', 3);


 
 

SELECT * FROM test_dept d inner join test_emp e ON e.d_id_fk = d.d_id ; #1

SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id ; #2
SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id where e.d_id_fk is not null; #3
SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id where e.d_id_fk is     null; #(与 'inner join' 的示意图区域重合，但是意义不同 )

SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id ; #4
SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id where d.d_id is not null; #5
SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id where d.d_id is     null; #(与 'inner join' 的示意图区域重合，但是意义不同 )

-- 6. full  join
SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id
UNION ALL
SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id ;

-- 7. full outer join ... is not null (去重 结果等于 'inner join'，但示意图不同 )
SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id where e.d_id_fk is not null
UNION ALL
SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id where d.d_id is not null;

-- 8. full inner join ... is null    (与 'inner join' 的示意图区域重合，但是意义不同 )
SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id where e.d_id_fk is null
UNION ALL
SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id where d.d_id is null;



1.
mysql> SELECT * FROM test_dept d inner join test_emp e ON e.d_id_fk = d.d_id ;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
+------+---------+------+--------+---------+

2.
mysql> SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id ;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
|    4 | 部门4   | NULL | NULL   |    NULL |
+------+---------+------+--------+---------+

3.
mysql> SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id where e.d_id_fk is not null;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
+------+---------+------+--------+---------+

4.
mysql> SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id ;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
| NULL | NULL    |    3 | 李3    |       3 |
+------+---------+------+--------+---------+

5.
mysql> SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id where d.d_id is not null;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
+------+---------+------+--------+---------+

6.
mysql> SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id
    -> UNION ALL
    -> SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id ;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
|    4 | 部门4   | NULL | NULL   |    NULL |
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
| NULL | NULL    |    3 | 李3    |       3 |
+------+---------+------+--------+---------+

7.
mysql> SELECT * FROM test_dept d left  join test_emp e ON e.d_id_fk = d.d_id where e.d_id_fk is not null
    -> UNION ALL
    -> SELECT * FROM test_dept d right join test_emp e ON e.d_id_fk = d.d_id where d.d_id is not null;
+------+---------+------+--------+---------+
| d_id | d_name  | e_id | e_name | d_id_fk |
+------+---------+------+--------+---------+
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
|    1 | 部门1   |    1 | 李1    |       1 |
|    2 | 部门2   |    2 | 李2    |       2 |
+------+---------+------+--------+---------+

 
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, '李四'), (4, 0, '新来的');
-- 初始化成绩
INSERT INTO test_score   VALUES(1, 10, 0), 　　(2, 20, 0),  　　　　　　　　　　　　(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;