1.准备库表

表结构

  1. CREATE TABLE `tbl_emp`  (
  2.  `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `name` varchar(20)  DEFAULT NULL,
  4. `deptId` int(11) DEFAULT NULL,
  5.  PRIMARY KEY (`id`) ,
  6. KEY `fk_dept_id`(`deptId`)
  7. )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
  9.  CREATE TABLE `tbl_dept`  (
  10.  `id` int(11) NOT NULL AUTO_INCREMENT,
  11. `deptName` varchar(30) DEFAULT NULL,
  12.  `locAdd` varchar(40) DEFAULT NULL,
  13.  PRIMARY KEY (`id`)
  14. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

数据

  1. insert into tbl_dept values(null,'RD',1);
  2. insert into tbl_dept values(null,'HR',12);
  3. insert into tbl_dept values(null,'MK',13);
  4. insert into tbl_dept values(null,'MIS',14);
  5. insert into tbl_dept values(null,'FD',15);
  7. insert into tbl_emp values(null,'z3',1);
  8. insert into tbl_emp values(null,'z4',1);
  9. insert into tbl_emp values(null,'z5',1);
  10. insert into tbl_emp values(null,'w5',2);
  11. insert into tbl_emp values(null,'w6',2);
  12. insert into tbl_emp values(null,'s7',3);
  13. insert into tbl_emp values(null,'s8',4);
  14. insert into tbl_emp values(null,'s9',51);

七种sql

image.png

  1. 左连接
  2. 右连接
  3. 并集
  4. a独享
  5. b独享
  6. 交集
  7. ab独享

    sql写法

    1. # 1交集
    2. select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;
    3. # 2a左
    4. select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;
    5. # 3b右
    6. select * from tbl_emp a right join  tbl_dept b on a.deptId=b.id;
    7. # 4a的独有
    8. select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null ;
    9. # 5b的独有
    10. select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.id is null ;
    11. # 6交集
    12. # union功能:并集+去重
    13. select * from tbl_emp a left join tbl_dept b on a.deptId=b.id
    14. union
    15. select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
    16. # 7a和b各自的独有
    17. select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null
    18. union
    19. select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.id is null ;