你知道 Join 有多少种用法吗?

逆流者 2021年03月30日 56次浏览

先看一张图,像不像数学中的集合,并集,交集,差集啥的。
在这里插入图片描述
下面我就用具体的例子来演示上图的7种情况:
新建两张表:t_emp和t_dept(员工表和部门表),这里约定一下,员工表t_emp代表图中A表,部门表t_dept代表图中B表。

创建表并插入数据:

DROP TABLE IF EXISTS `t_emp`;
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
 `dept_code` VARCHAR(40) DEFAULT NULL COMMENT "部门代码",
 `dept_name` VARCHAR(40) DEFAULT NULL COMMENT "部门名称",
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
 `name` VARCHAR(20) DEFAULT NULL COMMENT "员工名称",
  `age` INT(3) DEFAULT NULL COMMENT "年龄",
 `dept_id` INT(11) DEFAULT NULL COMMENT "部门表id",
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;
 
 
 
INSERT INTO t_dept(dept_code, dept_name) VALUES('ceo','总裁部门');
INSERT INTO t_dept(dept_code, dept_name) VALUES('development','开发部门');
INSERT INTO t_dept(dept_code, dept_name) VALUES('test','测试部门');


INSERT INTO t_emp(NAME,age,dept_id) VALUES('小明', 25, 2);
INSERT INTO t_emp(NAME,age,dept_id) VALUES('小红', 23, 3);
INSERT INTO t_emp(NAME,age,dept_id) VALUES('小王', 35, null);

部门表:
在这里插入图片描述
员工表:
在这里插入图片描述
从上面两张图可以看出 总裁部门中没有员工,小王没有部门。

左外连接

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
LEFT JOIN t_dept b
on a.dept_id = b.id;

在这里插入图片描述
以左边表(t_emp )为准,小王没有部门,右边表字段用null来补充。

左外连接(获取t_emp 表独有的)

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
LEFT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null;

在这里插入图片描述

右外连接



在这里插入图片描述
右外连接,以右边表为准,左表中没有的用null补充

右外连接(获取t_dept 表独有的)

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
RIGHT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null;

在这里插入图片描述

内连接

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
INNER JOIN t_dept b
on a.dept_id = b.id

在这里插入图片描述

获取两张表共有的

满外连接

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
FULL OUTER JOIN t_dept b
on a.dept_id = b.id

执行后发现报错了:

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
FULL OUTER JOIN t_dept b
on a.dept_id = b.id
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN t_dept b
on a.dept_id = b.id' at line 3
> 时间: 0.001s

这是因为mysq 不支持 FULL OUTER JOIN

换一种方式:使用UNION

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
LEFT JOIN t_dept b
on a.dept_id = b.id
UNION
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
RIGHT JOIN t_dept b
on a.dept_id = b.id

因为要联合的缘故,不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的一致。

在这里插入图片描述

满外连接(两表独有)

SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
LEFT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null
UNION
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name 
FROM t_emp a 
RIGHT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null;

在这里插入图片描述