MySQL的两张表的七种Join查询

SQL的语法格式如下

SELECT DISTINCT
	< select_list > 
FROM
	< left_table > < join_type >
JOIN < right_table > ON <join_condition>
WHERE
	< where_condition > 
GROUP BY
	< group_by_list > 
HAVING
	< having_condition > 
ORDER BY
	< order_by_condition > 
LIMIT < limit_number >

想掌握最基础的三个:left join、right join、inner join

假设有A表和B表两张表 

A表：id、员工名、部门号

id	name	dept_no
1	张三	1
2	李四	2

---

B表：主键id、部门名、部门主管

dept_no	dept_name	dept_admin
2	人力资源部	李四
3	财务部	null

DROP TABLE IF EXISTS `A`;
CREATE TABLE `A`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `dept_no` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

INSERT INTO `A` VALUES (1, '张三', 1);
INSERT INTO `A` VALUES (2, '李四', 2);

DROP TABLE IF EXISTS `B`;
CREATE TABLE `B`  (
  `dept_no` int(0) NOT NULL,
  `dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `dept_admin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`dept_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `B` VALUES (2, '人力资源部', '李四');
INSERT INTO `B` VALUES (3, '财务部', NULL);

 我们都知道数据库在进行表连接即（join）操作的时候，是进行笛卡尔积操作

A表两行记录，B表两行记录。进行迪卡积就变成2 x 2=4行记录

（不考虑数据真实性）假设如上A、B两张表进行笛卡尔积操作结果就是如下

id	name	dept_no	dept_no	dept_name	dept_admin
1	张三	1	2	人力资源部	李四
1	张三	1	3	财务部	null
2	李四	2	2	人力资源部	李四
2	李四	2	3	财务部	null

第一个是左连接：A Left Join B   得到集合(A)

left join 特点是保留A表所有字段，如果没有匹配到连接条件则用null填充

对应sql示例

select t1.*,t2.* from A t1 left join B t2 on t1.dept_no=t2.dept_no

结果如下：

id	name	dept_no	dept_no	dept_name	dept_admin
2	李四	2	2	人力资源部	李四
1	张三	1	null	null	null

 图示：

---

 第二个是右连接 A right join B   得到集合（B）

sql示例： 

select t1.*,t2.* from A t1 right join B t2 on t1.dept_no=t2.dept_no

结果如下：

id	name	dept_no	dept_no	dept_name	dept_admin
2	李四	2	2	人力资源部	李四
			3	财务部

图示：

---

第三个是 内连接 A inner join B   得到（A∩B）

SQL示例 

select t1.*,t2.* from A t1 inner join B t2 on t1.dept_no=t2.dept_no 

 对应结果就是：

id	name	dept_no	dept_no	dept_name	dept_admin
2	李四	2	2	人力资源部	李四

 图示：

---

 第四种：在理解上面的三种join下，查询（A -  A∩B）

对应图示：

sql示例:实际上只是加入where条件过滤

select t1.*,t2.* 
from A t1 
left join B t2 on t1.dept_no=t2.dept_no 
where t2.dept_no is null;

id	name	dept_no	dept_no	dept_name	dept_admin
1	张三	1

---

第五种：查询 （ B - A∩B ）

图示：

sql示例：

select t1.*,t2.* 
from A t1 right join B t2 on t1.dept_no=t2.dept_no 
where t1.dept_no is null;

id	name	dept_no	dept_no	dept_name	dept_admin
			3	财务部

---

 第六种：查询（A∪B - A∩B）

图示：

sql示例：利用union去重将上面的第四、第五种两条sql中间用union连接即可完成；即先完成一小部分的，然后将两个拼起来的思想

select A.*,B.* from A left join B on A.dept_no=B.dept_no where B.dept_no is null
union
select A.*,B.* from A right join B on A.dept_no=B.dept_no where A.dept_no is null;

结果：

id	name	dept_no	dept_no	dept_name	dept_admin
1	张三	1
			3	财务部

---

 第七种：AUB

图示：

MySQL中求并集可以使用union关键字进行处理（自动去重）

sql示例 

select t1.*,t2.* from A t1 left join B t2 on t1.dept_no=t2.dept_no
union
select t1.*,t2.* from A t1 right join B t2 on t1.dept_no=t2.dept_no;

id	name	dept_no	dept_no	dept_name	dept_admin
2	李四	2	2	人力资源部	李四
1	张三	1
			3	财务部