32. SQL--join：联合表

1. 前言

Join 是“连接”的意思，顾名思义，SQL JOIN 子句用于将两个或者多个表联合起来进行查询。

联合表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。联合表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

数据库中的表可以通过键将彼此联合起来，一个典型的例子是，将一个表的主键和另一个表的外键进行匹配。在表中，每个主键的值都是唯一的，这样做的目的是在不重复每个表中所有记录的情况下，将表之间的数据交叉捆绑在一起。

2. 语法

使用 SQL JOIN 连接两个表的基本语法如下：

select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是连接条件，只有满足此条件的记录才会合并为一行。您可以使用多个运算符来连接表，例如 =、>、<、<>、<=、>=、!=、between、like 或者 not，但是最常见的是使用 =。

当两个表中有同名的字段时，为了帮助数据库引擎区分是哪个表的字段，在书写同名字段名时需要加上表名，表名和字段名以点号.分隔，如下所示：

table_name.column_name

当然，如果书写的字段名在两个表中是唯一的，也可以不使用以上格式，只写字段名即可。

3. 示例

现在有以下两个表，分别是客户表和订单表。

表1：CUSTOMERS 表

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

表2：ORDERS 表

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

下面使用 SQL JOIN 语句连接连个表：

sql> select id, name, age, amount
     from customers
     join orders
     on  customers.id = orders.customer_id;

执行结果：

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | Khilan   |  25 |   1560 |
|  4 | Chaitali |  25 |   2060 |
+----+----------+-----+--------+

如果您不希望选取表的所有记录，也可以加上where子句，如下所示：

sql> select  id, name, amount, date
     from customers
     join orders
     on customers.id = orders.customer_id;
     where amount >1515
     order by amount;

执行结果：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
+----+----------+--------+---------------------+

on 和 where 的区别
连接表时，sql 会根据连接条件生成一张新的临时表。on 就是连接条件，它决定临时表的生成。where 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 join-on 了。

sql 先根据 on 生成一张临时表，然后再根据 where 对临时表进行筛选。

SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接，如下表所示：

如果不加任何修饰词，只写 join，那么默认为 inner joiin，上面例子中就是如此。

在以上几种连接方式中，inner join、left join、right join、full join 是最常使用的连接方式，掌握它们是学好 sql 的必备基础，下面我们借助示意图来加深读者对这几种连接方式的理解。