MySQL join多表连接知识详解

2021-12-09 17:09:34

1. SQL join多表连接介绍

SQL Join子句，主要用在select语句中，把两个或多个表的行结合起来，基于这些表之间的共同字段（往往是id字段）来查询，从多个表中返回满足条件的所有行。

2.常见join子句类型

常见join子句类型有INNER JOIN（同JOIN）、LEFT JOIN、RIGHT JOIN、FULL JOIN，其中第一种为内连接，后三种为外连接。

不同的join子句类型区别如下图所示：

3.实践数据准备

#1.建表语句和内容插入。a是左边的表，b是右边的表
create table a(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);)
create table b(aid int not null,city varchar(20) not null,telnum char(11));
insert into a values(1,'oldboy',35),(2,'oldgirl',28),(3,'inca',22),(5,'zs',23);
insert into b values(1,'bj',135),(2,'sz',189),(3,'sh',166),(4,'hz',187);
#2.最终数据结果。
mysql> select * from a;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | oldboy  |   35 |
|  2 | oldgirl |   28 |
|  3 | inca    |   22 |
|  5 | zs      |   23 |
+----+---------+------+
4 rows in set (0.00 sec)

mysql> select * from b;
+-----+------+--------+
| aid | city | telnum |
+-----+------+--------+
|   1 | bj   | 135    |
|   2 | sz   | 189    |
|   3 | sh   | 166    |
|   4 | hz   | 187    |
+-----+------+--------+
4 rows in set (0.00 sec)
#提示：注意两个表的ID列有相同的id，也有不同的ID

4. 内连接(inner join)实践

内连接是生产最常见的连接，只输出关联ID匹配的行。内连接有三种语句，都是等同的。

mysql> select * from a join b on a.id=b.aid;         #<==省略了inner关键词。
mysql> select * from a inner join b on a.id=b.aid;   #<==带inner关键词。
mysql> select * from a,b where a.id=b.aid;

以上3个语句输出的信息相同，即为两个表中id相同的行信息。

+----+---------+------+-----+------+--------+
| id | name    | age  | aid | city | telnum |
+----+---------+------+-----+------+--------+
|  1 | oldboy  |   35 |   1 | bj   | 135    |
|  2 | oldgirl |   28 |   2 | sz   | 189    |
|  3 | inca    |   22 |   3 | sh   | 166    |

5. 左外连接(left join)实践

LEFT JOIN子句会返回左表全部行和右表满足ON关联条件行，如果左表行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。

mysql> select * from a left join b on a.id=b.aid; #执行SQL输出结果如下。
+----+---------+------+------+------+--------+
| id | name    | age  | aid  | city | telnum |
+----+---------+------+------+------+--------+
|  1 | oldboy  |   35 |    1 | bj   | 135    |
|  2 | oldgirl |   28 |    2 | sz   | 189    |
|  3 | inca    |   22 |    3 | sh   | 166    |
|  5 | zs      |   23 | NULL | NULL | NULL   |
+----+---------+------+------+------+--------+

6. 右外连接(right join)实践

RIGHT JOIN子句会右表全部行和左表满足ON关联条件行，如果右表行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。

mysql> select * from a right outer join b on a.id=b.aid;  #<==outer可省略。
+------+---------+------+-----+------+--------+
| id   | name    | age  | aid | city | telnum |
+------+---------+------+-----+------+--------+
|    1 | oldboy  |   35 |   1 | bj   | 135    |
|    2 | oldgirl |   28 |   2 | sz   | 189    |
|    3 | inca    |   22 |   3 | sh   | 166    |
| NULL | NULL    | NULL |   4 | hz   | 187    |
+------+---------+------+-----+------+--------+
mysql> select * from a right join b on a.id=b.aid;
+------+---------+------+-----+------+--------+
| id   | name    | age  | aid | city | telnum |
+------+---------+------+-----+------+--------+
|    1 | oldboy  |   35 |   1 | bj   | 135    |
|    2 | oldgirl |   28 |   2 | sz   | 189    |
|    3 | inca    |   22 |   3 | sh   | 166    |
| NULL | NULL    | NULL |   4 | hz   | 187    |
+------+---------+------+-----+------+--------+

7.外连接(outer join)实践

outer join有的库叫做FULL JOIN会返回左表和右表所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替，不过mysql暂不支持这种连接，但可以用union all连接左连接和右连接来实现。

mysql> (select * from a left join b on a.id=b.aid)
    -> union all
    -> (select * from a right join b on a.id=b.aid);
+------+---------+------+------+------+--------+
| id   | name    | age  | aid  | city | telnum |
+------+---------+------+------+------+--------+
|    1 | oldboy  |   35 |    1 | bj   | 135    |
|    2 | oldgirl |   28 |    2 | sz   | 189    |
|    3 | inca    |   22 |    3 | sh   | 166    |
|    5 | zs      |   23 | NULL | NULL | NULL   |
|    1 | oldboy  |   35 |    1 | bj   | 135    |
|    2 | oldgirl |   28 |    2 | sz   | 189    |
|    3 | inca    |   22 |    3 | sh   | 166    |
| NULL | NULL    | NULL |    4 | hz   | 187    |
+------+---------+------+------+------+--------+

8.老男孩后记：

有关mysql的join，其实可以简单得执行? join，查看MySQL支持的连接类型及例子，一般人我都不告诉他。

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}
join_specification: {
    ON search_condition
  | USING (join_column_list)
}

有关多表连接的真实项目案例可参考老男孩的多表连接实践文章：https://www.cnblogs.com/oldboy666/p/15637461.html

码农公寓