18.MySQL之联结表（left、right、inner）

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录

本节讲述联结的三种方式，外部联结（左外部联结与右外部联结）和内部联结

1. 联结

1. 关系表
   在日常生活中，我们要设计一张表要将所有的信息包含在表中是可以的，但是这是很复杂的。这时候就出现了关系表。
   关系表就是保证将信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系）互相关联。关系表具有更好的可伸缩性。

2. 联结
   为什么要使用联结？
   正如上述所述，分解数据为多个表能更有效地存储，更方便地处理，并具有更大的可伸缩性。但这些好处是有代价的。当我们的要想从多个表中检索出数据，我们如何做？
   这时候就得使用联结。简单的说联结就是一种机制，用来在一条select语句中关联表，因此称之为联结。

   在讲述联结的三种方式之前，我想问问笛卡儿积是什么？
   笛卡儿乘积是指在数学中，两个集合X和Y的笛卡尓积。假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
   在数据库中，笛卡儿积指的是没有联结条件的表关系返回的结果为笛卡尔积。表示为：table1,table2
   举个栗子

mysql> select * from promary;
+-------+-----------+
| proID | proName   |
+-------+-----------+
|     5 | 河北省    |
|     6 | 山西省    |
|    16 | 山东省    |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> select * from city;
+--------------+-------+
| cityName     | proID |
+--------------+-------+
| 太原市       |     6 |
| 石家庄市     |     5 |
| 长春市       |     9 |
+--------------+-------+
3 rows in set (0.00 sec)

mysql> # 我们将promary表和city表做笛卡儿积
mysql> select * from promary,city;
+-------+-----------+--------+--------------+-------+
| proID | proName   | cityID | cityName     | proID |
+-------+-----------+--------+--------------+-------+
|     5 | 河北省    |      1 | 太原市       |     6 |
|     6 | 山西省    |      1 | 太原市       |     6 |
|    16 | 山东省    |      1 | 太原市       |     6 |
|     5 | 河北省    |      1 | 石家庄市     |     5 |
|     6 | 山西省    |      1 | 石家庄市     |     5 |
|    16 | 山东省    |      1 | 石家庄市     |     5 |
|     5 | 河北省    |      1 | 长春市       |     9 |
|     6 | 山西省    |      1 | 长春市       |     9 |
|    16 | 山东省    |      1 | 长春市       |     9 |
+-------+-----------+--------+--------------+-------+
9 rows in set (0.10 sec)

从上面可以看出这是将promary中的每一行去和city的每一行联结起来（一共有3*3行），形成了笛卡尔积。

2. 外部联结之左联结

我们可以看出两个表做笛卡尔积并不能得到我们想要的数据，如何筛选出我们想要的数据？
这时候我们可以用左联接来做。
格式：
select 列1,列2,列n from
tableA left outer join tableB
on tableA.列 = table.列(条件)(这里outer可省)
左联结就是以左表(tableA)为准，去右表(tableB)匹配数据，找不到匹配，用null补齐。
举个栗子

mysql> # 左连接
mysql> # 查询每个城市对应的省份
mysql> select promary.*,city.* from
    -> promary left join city
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     6 | 山西省    | 太原市       |     6 |
|     5 | 河北省    | 石家庄市     |     5 |
|    16 | 山东省    | NULL         |  NULL |
+-------+-----------+--------------+-------+
3 rows in set (0.00 sec)

mysql> # 左连接
mysql> # 查询每个城市对应的省份
mysql> select promary.*,city.* from
    -> city left join promary
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     5 | 河北省    | 石家庄市     |     5 |
|     6 | 山西省    | 太原市       |     6 |
|  NULL | NULL      | 长春市       |     9 |
+-------+-----------+--------------+-------+
3 rows in set (0.40 sec)

3.外部联结之右联结

格式：
select 列1,列2,列n from
tableA inner join tableB
on tableA.列 = table.列(条件)
右联结就是以右表(tableB)为准，去左表(tableA)匹配数据，找不到匹配，用null补齐。
左联接是可以与右联结互换的。 tableA right outer join tableB = tableB left outer join tableA
举个栗子

mysql> # 右连接
mysql> # 查询每个省对应下的城市
mysql> select promary.*,city.* from
    -> city right join promary
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     6 | 山西省    | 太原市       |     6 |
|     5 | 河北省    | 石家庄市     |     5 |
|    16 | 山东省    | NULL         |  NULL |
+-------+-----------+--------------+-------+
3 rows in set (0.00 sec)

说到底左联接和右联结是同一回事，但是一般我们使用左联结多些。

4. 内部联结之inner

内部联结又称为等值联结，将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。
说简单点就是查询左右表都有的数据。即不要左右连接中有null的一部分。
内连接是 左右连接的交集
格式：
select 列1,列2,列n from
tableA right outer join tableB
on tableA.列 = table.列(条件)
举个栗子

 mysql> # 内连接（inner）
mysql> # 查询每个省份对应下的城市，且必须有不为null的城市
mysql> select promary.*,city.* from
    -> promary inner join city
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     5 | 河北省    | 石家庄市     |     5 |
|     6 | 山西省    | 太原市       |     6 |
+-------+-----------+--------------+-------+
2 rows in set (0.00 sec)

#### 5. 联结多个表
不必多说，举个栗子大家就理解了。

 mysql> # 在products、productnotes、venders三个表中查出价格少于5的商品的prod_id、vend_id、prod_name、vend_name、note_id。
 mysql> select products.prod_id,products.vend_id,prod_name,productnotes.note_id,vendors.vend_name from
    -> products  left join productnotes
    -> on products.prod_id = productnotes.prod_id
    -> left join vendors
    -> on products.vend_id = vendors.vend_id
    -> where prod_price < 5;
+---------+---------+---------------+---------+-------------+
| prod_id | vend_id | prod_name     | note_id | vend_name   |
+---------+---------+---------------+---------+-------------+
| FC      |    1003 | Carrots       |     104 | ACME        |
| FC      |    1003 | Carrots       |     110 | ACME        |
| SLING   |    1003 | Sling         |     111 | ACME        |
| FU1     |    1002 | Fuses         |    NULL | LT Supplies |
| TNT1    |    1003 | TNT (1 stick) |    NULL | ACME        |
+---------+---------+---------------+---------+-------------+
5 rows in set (0.00 sec)

通过上述栗子可以看出，若要进行多个表之间的联合，只需在后面多加一个联结就行。除此之外，联结后的表可以当成是一张表，可以在后面对其进行where、group by等子句的操作。

tips

• 在union中只用order by不会起作用，会被优化掉
• 子句使用order by等操作时要括起来，也可以在最后使用这些操作。