MYSQL联结（Join）

1 联结的作用和分类

我们都知道Mysql数据库是一种关系型数据库，这种数据库的最大特点之一是每个数据表之间都通过外键相关联。关系型数据库的优点可以避免很多不必要的重复，例如我们有一些产品和供应商的数据字段，如果有一个供应了很多产品，则会在产品后面重复写入很多次供应商，从而造成数据的冗余。另外，使用两个数据表存储产品和供应商，在更改供应商信息时，只需更改一次即可。

联结是一种关联两个或多个数据表时的一种操作，具体可以分为笛卡尔积（叉联结）、内联结、自联结、外联结，外联结又可以分为左外联结和右外联结。

2 笛卡尔积

笛卡尔积是一种类似于广播的联结方式，我们接下来的均以“mysql必知必会”一书中的数据表举例。

select prod_id, vendors.vend_id from products, vendors;

输出：

+---------+---------+
| prod_id | vend_id |
+---------+---------+
| ANV01   |    1001 |
| ANV01   |    1002 |
| ANV01   |    1003 |
| ANV01   |    1004 |
| ANV01   |    1005 |
| ANV01   |    1006 |
| ANV02   |    1001 |
| ANV02   |    1002 |
| ANV02   |    1003 |
| ANV02   |    1004 |
| ANV02   |    1005 |
| ANV02   |    1006 |
| ANV03   |    1001 |
| ANV03   |    1002 |
| ANV03   |    1003 |
| ANV03   |    1004 |
| ANV03   |    1005 |
| ANV03   |    1006 |
| FU1     |    1001 |
| FU1     |    1002 |
| FU1     |    1003 |
| FU1     |    1004 |
| FU1     |    1005 |
| FU1     |    1006 |
| OL1     |    1001 |
| OL1     |    1002 |
| OL1     |    1003 |
| OL1     |    1004 |
| OL1     |    1005 |
| OL1     |    1006 |
| DTNTR   |    1001 |
| DTNTR   |    1002 |
| DTNTR   |    1003 |
| DTNTR   |    1004 |
| DTNTR   |    1005 |
| DTNTR   |    1006 |
| FB      |    1001 |
| FB      |    1002 |
| FB      |    1003 |
| FB      |    1004 |
| FB      |    1005 |
| FB      |    1006 |
| FC      |    1001 |
| FC      |    1002 |
| FC      |    1003 |
| FC      |    1004 |
| FC      |    1005 |
| FC      |    1006 |
| SAFE    |    1001 |
| SAFE    |    1002 |
| SAFE    |    1003 |
| SAFE    |    1004 |
| SAFE    |    1005 |
| SAFE    |    1006 |
| SLING   |    1001 |
| SLING   |    1002 |
| SLING   |    1003 |
| SLING   |    1004 |
| SLING   |    1005 |
| SLING   |    1006 |
| TNT1    |    1001 |
| TNT1    |    1002 |
| TNT1    |    1003 |
| TNT1    |    1004 |
| TNT1    |    1005 |
| TNT1    |    1006 |
| TNT2    |    1001 |
| TNT2    |    1002 |
| TNT2    |    1003 |
| TNT2    |    1004 |
| TNT2    |    1005 |
| TNT2    |    1006 |
| JP1000  |    1001 |
| JP1000  |    1002 |
| JP1000  |    1003 |
| JP1000  |    1004 |
| JP1000  |    1005 |
| JP1000  |    1006 |
| JP2000  |    1001 |
| JP2000  |    1002 |
| JP2000  |    1003 |
| JP2000  |    1004 |
| JP2000  |    1005 |
| JP2000  |    1006 |
+---------+---------+
84 rows in set (0.00 sec)

products数据表中一共有14行数据，vendors表中一共有6行数据，则笛卡尔积一共有14*6行的数据。我们可以发现它实际上是一个表向另一个表进行广播操作。

3 内联结

内联结是基于2个或多个表之间的等值联结方式，可以使用···inner join···on的方式。

例如查询所有产品的供应商。

select prod_id, vendors.vend_name from products inner join vendors on products.vend_id = vendors.vend_id;

输出：

