MySQL-JOIN连接

连载于：https://www.cnblogs.com/BgXhh/protected/articles/12971687.html 密码：303158131

示例表数据

部门表

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptName` varchar(30) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `department` VALUES ('1', '研发部(RD)', '2层');
INSERT INTO `department` VALUES ('2', '人事部(HR)', '3层');
INSERT INTO `department` VALUES ('3', '市场部(MK)', '4层');
INSERT INTO `department` VALUES ('4', '后勤部(MIS)', '5层');
INSERT INTO `department` VALUES ('5', '财务部(FD)', '6层');

员工表

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

INSERT INTO `employee` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `employee` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `employee` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `employee` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `employee` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `employee` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `employee` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `employee` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');

应用

内连接

作用

查询两张表的共有部分

语句

Select <select_list> from tableA A  Inner join  tableB B  on A.Key = B.Key

---

SELECT * from employee e  INNER JOIN department d on e.dep_id = d.id;

---

左连接

作用

把左边表的内容全部查出,右边表只查出满足条件的记录

语句

Select <select_list> from tableA A Left Join  tableB B  on A.Key = B.Key

---

SELECT * from employee e  LEFT JOIN department d on e.dep_id = d.id;

---

右连接

作用

把右边表的内容全部查出,左边表只查出满足条件的记录

语句

Select <select_list> from tableA A RIGHT Join  tableB B  on A.Key = B.Key

---

SELECT * from employee e  RIGHT JOIN department d on e.dep_id = d.id;

---

查询左表独有数据

作用

查询A的独有数据

语句

Select <select_list> from tableA A Left Join  tableB B  on A.Key = B.Key where B.key IS NULL

---

SELECT * from employee e LEFT JOIN department d on e.dep_id = d.id WHERE d.id IS NULL;

---

查询右表独有数据

作用

查询B表的独有数据

语句

Select <select_list> from tableA A Right Join tableB B  on A.Key = B.Key where A.key IS NULL;

---

SELECT * from employee e RIGHT JOIN department d on e.dep_id = d.id WHERE e.id IS NULL;

---

全连接

作用

查询两个表的全部信息

语句

Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key

注: Mysql 默认不支持此种写法 Oracle 支持

SELECT * from employee e  LEFT JOIN department d on e.dep_id = d.id
UNION
SELECT * from employee e  RIGHT JOIN department d on e.dep_id = d.id

---

查询左右表各自的独有的数据

作用

查询A和B各自的独有的数据

语句

Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key where A.key = null or B.key = null

---

SELECT * from employee e  LEFT JOIN department d on e.dep_id = d.id WHERE d.id is NULL
UNION
SELECT * from employee e  RIGHT JOIN department d on e.dep_id = d.id WHERE e.dep_id is NULL

---