mysql内链接与交叉连接_【数据库】MySQL连接-内连接、外连接、交叉连接

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连接方式图片说明