+---------+-------------+
| prod_id | vend_name   |
+---------+-------------+
| ANV01   | Anvils R Us |
| ANV02   | Anvils R Us |
| ANV03   | Anvils R Us |
| FU1     | LT Supplies |
| OL1     | LT Supplies |
| DTNTR   | ACME        |
| FB      | ACME        |
| FC      | ACME        |
| SAFE    | ACME        |
| SLING   | ACME        |
| TNT1    | ACME        |
| TNT2    | ACME        |
| JP1000  | Jet Set     |
| JP2000  | Jet Set     |
+---------+-------------+
14 rows in set (0.00 sec)

4 自联结

自联结实际上也是内联结的一种特殊情况，只不过自联结使用了两个相同的表作为内联结。例如我们要查找和ANV01具有相同供应商的所有产品ID，这实际上有两种查找方式，一是使用子查询，二是使用自联结查询。

子查询需要我们现在字句中查找ANV01供应商的ID，然后再将供应商ID作为where的条件查找产品ID。

select prod_id from products where vend_id in (select vend_id from products where prod_id = "ANV01");

输出：

+---------+
| prod_id |
+---------+
| ANV01   |
| ANV02   |
| ANV03   |
+---------+
3 rows in set (0.00 sec)

我们需要知道虽然子查询逻辑简单，但是查询的性能远不如联结查询，所以还需要在查询语句中使用自联结查询完成上述需求。自联结的查询逻辑稍微有些繁琐，这里需要读者简单在脑中回顾一下笛卡尔积和内联结查询的工作原理，上述子查询中使用了两次在products表中的操作，所以我们可以将两个prodocts表做一个笛卡尔积并加上供应商ID相等的限制条件，这实际上也是一个内联结查询的操作。

select p1.prod_id, p1.vend_id, p2.prod_id, p2.vend_id from products as p1 inner join products as p2 where p1.vend_id = p2.vend_id;

输出：

+---------+---------+---------+---------+
| prod_id | vend_id | prod_id | vend_id |
+---------+---------+---------+---------+
| ANV01   |    1001 | ANV01   |    1001 |
| ANV01   |    1001 | ANV02   |    1001 |
| ANV01   |    1001 | ANV03   |    1001 |
| ANV02   |    1001 | ANV01   |    1001 |
| ANV02   |    1001 | ANV02   |    1001 |
| ANV02   |    1001 | ANV03   |    1001 |
| ANV03   |    1001 | ANV01   |    1001 |
| ANV03   |    1001 | ANV02   |    1001 |
| ANV03   |    1001 | ANV03   |    1001 |
| FU1     |    1002 | FU1     |    1002 |
| FU1     |    1002 | OL1     |    1002 |
| OL1     |    1002 | FU1     |    1002 |
| OL1     |    1002 | OL1     |    1002 |
| DTNTR   |    1003 | DTNTR   |    1003 |
| DTNTR   |    1003 | FB      |    1003 |
| DTNTR   |    1003 | FC      |    1003 |
| DTNTR   |    1003 | SAFE    |    1003 |
| DTNTR   |    1003 | SLING   |    1003 |
| DTNTR   |    1003 | TNT1    |    1003 |
| DTNTR   |    1003 | TNT2    |    1003 |
| FB      |    1003 | DTNTR   |    1003 |
| FB      |    1003 | FB      |    1003 |
| FB      |    1003 | FC      |    1003 |
| FB      |    1003 | SAFE    |    1003 |
| FB      |    1003 | SLING   |    1003 |
| FB      |    1003 | TNT1    |    1003 |
| FB      |    1003 | TNT2    |    1003 |
| FC      |    1003 | DTNTR   |    1003 |
| FC      |    1003 | FB      |    1003 |
| FC      |    1003 | FC      |    1003 |
| FC      |    1003 | SAFE    |    1003 |
| FC      |    1003 | SLING   |    1003 |
| FC      |    1003 | TNT1    |    1003 |
| FC      |    1003 | TNT2    |    1003 |
| SAFE    |    1003 | DTNTR   |    1003 |
| SAFE    |    1003 | FB      |    1003 |
| SAFE    |    1003 | FC      |    1003 |
| SAFE    |    1003 | SAFE    |    1003 |
| SAFE    |    1003 | SLING   |    1003 |
| SAFE    |    1003 | TNT1    |    1003 |
| SAFE    |    1003 | TNT2    |    1003 |
| SLING   |    1003 | DTNTR   |    1003 |
| SLING   |    1003 | FB      |    1003 |
| SLING   |    1003 | FC      |    1003 |
| SLING   |    1003 | SAFE    |    1003 |
| SLING   |    1003 | SLING   |    1003 |
| SLING   |    1003 | TNT1    |    1003 |
| SLING   |    1003 | TNT2    |    1003 |
| TNT1    |    1003 | DTNTR   |    1003 |
| TNT1    |    1003 | FB      |    1003 |
| TNT1    |    1003 | FC      |    1003 |
| TNT1    |    1003 | SAFE    |    1003 |
| TNT1    |    1003 | SLING   |    1003 |
| TNT1    |    1003 | TNT1    |    1003 |
| TNT1    |    1003 | TNT2    |    1003 |
| TNT2    |    1003 | DTNTR   |    1003 |
| TNT2    |    1003 | FB      |    1003 |
| TNT2    |    1003 | FC      |    1003 |
| TNT2    |    1003 | SAFE    |    1003 |
| TNT2    |    1003 | SLING   |    1003 |
| TNT2    |    1003 | TNT1    |    1003 |
| TNT2    |    1003 | TNT2    |    1003 |
| JP1000  |    1005 | JP1000  |    1005 |
| JP1000  |    1005 | JP2000  |    1005 |
| JP2000  |    1005 | JP1000  |    1005 |
| JP2000  |    1005 | JP2000  |    1005 |
+---------+---------+---------+---------+
66 rows in set (0.00 sec)

