创建高级联结

1.使用别名

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

• 缩短SQL语句；
• 允许在单条SELECT语句中多次使用相同的表。

例如；

SELECT cust_name, cust_contact 
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id 
 AND OI.order_num = O.order_num 
 AND prod_id = 'RGAN01';

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+

分析：FROM子句中3个表全都具有别名。customers AS c建立c作为customers的别名，等等。别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。

表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

2.使用不同类型的联结

2.1自联结

问题：假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物
品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

使用自联结解决

输入

SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
FROM Customers AS c1, Customers AS c2 
WHERE c1.cust_name = c2.cust_name 
 AND c2.cust_contact = 'Jim Jones'; 

输出

+------------+-----------+--------------------+
| cust_id    | cust_name | cust_contact       |
+------------+-----------+--------------------+
| 1000000003 | Fun4All   | Jim Jones          |
| 1000000004 | Fun4All   | Denise L. Stephens |
+------------+-----------+--------------------+

分析：此查询中需要的两个表实际上是相同的表，为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。

用自联结而不用子查询：
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然结果是一样的，有时候处理联结远比处理子查询快得多。

2.2自然联结

标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

输出

SELECT C.*, O.order_num, O.order_date, 
       OI.prod_id, OI.quantity, OI.item_price 
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id 
 AND OI.order_num = O.order_num 
 AND prod_id = 'RGAN01'; 

+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+
| cust_id    | cust_name     | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            | order_num | order_date          | prod_id | quantity | item_price |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+
| 1000000004 | Fun4All       | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |     20007 | 2005-09-01 00:00:00 | RGAN01  |       50 |       4.49 |
| 1000000005 | The Toy Store | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |     20008 | 2005-09-15 00:00:00 | RGAN01  |        5 |       4.99 |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+

分析：在这个例子中，通配符只对第一个表使用，所有其他列明确给出，所以没有重复的列被检出。

事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

2.3外部联结

下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单：

输入

SELECT customers.cust_id,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+

为了检索所有客户，包括那些没有订单的客户，可如下进行：

输入

SELECT customers.cust_id,orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

输出

+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+

分析：
这条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 JOIN orders
ON customers.cust_id = orders.cust_id;

+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+

3.使用带聚集函数的联结

聚集函数用来汇总数据。

聚集函数也可以方便地与其他联结一起使用。请看下面的例子：

SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

+---------------+------------+---------+
| cust_name     | cust_id    | num_ord |
+---------------+------------+---------+
| Village Toys  | 1000000001 |       2 |
| Kids Place    | 1000000002 |       0 |
| Fun4All       | 1000000003 |       1 |
| Fun4All       | 1000000004 |       1 |
| The Toy Store | 1000000005 |       1 |
+---------------+------------+---------+

分析：这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Kids Place，它有0个订单。

4.使用联结和联结条件

关于联结及其使用的某些要点：

• 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。