MySQL高级知识——JOIN

MySQL中各种连接操作

以person和address两张表为例进行说明，两表结构如下：

select * from person;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | AAA  |   18 |
|  2 | BBB  |   25 |
|  3 | CCC  |   21 |
+----+------+------+
3 rows in set (0.00 sec)

select * from address;
+----+----------+
| id | address  |
+----+----------+
|  1 | HOME_AAA |
|  2 | HOME_BBB |
|  4 | HOME_DDD |
+----+----------+
3 rows in set (0.08 sec)

1.内连接

1.1 自然连接（natural join）

自然连接只考虑两个关系中在共同属性上取值相同的元组对。结果中无null，不需要使用修饰词限制连接属性。
在本例中，共同属性为id，结果如下：

select * from person natural join address;
+----+------+------+----------+
| id | name | age  | address  |
+----+------+------+----------+
|  1 | AAA  |   18 | HOME_AAA |
|  2 | BBB  |   25 | HOME_BBB |
+----+------+------+----------+
2 rows in set (0.05 sec)

1.2 内连接（inner join）

默认情况下inner join和join两种语法等价。
对于内连接，必须使用using或on指定连接属性/条件，否则产生的结果与交叉连接相同（结果均为两个表的笛卡尔积），在后文中介绍。

使用using指定连接属性，结果中只包含1个id属性：

select * from person join address using(id);
+----+------+------+----------+
| id | name | age  | address  |
+----+------+------+----------+
|  1 | AAA  |   18 | HOME_AAA |
|  2 | BBB  |   25 | HOME_BBB |
+----+------+------+----------+
2 rows in set (0.00 sec)

使用on指定连接条件，结果中包含2个id属性：

select * from person inner join address on person.id = address.id;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  2 | HOME_BBB |
+----+------+------+----+----------+
2 rows in set (0.00 sec)

2.外连接

2.1 左外连接（left outer join）

默认情况下left outer join和left join两种语法等价。
以左边的表为主，在右边的表中找到所有满足条件的元素，并把他们连接起来，如果没有对应的元素，则在相应位置上的值为null。
特别注意：使用外连接必须使用using或on指定连接属性或连接条件，否则会报错。

使用using指定连接属性，结果中只包含1个id属性：

select * from person left outer join address using(id);
+----+------+------+----------+
| id | name | age  | address  |
+----+------+------+----------+
|  1 | AAA  |   18 | HOME_AAA |
|  2 | BBB  |   25 | HOME_BBB |
|  3 | CCC  |   21 | NULL     |
+----+------+------+----------+
3 rows in set (0.00 sec)

使用on指定连接条件，结果中包含2个id属性：

select * from person left outer join address on person.id = address.id;
+----+------+------+------+----------+
| id | name | age  | id   | address  |
+----+------+------+------+----------+
|  1 | AAA  |   18 |    1 | HOME_AAA |
|  2 | BBB  |   25 |    2 | HOME_BBB |
|  3 | CCC  |   21 | NULL | NULL     |
+----+------+------+------+----------+
3 rows in set (0.00 sec)

2.2 右外连接（right outer join）

默认情况下right outer join和right join两种语法等价。
与左外连接相反，以右边的表为主，在左边的表中找到所有满足条件的元素，并把他们连接起来，如果没有对应的元素，则在相应位置上的值为null。
特别注意：使用外连接必须使用using或on指定连接属性或连接条件，否则会报错。

使用using指定连接属性，结果中只包含1个id属性：

select * from person right outer join address using(id);
+----+----------+------+------+
| id | address  | name | age  |
+----+----------+------+------+
|  1 | HOME_AAA | AAA  |   18 |
|  2 | HOME_BBB | BBB  |   25 |
|  4 | HOME_DDD | NULL | NULL |
+----+----------+------+------+
3 rows in set (0.04 sec)

使用on指定连接条件，结果中包含2个id属性：

select * from person right outer join address on person.id = address.id;
+------+------+------+----+----------+
| id   | name | age  | id | address  |
+------+------+------+----+----------+
|    1 | AAA  |   18 |  1 | HOME_AAA |
|    2 | BBB  |   25 |  2 | HOME_BBB |
| NULL | NULL | NULL |  4 | HOME_DDD |
+------+------+------+----+----------+
3 rows in set (0.02 sec)

2.3 全外连接（full outer join）

