MySQL的七种连接

刚学习Java和数据库，文章中讲的不对的地方，还请各位大神多多指教！

本文参考链接(https://blog.csdn.net/qq_50596778/article/details/123145434)

————————————————
版权声明：本文为CSDN博主「NeverOW」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_50596778/article/details/123145434

简介

图上画了七种连接，最主要的还是左连接(LEFT JOIN)、右连接(RIGHT JOIN)、内连接(INNER JOIN)这3种，其他四种连接都是在这3种的基础上演变的。

开始练习

建表

部门表

-- 创建前先删除表
DROP TABLE IF EXISTS `dept`;
-- 创建部门表
CREATE TABLE `dept` (
	`dept_id` int(11) NOT NULL AUTO_INCREMENT,
    `dept_name` varchar(30) DEFAULT NULL,
    `dept_number` int(11) DEFAULT NULL,
    PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('1', 'AA', '100');
INSERT INTO `dept` VALUES ('2', 'BB', '200');
INSERT INTO `dept` VALUES ('3', 'CC', '300');
INSERT INTO `dept` VALUES ('4', 'DD', '400');
INSERT INTO `dept` VALUES ('5', 'HH', '500');

员工表

-- 创建前先删除表
DROP TABLE IF EXISTS `emp`;
-- 创建员工表
CREATE TABLE `emp` (
    `emp_id` int(11) NOT NULL AUTO_INCREMENT,
    `emp_name` varchar(30) DEFAULT NULL,
    `emp_age` int(11) DEFAULT NULL,
    `dept_id` int(11) NOT NULL,
    PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES('1', 'zhangsan', '20', '1');
INSERT INTO `emp` VALUES('2', 'lisi', '25', '6');
INSERT INTO `emp` VALUES('3', 'wangwu', '19', '4');
INSERT INTO `emp` VALUES('4', 'zhaoliu', '29', '5');
INSERT INTO `emp` VALUES('5', 'xiaohong', '30', '2');
INSERT INTO `emp` VALUES('6', 'xiaohu', '26', '3');
INSERT INTO `emp` VALUES('7', 'zhangle', '23', '3');
INSERT INTO `emp` VALUES('8', 'qingtian', '38', '3');
INSERT INTO `emp` VALUES('9', 'xiayutian', '36', '2');
INSERT INTO `emp` VALUES('10', 'fangjia', '40', '1');

建表结果：

dept表：

emp表：

1.左连接（left join）

A独有的+AB的交集

执行SQL语句

SELECT * FROM emp AS e LEFT JOIN dept AS d ON e.dept_id=d.dept_id;

执行结果：

结果分析：通过SQL语句可以看出，查询的是所有字段，主表为emp，从表为dept，相当于两个for循环嵌套，外层为emp，内层为dept，所以在结果中emp表的字段（emp_id 到 dept_id）的值的顺序跟原来表中的一样，dept表中的字段（dept_id(2) 到 dept_number）的值需要根据emp表中的dept_id在dept表中查找，如果dept_id相等就返回对应的值，如果emp表中的dept_id在dept表中不存在，那些值都等于null（结果中的第二条）。

2.右连接(right join)

B的独有和AB的交集

执行SQL语句

SELECT * FROM emp AS e RIGHT JOIN dept AS d ON e.dept_id=d.dept_id;

执行执行结果：

结果分析：主表为dept，从表为emp，相当于两个for循环嵌套，外层为dept，内层为emp，dept表的dept_id是从小到大排序的，所以结果也会按照这个顺序排序（结果中的dept_id(2)字段）。查找时先查dept_id=1，emp中有两条，返回2条结果，然后再查dept_id=2，返回2条结果，一直查到dept_id=5，总的结果数为 2+2+3+1+1=9条。

3.内连接(inner join)

AB的交集

执行SQL语句

SELECT * FROM emp AS e INNER JOIN dept AS d ON e.dept_id=d.dept_id;

执行结果：

结果分析：比左连接的结果少了dept_id=6这一条，因为只有一个表中有dept_id=6，交集是两个表中的dept_id值相等。

4.左外连接(left join且右表=null)

A中独有

执行SQL语句

SELECT * FROM emp AS e LEFT JOIN dept AS d ON e.dept_id=d.dept_id WHERE d.dept_id is null;

执行结果：

结果分析：左外连接是在左连接的基础上增加了一个条件d.dept_id is null，所以结果只有一条。

5. 右外连接(right join且左表=null)

B中独有

1.先插入一条数据，插入一条左表没有的dept_id值

INSERT INTO `dept` VALUES ('11', 'XX', '1000');

2.执行右外连接的SQL语句

SELECT * FROM emp AS e RIGHT JOIN dept AS d ON e.dept_id=d.dept_id WHERE e.dept_id is null;

执行结果

结果分析：右外连接是在右连接的基础上增加了一个条件e.dept_id is null，之前右连接结果中的e.dept_id都是有值的，如果直接执行右外连接的SQL语句，得到的结果是空的；我们插入一条数据后，返回的结果就是我们刚插入的数据。

6. 全连接(full outer join)

A中独有+B中独有+AB的交集

注意：MySQL不支持FULL OUTER JOIN(在ORACLE支持)，因此使用UNION的方式来实现，可以

合并+去重

应用场景：

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：

1、要求多条查询语句的查询列数是一致的

2、要求多条查询语句的查询的每一列的类型和顺序最好一致

3、union关键字默认去重，如果使用union all 可以包含重复项

执行SQL语句

SELECT * FROM emp AS e LEFT JOIN dept AS d ON e.dept_id=d.dept_id 
UNION 
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id=d.dept_id WHERE e.dept_id is null;

执行结果

结果解析：全连接=左连接+右外连接，左连接的结果为emp_id=1~10，右外连接的结果为最后一条。

7. 全外连接(full outer join且左右表=null)

A中独有+B中独有

执行SQL语句

SELECT * from emp e LEFT JOIN dept d ON e.dept_id=d.dept_id WHERE d.dept_id is null 
UNION 
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id=d.dept_id WHERE e.dept_id is null;

执行结果

结果解析：全外连接=左外连接+右外连接，左外连接结果为第一条，右外连接结果为第二条。

Do what you think is right

做你认为正确的事