|NO.Z.00138|——————————|BigDataEnd|——|Java&MySQL.高级.V10|——|MySQL.v10|七种JOIN方式|介绍编写|

一、JOIN查询的七种方式：7中JOIN ,可以分为四类: 内连接 、左连接 、右连接、 全连接

二、JOIN查询SQL编写

### --- JOIN查询SQL编写
——>        创建表 插入数据

~~~     # 部门表
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
    `id` varchar(40) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

~~~     # 员工表

DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
    `id` varchar(40) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    `age` int(3) DEFAULT NULL,
    `deptid` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `deptid` (`deptid`),
    CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

~~~     # 插入部门数据

INSERT INTO `t_dept` VALUES ('1', '研发部');
INSERT INTO `t_dept` VALUES ('2', '人事部');
INSERT INTO `t_dept` VALUES ('3', '财务部');

~~~     # 插入员工数据

INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1');
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2');
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1');
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);

### --- 内连接

mysql> SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id;
+----+--------+------+--------+----+-----------+
| id | name   | age  | deptid | id | name      |
+----+--------+------+--------+----+-----------+
| 1  | 赵四   |   23 | 1      | 1  | 研发部    |
| 2  | 刘能   |   25 | 2      | 2  | 人事部    |
| 3  | 广坤   |   27 | 1      | 1  | 研发部    |
+----+--------+------+--------+----+-----------+

### --- 左连接

mysql> SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id;
+----+--------+------+--------+------+-----------+
| id | name   | age  | deptid | id   | name      |
+----+--------+------+--------+------+-----------+
| 1  | 赵四   |   23 | 1      | 1    | 研发部    |
| 3  | 广坤   |   27 | 1      | 1    | 研发部    |
| 2  | 刘能   |   25 | 2      | 2    | 人事部    |
| 4  | 玉田   |   43 | NULL   | NULL | NULL      |
+----+--------+------+--------+------+-----------+

### --- 左连接去重叠部分

mysql> SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id WHERE e.deptid IS NULL;
+----+--------+------+--------+------+------+
| id | name   | age  | deptid | id   | name |
+----+--------+------+--------+------+------+
| 4  | 玉田   |   43 | NULL   | NULL | NULL |
+----+--------+------+--------+------+------+

### --- 右连接

mysql> SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id;
+------+--------+------+--------+----+-----------+
| id   | name   | age  | deptid | id | name      |
+------+--------+------+--------+----+-----------+
| 1    | 赵四   |   23 | 1      | 1  | 研发部    |
| 2    | 刘能   |   25 | 2      | 2  | 人事部    |
| 3    | 广坤   |   27 | 1      | 1  | 研发部    |
| NULL | NULL   | NULL | NULL   | 3  | 财务部    |
+------+--------+------+--------+----+-----------+

### --- 右连接去重叠部分

mysql> SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id WHERE e.id IS NULL;
+------+------+------+--------+----+-----------+
| id   | name | age  | deptid | id | name      |
+------+------+------+--------+----+-----------+
| NULL | NULL | NULL | NULL   | 3  | 财务部    |
+------+------+------+--------+----+-----------+

### --- 全连接
~~~     MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
~~~     多个SELECT 语句会删除重复的数据。

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id;

#
mysql> SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
    -> UNION
    -> SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id;
+------+--------+------+--------+------+-----------+
| id   | name   | age  | deptid | id   | name      |
+------+--------+------+--------+------+-----------+
| 1    | 赵四   |   23 | 1      | 1    | 研发部    |
| 3    | 广坤   |   27 | 1      | 1    | 研发部    |
| 2    | 刘能   |   25 | 2      | 2    | 人事部    |
| 4    | 玉田   |   43 | NULL   | NULL | NULL      |
| NULL | NULL   | NULL | NULL   | 3    | 财务部    |
+------+--------+------+--------+------+-----------+

### --- 各自独有

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id WHERE e.deptid IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id WHERE e.id IS NULL;

#
mysql> SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id WHERE e.deptid IS NULL
    -> UNION
    -> SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id WHERE e.id IS NULL;
+------+--------+------+--------+------+-----------+
| id   | name   | age  | deptid | id   | name      |
+------+--------+------+--------+------+-----------+
| 4    | 玉田   |   43 | NULL   | NULL | NULL      |
| NULL | NULL   | NULL | NULL   | 3    | 财务部    |
+------+--------+------+--------+------+-----------+

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---