全外连接显示所有表的行、列，条件不匹配的值为皆为null。
特别注意：在MySQL中不支持全外连接操作，可通过Union左外连接和右外连接来实现。

使用on指定连接条件，结果中包含2个id属性：

mysql> select * from person left outer join address on person.id = address.id
    -> union
    -> select * from person right outer join address on person.id = address.id;
+------+------+------+------+----------+
| id   | name | age  | id   | address  |
+------+------+------+------+----------+
|    1 | AAA  |   18 |    1 | HOME_AAA |
|    2 | BBB  |   25 |    2 | HOME_BBB |
|    3 | CCC  |   21 | NULL | NULL     |
| NULL | NULL | NULL |    4 | HOME_DDD |
+------+------+------+------+----------+
4 rows in set (0.40 sec)

---

3.交叉连接（cross join）

交叉连接所返回结果即为笛卡尔积，即：对于两个不同的集合A和B，对于A中的每一个元素，都有对于在B中的所有元素做连接运算。因此对于两个元组分别为m，n的表，笛卡尔积后得到的元组个数为m x n。
由于交叉连接与笛卡尔积结果相同，因此如下两种写法相同：

写法1：交叉连接cross join

select * from person cross join address;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  1 | HOME_AAA |
|  3 | CCC  |   21 |  1 | HOME_AAA |
|  1 | AAA  |   18 |  2 | HOME_BBB |
|  2 | BBB  |   25 |  2 | HOME_BBB |
|  3 | CCC  |   21 |  2 | HOME_BBB |
|  1 | AAA  |   18 |  4 | HOME_DDD |
|  2 | BBB  |   25 |  4 | HOME_DDD |
|  3 | CCC  |   21 |  4 | HOME_DDD |
+----+------+------+----+----------+
9 rows in set (0.00 sec)

写法2：笛卡尔积

select * from person,address;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  1 | HOME_AAA |
|  3 | CCC  |   21 |  1 | HOME_AAA |
|  1 | AAA  |   18 |  2 | HOME_BBB |
|  2 | BBB  |   25 |  2 | HOME_BBB |
|  3 | CCC  |   21 |  2 | HOME_BBB |
|  1 | AAA  |   18 |  4 | HOME_DDD |
|  2 | BBB  |   25 |  4 | HOME_DDD |
|  3 | CCC  |   21 |  4 | HOME_DDD |
+----+------+------+----+----------+
9 rows in set (0.00 sec)

当使用内链接不加连接属性或条件时，结果也为笛卡尔积：

select * from person inner join address;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  1 | HOME_AAA |
|  3 | CCC  |   21 |  1 | HOME_AAA |
|  1 | AAA  |   18 |  2 | HOME_BBB |
|  2 | BBB  |   25 |  2 | HOME_BBB |
|  3 | CCC  |   21 |  2 | HOME_BBB |
|  1 | AAA  |   18 |  4 | HOME_DDD |
|  2 | BBB  |   25 |  4 | HOME_DDD |
|  3 | CCC  |   21 |  4 | HOME_DDD |
+----+------+------+----+----------+
9 rows in set (0.09 sec)

---

4.连接语句中on与where的区别

on和where的查询顺序不同。
标准查询关键字执行顺序为：from > where > group by > having > order by
在连接语句中，join是在from范围内，而on条件是对join进行条件过滤，所以会先on条件筛选表，然后再做join；对于where来说，是在join执行后生成的临时表上再进行条件过滤。
以right join的on条件和where条件进行举例说明：

例1：on条件中增加对左表的限制条件

结果：返回右表的全部记录，左表的筛选条件生效

select * from person right join address on person.id = address.id and person.age<20;
+------+------+------+----+----------+
| id   | name | age  | id | address  |
+------+------+------+----+----------+
|    1 | AAA  |   18 |  1 | HOME_AAA |
| NULL | NULL | NULL |  2 | HOME_BBB |
| NULL | NULL | NULL |  4 | HOME_DDD |
+------+------+------+----+----------+
3 rows in set (0.00 sec)

例2：on条件后增加where限制条件

结果：where后边的条件是对最终的临时表进行筛选，on后边的条件是作为匹配条件进行筛选

select * from person right join address on person.id = address.id where person.age<20;
+------+------+------+----+----------+
| id   | name | age  | id | address  |
+------+------+------+----+----------+
|    1 | AAA  |   18 |  1 | HOME_AAA |
+------+------+------+----+----------+
1 row in set (0.03 sec)

---

附录：SQL连接方式图片说明