MySQL通用Join查询

发表于 更新于 分类于 Disqus：
在日常的开发中，MySQL的Join查询是必不可少的，通过本篇文章来彻底弄清楚Join查询的常见类型，以及MySQL全外连接中UNION/UNION ALL 的区别及用法.

常见的Join查询类型

通过Google图库搜索到的，点击图片可以放大查看，介绍了7中常见的Join查询。

测试用例

建表语句

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `locAdd` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select * from dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+

mysql> select * from emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+

内连接

内连接可以理解为查询符合查询条件的两张表的交集，对应center位置的图.

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select name, deptName from dept t1 inner join emp t2 where t1.id = t2.deptId;
+------+----------+
| name | deptName |
+------+----------+
| z3   | RD       |
| z4   | RD       |
| z5   | RD       |
| w5   | HR       |
| w6   | HR       |
| s7   | MK       |
| s8   | MIS      |
+------+----------+

结果中不包含epm表中id为8和dept表中id为5的记录.

左外连接

左外连接分为2种情况：

  1. 查询结果包含交集，对应最左上角的图.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> select deptName, name from dept t1 left join emp t2 on t1.id = t2.deptId;
    +----------+------+
    | deptName | name |
    +----------+------+
    | RD       | z3   |
    | RD       | z4   |
    | RD       | z5   |
    | HR       | w5   |
    | HR       | w6   |
    | MK       | s7   |
    | MIS      | s8   |
    | FD       | NULL |
    +----------+------+

    结果中包含了左表dept与右表emp交集记录以及除交集外的所有左表记录.

  2. 查询结果不包含交集，对应中间左边的图.

    1
    2
    3
    4
    5
    6
    7
    mysql> select deptName, name from dept t1 left join emp t2 on t1.id = t2.deptId
        -> where t2.deptId is null;
    +----------+------+
    | deptName | name |
    +----------+------+
    | FD       | NULL |
    +----------+------+

    结果中包含了左表dept表所有记录与(左表dept与右表emp交集)的差集记录.

右外连接

和左外连接类似，右外链接也分为2种：

  1. 查询结果包含交集，对应右上角的图.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> select deptName, name from dept t1 right join emp t2 on t1.id = t2.deptId;
    +----------+------+
    | deptName | name |
    +----------+------+
    | RD       | z3   |
    | RD       | z4   |
    | RD       | z5   |
    | HR       | w5   |
    | HR       | w6   |
    | MK       | s7   |
    | MIS      | s8   |
    | NULL     | s9   |
    +----------+------+

    结果包含右表emp表与左表dept的交集以及除交集外的所有右表的记录.

  2. 查询结果不包含交集，对应中间右边的图.

    1
    2
    3
    4
    5
    6
    7
    mysql> select deptName, name from dept t1 right join emp t2 on t1.id = t2.deptId
        -> where t1.id is null;
    +----------+------+
    | deptName | name |
    +----------+------+
    | NULL     | s9   |
    +----------+------+

    结果包含右表所有记录与(右表emp表与左表dept的交集)的差集记录.

全外连接(UNION)

MySQL中不存在FULL OUTER JOIN，使用 UNION / UNION ALL 来达到全外连接的效果.
全外连接也包含2种情况：

  1. 查询结果包含交集，对应左下角的图.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    mysql> select deptName, name from dept t1 left join emp t2 on t1.id = t2.deptId
        -> union
        -> select deptName, name from dept t1 right join emp t2 on t1.id = t2.deptId;
    +----------+------+
    | deptName | name |
    +----------+------+
    | RD       | z3   |
    | RD       | z4   |
    | RD       | z5   |
    | HR       | w5   |
    | HR       | w6   |
    | MK       | s7   |
    | MIS      | s8   |
    | FD       | NULL |
    | NULL     | s9   |
    +----------+------+

    结果中包含了dept和emp表的所有记录.

  2. 查询结果不包含交集，对应右下角的图.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> select deptName, name from dept t1 left join emp t2 on t1.id = t2.deptId where t2.deptId is null
        -> union
        -> select deptName, name from dept t1 right join emp t2 on t1.id = t2.deptId where t1.id is null;
    +----------+------+
    | deptName | name |
    +----------+------+
    | FD       | NULL |
    | NULL     | s9   |
    +----------+------+

    结果中包含了dept和emp表除交集外的所有数据.

UNION/UNION ALL

MySQL使用UNION/UNION ALL 来达到全外连接的效果，接着上面的测试用例.

  1. UNION

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    mysql> select deptName, name from dept t1 left join emp t2 on t1.id = t2.deptId
        -> union
        -> select deptName, name from dept t1 right join emp t2 on t1.id = t2.deptId;
    +----------+------+
    | deptName | name |
    +----------+------+
    | RD       | z3   |
    | RD       | z4   |
    | RD       | z5   |
    | HR       | w5   |
    | HR       | w6   |
    | MK       | s7   |
    | MIS      | s8   |
    | FD       | NULL |
    | NULL     | s9   |
    +----------+------+

  2. UNION ALL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    mysql> select deptName, name from dept t1 left join emp t2 on t1.id = t2.deptId
        -> union all
        -> select deptName, name from dept t1 right join emp t2 on t1.id = t2.deptId;
    +----------+------+
    | deptName | name |
    +----------+------+
    | RD       | z3   |
    | RD       | z4   |
    | RD       | z5   |
    | HR       | w5   |
    | HR       | w6   |
    | MK       | s7   |
    | MIS      | s8   |
    | FD       | NULL |
    | RD       | z3   |
    | RD       | z4   |
    | RD       | z5   |
    | HR       | w5   |
    | HR       | w6   |
    | MK       | s7   |
    | MIS      | s8   |
    | NULL     | s9   |
    +----------+------+

  3. 结论
    UNION 会去除重复记录；UNION ALL不会去除重复记录，只是进行简单的合并.
    从效率上来讲 UNION ALL 的效率更高.

0%