SQL联结表及高级联结

本文介绍: sql 联结

关系表

理解关系表的最好方法是来看一个现实世界中的例子。假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。
假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信息分开存储的理由如下:

因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。
如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（ relational））互相关联。
在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。 vendors 表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（ primarykey）（在第 1 章中首次提到），可以是供应商 ID 或任何其他唯一值。
products 表只存储产品信息，它除了存储供应商 ID（ vendors 表的主键）外不存储其他供应商信息。 vendors 表的主键又叫作 products 的外键，它将 vendors 表与 products 表关联，利用供应商 ID 能从 vendors 表中找出相应供应商的详细信息。
外键（ foreignkey）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
这样做的好处如下:
4. 供应商信息不重复，从而不浪费时间和空间;
5. 如果供应商信息变动，可以只更新 vendors 表中的单个记录，相关表中的数据不用改动；
6. 由于数据无重复，显然数据是一致的，这使得处理数据更简单;

联结

SQL 最强大的功能之一就是能在数据检索查询的执行中联结（ join）表。联结是利用 SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习 SQL 的一个极为重要的组成部分。
如果数据存储在多个表中，怎样用单条 SELECT 语句检索出数据？
答案是使用联结。简单地说，联结是一种机制，用来在一条 SELECT 语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性

重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由 MySQL 根据需要建立，它存在于查询的执行当中。在使用关系表时，仅在关系列中插入合法的数据非常重要。回到这里的例子，如果在 products 表中插入拥有非法供应商 ID（即没有在 vendors 表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。为防止这种情况发生，可指示 MySQL 只允许在 products 表的供应商 ID 列中出现合法值（即出现在 vendors 表中的供应商）。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的.

外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：

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

联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。
下面的 SELECT 语句给出一个简单的内部联结。它检索所有客户及其订单：

select customers.cust_id, orders.order_num from customers inner join orders;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10005 |     20005 |
|   10004 |     20005 |
|   10003 |     20005 |
|   10002 |     20005 |
|   10001 |     20005 |
|   10005 |     20009 |
|   10004 |     20009 |
|   10003 |     20009 |
|   10002 |     20009 |
|   10001 |     20009 |
|   10005 |     20006 |
|   10004 |     20006 |
|   10003 |     20006 |
|   10002 |     20006 |
|   10001 |     20006 |
|   10005 |     20007 |
|   10004 |     20007 |
|   10003 |     20007 |
|   10002 |     20007 |
|   10001 |     20007 |
|   10005 |     20008 |
|   10004 |     20008 |
|   10003 |     20008 |
|   10002 |     20008 |
|   10001 |     20008 |
+---------+-----------+
25 rows in set (0.00 sec)

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行：

select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id=orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

这条 SELECT 语句使用了关键字 OUTER JOIN 来指定联结的类型类型（而不是在 WHERE 子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句的左边表（ customers 表）中选择所有行。为了从右边的表中选择所有行，应该使用 RIGHT OUTER JOIN，如下例所示：

select customers.cust_id, orders.order_num from customers right outer join orders on customers.cust_id=orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)