10.3.2　从多个表中获取数据

通常，要通过数据库回答一个问题，必须使用多个表中的数据。例如，如果要知道哪些顾客在本月中有订单，就需要查阅customers表和orders表。特别地，如果还希望知道他们订购了什么，还需要查阅order_items表。

这些数据分布在不同的的表中，因为它们与现实世界的对象相关。这是设计优秀数据库的原则之一，我们在第8章中已经介绍了。

要在SQL中将这些信息放到一起，必须执行一个名为关联的操作。简单地说，这意味着需要根据数据间的关系将两个或更多的表关联到一起。例如，如果要查看顾客Julie Smith的订单，我们就要查阅customers表中值为Julie的customerid，然后从orders表查找该customerid所对应的订单。

尽管关联的概念非常简单，但是它是SQL中比较微妙和复杂的一部分。MySQL中实现了许多不同种类的连接，而每一个连接都具有不同的用途。

1.简单的双表关联

现在，让我们以刚刚讨论的一些对Julie Smith的SQL查询为开始：

select orders.orderid,orders.amount,orders.date

from customers,orders

where customers.name='Julie Smith'

and customers.customerid=orders.customerid;

该查询的输出结果如下所示：

+————-+————+——————+

|orderid|amount|date|

+————-+————+——————+

|1|69.98|2007-04-02|

|4|24.99|2007-05-01|

+————-+————+——————+

这里有几点需要注意。首先，因为是通过来自两个表的信息来完成这个查询，因此我们必须将两个表都列在这里。

通过列出两个表，也指定了关联的类型，尽管可能还不知道它。表名称之间的逗号等价于输入INNER JOIN或CROSS JOIN。这是一种类型的关联，有时也称为完全关联（full join）或表的笛卡儿乘积（Cartesian product）。其意思是，“将多个表列出来，形成一个大表。该表应该有一行来自所有表的每一行的所有可能组合，无论它是否有意义”。换句话说，我们得到了一个表，customers表的每一行都在该表中，并且这些行都与orders表中每一行相匹配，而不管顾客是否下了一个特定的订单。

在大多情况下，这样做并没有很大的意义。通常，我们要做的是查看真正匹配的行，即匹配特定顾客的该顾客所订的订单。

我们通过在WHERE子句中使用关联条件（join condition）来完成。这是一类条件语句，它解释了哪些属性显示两个表之间的关系。在这个例子中，关联条件是：

customers.customerid=orders.customerid

以上代码将告诉MySQL，如果customers表中的customerid与orders表中的customerid相匹配，那么就将行显示在结果表中。

通过在查询中添加此关联条件，我们实际上已经将关联转变成另一种类型，可以称之为等价关联（equi-join）。

注意我们使用了点号以使得来自某个表的某列这种关系看起来更清晰。也就是，customers.customerid表示来自customers表的customerid，而orders.customerid则表示orders表中的customerid。

如果一列的名称不具有唯一性，也就是，如果某列出现在多于一个表中的时候，我们需要使用点号。作为其扩充，也可以用它表示来自不同数据库的非模糊列。在这个例子中，使用了table.column表示方法。可以用database.table.column来指定数据库，例如，要测试如下所示的条件：

books.orders.customerid=other_db.orders.customerid

然而，在查询中，可以使用点号表示方法来表示所有被引用的列。这也是一个好主意，特别是在查询开始变得复杂之后。MySQL不要求它，但是它的确可以使查询变得更易读和易于维护。注意我们在前面介绍的查询其余部分采用了这一惯例，例如，使用条件：

customers.name='Julie Smith'

name列只出现在表customers中，我们并不需要指定它。MySQL将不会产生混淆。然而对编程人员来说，name本身就很模糊，因此当指定customer.name时，这使得代码更清晰。

2.关联多个表

关联多于两个表的情况并不比两个表的关联更复杂。按照通常的规则，必须利用关联条件成对地关联表。可以把它想像为从一个表到一个表再到另一个表地跟踪数据间的关系。例如，如果我们要知道哪些顾客已经订购了关于Java的图书（可能我们要向他们发送一本关于java的新书信息），需要在几个表中搜索这些关系。

我们需要找到在关于Java的order_item中至少订了一个订单的顾客。从customers表到orders表，可以如前所述使用customerid。从orders表到order_items表，可以使用orderid。要从order_items表到books表中特定的书。可以使用ISBN。完成所有这些连接后，就可以测试书刊标题中是否包含"Java"一词，并返回订购了其中任何一本书的顾客名称。

