[学会MySql系列] 第六篇：表联结（JOIN）

接上篇

9.表联结

9.1 什么是联结

在回答什么是联结之前，需要先了解以下什么是关系数据库。
以某个超市的某天进货数据为例:

+---------+-----------+----------+------------+-----------+---------+-------------------+
| prod_id | prod_name | quantity | item_price | supplier  | numbers | address           |
+---------+-----------+----------+------------+-----------+---------+-------------------+
|    1001 | A         |      100 |        100 | A Company | 6214520 | x road,x district |
|    1002 | B         |       50 |        300 | A Company | 6214520 | x road,x district |
|    1003 | C         |       55 |        100 | B Company | 8205520 | y road,y district |
+---------+-----------+----------+------------+-----------+---------+-------------------+

在这张表中商品和供应商信息是在一起的，这样存在很多弊端：
1.存储浪费，因为同一供应商重复录入信息：商品A和B的供应商是一家
2.如果供应商信息更新了需要更新若干个值：如果A公司搬迁了或者号码换了，就要更新一串值
3.容易出现错误，特别是人工录入的话：这种供应商在一张表中，需要录入很多信息，容易出错

建立一张进货单表，一张供应商表，然后建立两者的映射关系，这样的形成的关系表能够很好的解决这些问题。

#products table
+---------+-----------+----------+------------+-------------+
| prod_id | prod_name | quantity | item_price | supplier_id |
+---------+-----------+----------+------------+-------------+
|    1001 |   A       |    100   |    100     |       101   | 
|    1002 |   B       |     50   |    300     |       101   | 
|    1003 |   C       |     55   |    100     |       102   | 
+---------+-----------+----------+------------+-------------+
#suppliers table
+-------------+-----------+----------+-----------------------+
| supplier_id | supplier  | numbers  |       address         |
+-------------+-----------+----------+-----------------------+
|        101  | A Company | 6214520  | x road, x district... |
|        102  | B Company | 8205520  | y road, y district... |
+-------------+-----------+----------+-----------------------+

上面两个表格只要形成关于supplier_id的映射即可。这样的话如果供应商信息更新，我们只需要更新supplier table即可，更新次数更少，而且每天的进货单中不需要将详细的供应商信息填进去，重复录入少。

那么问题就是这种映射是啥？我在查询products表格时候怎么将供应商信息补上呢？这就需要表的联结（JOIN）。

9.2 怎么建立联结

其实建立表的联结并不麻烦，设置有点简单，观察两张表格，不难想其实就是一种条件检索数据，所以用WHERE子句来定义即可，句法如下

SELECT prod_name, quantity, item_price, supplier, numbers, address
FROM products, suppliers
WHERE products.supplier_id=suppliers.supplier_id; 
# WHERE子句就是联结的语句

这段语句的输出为

mysql> SELECT prod_id, prod_name, quantity, item_price, supplier_name, numbers, address
    -> FROM products, suppliers
    -> WHERE products.supplier_id=suppliers.supplier_id;
+---------+-----------+----------+------------+---------------+---------+--------------------+
| prod_id | prod_name | quantity | item_price | supplier_name | numbers | address            |
+---------+-----------+----------+------------+---------------+---------+--------------------+
|    1001 | A         |      100 |        100 | A Company     | 6214520 | x road,x district  |
|    1002 | B         |       50 |        300 | A Company     | 6214520 | x road,x district  |
|    1003 | C         |       55 |        100 | B Company     | 8205520 | y road,y district  |
+---------+-----------+----------+------------+---------------+---------+--------------------+
3 rows in set (0.00 sec)

• 这里值得关注的是WHERE子句中的列一定是带表名的完全限定列名，因为在这里两列的列名都是一样的，都是supplier_id。
• 如果在SELECT子句中存在两个备选的列名一样，就一定要用完全限定列名，也就是加上表名才行。

当然如果表格有三个四个五个六个…，表联结的建立还是用WHERE子句，只不过多几个WHERE而已，到时候用AND连接就可以了。

9.3 联结类型

实际上为了满足不同的检索数据功能，联结又分有三种联结，即自联结、内部联结、外部联结。

9.3.1 自联结

来看一个例子，小王某一天出差去A供应商进货，在出发先准备一下要采购的商品目录，但是每一天公司会指定一张进货总表，于是小王就需要进行以下检索①小王只记得prod_A是A供应商的商品，于是先查找商品prod_A的供应商A在进货总表里面的代号，②查找这个代号下面的所有商品。

完成这些动作，可以用上篇的嵌套查询或者叫子查询来实现

SELECT prod_name
FROM products
WHERE supplier_id=
	(SELECT supplier_id
	FROM products
	WHERE prod_name=prod_A);
#子查询负责先查找供应商代码,外面一层查询则将检索
#该供应商下的所有商品

