MySQL通用Join查询

常见的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%