看看我们如何完成所有这些查询：

select customers.name

from customers,orders,order_items,books

where customers.customerid=orders.customerid

and orders.orderid=order_items.orderid

and order_items.isbn=books.isbn

and books.title like'%Java%';

该查询将返回如下输出：

+————————-+

|name|

+————————-+

|Julie Smith|

+————————-+

请注意，我们在4个不同表之间跟踪数据，并利用等价关联来获得这些数据，而在这个过程中，需要使用3个不同的关联条件。要为每一对即将关联的表设置一个条件，因此关联条件的总数量应该比将要关联的表数少一个，这通常是正确的。这条重要的规则在测试查询效率不高时非常实用。然后核对关联条件并确认已经完成了从已知到需要知道的自始至终的跟踪过程。

3.查找不匹配行

在MySQL中，我们还经常使用的另一个重要关联类型是左关联。

在前面的例子中可以注意到，只有那些在表之间有匹配的行才能被包含进来。有时，我们特别需要没有匹配的行。例如，从来没有订单的顾客，或从没被订购过一次的图书。

在MySQL中，实现此类查询最简单的方法就是使用左关联。左关联是在两个表之间指定的关联条件下匹配的数据行。如果右边的表中没有匹配行，结果中就会增加一行，该行右边的列为NULL。

我们看下面的例子：

select customers.customerid,customers.name,orders.orderid

from customers left join orders

on customers.customerid=orders.customerid;

该SQL查询使用一个左关联将customers表和orders表关联起来。注意左关联的连接条件所使用的语法有些不同；在这个例子中，关联条件出现在SQL语句的特殊子句ON子句中。

该查询的输出结果如下所示：

+——————+————————-+————-+

|customerid|name|orderid|

+——————+————————-+————-+

|3|Julie Smith|1|

|3|Julie Smith|4|

|4|Alan Wong|NULL|

|5|Michelle Arthur|NULL|

+——————+————————-+————-+

该输出只显示了orderid为非空的客户。

如果我们只需查看没有订购任何商品的顾客，可以检查右边表的主键字段是否为NULL（在这个例子中，orderid），因为在任何真正的数据行中这个不应该为NULL：

select customers.customerid,customers.name

from customers left join orders

using(customerid)

where orders.orderid is null;

其结果为：

+——————+————————-+

|customerid|name|

+——————+————————-+

|4|Alan Wong|

|5|Michelle Arthur|

+——————+————————-+

注意在这个例子中，对关联条件也使用了不同的语法。左关联支持第一个例子中用到的ON语法，也支持第二个例子中用过的USING语法。请注意，USING语法并不需要指定连接属性所来自的表；正是由于这个原因，如果希望使用USING子句，两个表中的列必须有同样的名称。

可以通过使用子查询来满足这种查询。在本章的稍后内容，我们将了解子查询。

4.使用表的别名：Aliases

通常，用表的别名来指定表是很方便的，偶尔也是很必要的。表的其他名称就是表的别名（aliase）。可以在一个查询的开始创建它们，然后在整个查询过程中使用。因为它们便于记忆，因此使用起来非常方便。考虑前面讨论过的庞大查询，我们可以使用别名对其重新编写：

select c.name

from customers as c,orders as o,order_items as oi,books as b

where c.customerid=o.customerid

and o.orderid=oi.orderid

and oi.isbn=b.isbn

and b.title like'%Java%';

因为可以声明所需要使用的表，因此我们增加了AS子句以声明该表的别名。我们也可以给列起别名，在接下来介绍集合函数的时候我们将详细介绍它。

当要关联一个表到表本身的时候就必须使用表别名。这听起来很深奥而难于理解。例如，如果要查找同一个表中值相同的行，它就很有意义。如果要查找住在同一城市的顾客（可能要建立一个阅读小组），可以给同一个表（customers）起两个不同的别名：

select c1.name,c2.name,c1.city

from customers as c1,customers as c2

where c1.city=c2.city

and c1.name!=c2.name;

基本上，我们要做的是将表customers看作两个不同的表，c1和c2，并在city列执行关联。注意这也需要另外一个条件：c1.name!=c2.name;这可以避免顾客作为其自身的匹配而出现。

5.关联的总结

表10-2总结了我们已经介绍的不同类型关联。还有一些这里没有介绍的其他类型，但是本表给出的是可能遇到的主要类型。