MySQL 之 join 详解

说明：本学习笔记参考了
慕课网《MySQL开发技巧（一）》视频教程，讲师 sqlercn 。
MySQL开发技巧（一）丨章节
http://www.imooc.com/learn/398

SQL语言介绍
正确使用 SQL 语句是非常重要的。
1、增加数据库的处理效率，减少应用相应时间；
2、减少数据库服务器负载，增加服务器稳定性；
3、减少服务器间通讯的网络流量。

join 语句的类型
1、内连接
2、全外连接
3、左外连接
4、右外连接
5、交叉连接

执行环境：

CREATE TABLE user1(
  id INT PRIMARY KEY AUTO_INCREMENT ,
  user_name VARCHAR(100),
  over VARCHAR(100)
);

INSERT user1 VALUES(NULL,"唐僧","唐僧成佛了");
INSERT user1 VALUES(NULL,"猪八戒","猪八戒成佛了");
INSERT user1 VALUES(NULL,"孙悟空","孙悟空成佛了");
INSERT user1 VALUES(NULL,"沙僧","沙僧成佛了");

CREATE TABLE user2(
  id INT PRIMARY KEY AUTO_INCREMENT ,
  user_name VARCHAR(100),
  over VARCHAR(100)
);

INSERT user2 VALUES(NULL,"孙悟空","成佛");
INSERT user2 VALUES(NULL,"蜘蛛精","被降服");
INSERT user2 VALUES(NULL,"牛魔王","被降服");
INSERT user2 VALUES(NULL,"铁扇公主","被降服");
INSERT user2 VALUES(NULL,"女儿国国王","被降服");

1、内连接

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a
  INNER JOIN user2 b
    ON a.user_name = b.user_name

查询结果：

2、左外连接

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a
  LEFT JOIN user2 b
    ON a.user_name = b.user_name

查询结果：

我们可以看到，作为左表的 user1 表中的记录全部显示，右表中与左表不能匹配的字段值显示为空。

注意：利用左外连接，我们还可以查询出只在左表中出现的数据的集合，只需要再加一个 where 子句就可以了。

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a
  LEFT JOIN user2 b
    ON a.user_name = b.user_name
    WHERE b.user_name IS NULL;

3、右外连接

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a
  RIGHT JOIN user2 b
    ON a.user_name = b.user_name

查询结果：

分析类似左外连接。右边的表 user2 中的记录全部显示。

同样地，在右外连接中也可以查询出只在右外连接中有的记录。

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a
  RIGHT JOIN user2 b
    ON a.user_name = b.user_name
    WHERE a.user_name IS NULL;

4、全外连接
MySQL 并不支持全外连接。但是我们可以使用 union all 语句实现全连接。

SELECT   a.id,  a.user_name,  a.over,  b.id,  b.user_name,  b.over 
FROM user1 a
  LEFT JOIN user2 b
    ON a.user_name = b.user_name
UNION ALL
SELECT   a.id,  a.user_name,  a.over,  b.id,  b.user_name,  b.over 
FROM user1 a
  RIGHT JOIN user2 b
    ON a.user_name = b.user_name

查询结果：

类似地，全连接还可以查询出只存在于两个集合中的元素的集合，也就是把它们的公共数据排除掉。

SELECT   a.id,  a.user_name,  a.over,  b.id,  b.user_name,  b.over 
FROM user1 a
  LEFT JOIN user2 b
    ON a.user_name = b.user_name WHERE b.user_name IS NULL
UNION ALL
SELECT   a.id,  a.user_name,  a.over,  b.id,  b.user_name,  b.over 
FROM user1 a
  RIGHT JOIN user2 b
    ON a.user_name = b.user_name WHERE a.user_name IS NULL

查询结果集：

5、交叉连接

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a,
  user2 b

显示结果：

还可以这样写：

SELECT
  a.id,
  a.user_name,
  a.over,
  b.id,
  b.user_name,
  b.over
FROM user1 a CROSS JOIN
  user2 b

上面回顾了关于 MySQL 表连接的基本知识，下面开始介绍关于 join 的使用技巧。

1、使用 join 更新表

我们的需求是：把同时存在于师徒四人表和悟空兄弟表中的记录的人在师徒四人表中的 over 字段修改为“齐天大圣孙行者”。

UPDATE user1 a 
INNER JOIN ( SELECT b.user_name 
             FROM user1 a INNER JOIN user2 b 
             ON a.user_name = b.user_name) b
ON a.user_name = b.user_name
             SET a.over = '齐天大圣孙行者';

分析：我们使用下面的查询语句，将会得到：

SELECT * FROM user1 a 
INNER JOIN ( SELECT b.user_name 
             FROM user1 a INNER JOIN user2 b 
             ON a.user_name = b.user_name) b
ON a.user_name = b.user_name

关键：如果我们去修改悟空兄弟表中的 over 字段，就会报错。

2、使用 join 优化子查询

需求1：我们想查询 user1 表中 user_name 字段在 user2 表中有的。
我们最最直接的想法：

SELECT a.id,a.user_name,a.over
FROM user1 a 
WHERE a.user_name IN(SELECT b.user_name FROM user2 b);

但是这样做效率并不高，为此，我们可以这样优化：

SELECT a.id,a.user_name,a.over FROM user1 a
 JOIN user2 b ON a.user_name=b.user_name

注意： join 如果什么都不写，默认是内连接。

查询得到的结果集是一样的。

注意：如果 user1 这张表与 user2 这张表是一对多关系的话，要注意去重。可以简单思考一下为什么会有重复。

测试语句：

INSERT INTO user2 VALUES(NULL,'孙悟空','成为了齐天大圣');

最佳实践：

SELECT DISTINCT a.id,a.user_name,a.over FROM user1 a
 JOIN user2 b ON a.user_name=b.user_name

需求2：我想查询出 user1 里面 id、user_name ，user2 里面的 over 字段，一定要 user_name 匹配才行。