上述代码查询出了在同一个供应商下的不同产品的笛卡尔积，例如前三行中p1表对应的是“ANV01”产品，p2表对应的是所有“1001”供应商的产品。

select p2.prod_id, p2.vend_id from products as p1 inner join products as p2 where p1.vend_id = p2.vend_id and p1.prod_id="ANV01";

输出：

+---------+---------+
| prod_id | vend_id |
+---------+---------+
| ANV01   |    1001 |
| ANV02   |    1001 |
| ANV03   |    1001 |
+---------+---------+
3 rows in set (0.00 sec)

自联结查询在工作中应用的也是相当广泛，例如查询回访用户的个数等。

5 外联结

外联结主要用于查询某一侧表的全部结果，例如：

对每个客户下了多少单进行计数，同时包括那些未购买的客户
列出所有产品以及订购数量，包括没有人订购的产品
计算平均销售规模，包括哪些至今尚未下单的用户

由于外部联结比较简单，所以直接给出参考答案：

对每个客户下了多少单进行计数，同时包括那些未购买的客户

select c.cust_id, count(order_num) from customers as c left join orders as o on c.cust_id = o.cust_id group by c.cust_id;

输出：

+---------+------------------+
| cust_id | count(order_num) |
+---------+------------------+
|   10001 |                2 |
|   10002 |                0 |
|   10003 |                1 |
|   10004 |                1 |
|   10005 |                1 |
+---------+------------------+
5 rows in set (0.00 sec)

列出所有产品以及订购数量，包括没有人订购的产品

select p.prod_id, quantity from products as p left join orderitems as oi on p.prod_id = oi.prod_id;

输出：

+---------+----------+
| prod_id | quantity |
+---------+----------+
| ANV01   |       10 |
| ANV02   |        3 |
| ANV03   |        1 |
| FU1     |     NULL |
| OL1     |        1 |
| DTNTR   |     NULL |
| FB      |        1 |
| FB      |        1 |
| FC      |       50 |
| SAFE    |     NULL |
| SLING   |        1 |
| TNT1    |     NULL |
| TNT2    |        5 |
| TNT2    |      100 |
| JP1000  |     NULL |
| JP2000  |        1 |
+---------+----------+
16 rows in set (0.00 sec)

计算平均销售规模，包括哪些至今尚未下单的用户

select c.cust_id, avg(quantity*item_price) from customers as c left join orders as o on c.cust_id = o.cust_id left join orderitems as oi on o.order_num = oi.order_num group by c.cust_id;

输出：

+---------+--------------------------+
| cust_id | avg(quantity*item_price) |
+---------+--------------------------+
|   10001 |                23.542500 |
|   10002 |                     NULL |
|   10003 |                55.000000 |
|   10004 |              1000.000000 |
|   10005 |               125.000000 |
+---------+--------------------------+
5 rows in set (0.00 sec)