这样实现貌似有点麻烦，自联结就是为这个准备的。将上述问题用以下语句来实现

SELECT p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.supplier_id=p2.supplier_id
 AND p2.prod_name = prod_A;

这里的AS关键词又出现了，同样这是一种别名。那么为什么要设置别名呢？

我们来分析一下，在这个问题中其实是分两次检索，第一次是对products表检索供应商代码，第二次是对products表检索该供应商下属的商品。这两次由于都是面对同一个表，什么表名和列名都是一样的，那么问题来了如果没有别名，Mysql是不知道你的prod_name到底是用在了第一次还是用在了第二次检索里，这个时候通过别名能够解决这个问题。

通过别名就能够明确p1.prod_name是第二次检索，p2.prod_name用在了第一次检索当作检索条件。

9.3.2 内部联结

像8.2中的联结定义为内部联结。所有两表中存在两列通过相等映射来建立的联结都叫内部联结。

不过内部联结还有一种特殊形式

SELECT prod_name, quantity, item_price, supplier
FROM products INNER JOIN suppliers    #表的顺序是没关系的
ON products.supplier_id=suppliers.supplier_id; 

这个时候ON子句就替代了WHERE子句的功能。

9.3.3 外部联结

在8.2的内部联结的中两张表中的联结的supplier_id列每一行的内容都能够匹配到的，那如果不能匹配到呢？例如下面的表格，prod table中的supplier_id里面的内容和supplier table中的supplier_id并不能完全匹配（比如prodtable中出现的供应商比suppliertable中的少），这种情况就不是内部联结了，而是外部联结。

#prod table
#products table
+---------+-----------+----------+------------+-------------+
| prod_id | prod_name | quantity | item_price | supplier_id |
+---------+-----------+----------+------------+-------------+
|    1001 |   A       |    100   |    100     |       101   | 
|    1002 |   B       |     50   |    300     |       101   | 
|    1003 |   C       |     55   |    100     |       102   | 
|    1004 |   D       |     35   |    100     |       102   | 
+---------+-----------+----------+------------+-------------+
#suppliers table
+-------------+-----------+----------+-----------------------+
| supplier_id | supplier  | numbers  |       address         |
+-------------+-----------+----------+-----------------------+
|        101  | A Company | 6214520  | x road, x district... |
|        102  | B Company | 8205520  | y road, y district... |
|        103  | C Company | 4212138  | z road, z district... |
+-------------+-----------+----------+-----------------------+

下面是外部联结的语句

SELECT prod_id, prod_name, quantity, item_price, supplier_name, numbers, address
FROM product LEFT OUTER JOIN suppliers    #顺序和匹配子句需要对应起来
ON product.supplier_id=suppliers.supplier_id; 

结果如下

mysql> SELECT prod_id, prod_name, quantity, item_price, supplier_name, numbers, address
    -> FROM product LEFT OUTER JOIN suppliers
    -> ON product.supplier_id=suppliers.supplier_id;
+---------+-----------+----------+------------+---------------+---------+--------------------+
| prod_id | prod_name | quantity | item_price | supplier_name | numbers | address            |
+---------+-----------+----------+------------+---------------+---------+--------------------+
|    1001 | A         |      100 |        100 | A Company     | 6214520 | x road,x district  |
|    1002 | B         |       50 |        300 | A Company     | 6214520 | x road,x district  |
|    1003 | C         |       55 |        100 | B Company     | 8205520 | y road, y district |
+---------+-----------+----------+------------+---------------+---------+--------------------+

上述语句中LEFT OUTER JOIN的意思是保留所有product的行，从suppliers表中联结列匹配内容，填充到左侧表相应的行中去。如下所示

如果product和suppliers顺序换一下结果就不一样了，例如

mysql> SELECT prod_id, prod_name, quantity, item_price, supplier_name, numbers, address
    -> FROM suppliers LEFT OUTER JOIN product
    -> ON product.supplier_id=suppliers.supplier_id;
+---------+-----------+----------+------------+---------------+---------+--------------------+
| prod_id | prod_name | quantity | item_price | supplier_name | numbers | address            |
+---------+-----------+----------+------------+---------------+---------+--------------------+
|    1001 | A         |      100 |        100 | A Company     | 6214520 | x road,x district  |
|    1002 | B         |       50 |        300 | A Company     | 6214520 | x road,x district  |
|    1003 | C         |       55 |        100 | B Company     | 8205520 | y road, y district |
|    NULL | NULL      |     NULL |       NULL | C Company     | 4212138 | z road,z district  |
+---------+-----------+----------+------------+---------------+---------+--------------------+

• 注当然不变表的顺序，将外联结的关键词子句改成 RIGHT OUTER JOIN ，也是上述结果。