day13-创建高级联结
使用表列名
如何使用别名引用被检索的表列。给列起别名的语法如下：
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
输出：
mysql> SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
-> AS vend_title
-> FROM Vendors
-> ORDER BY vend_name;
+------------+
| vend_title |
+------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------+
6 rows in set, 24 warnings (0.00 sec)
mysql>
SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：
缩短 SQL 语句；
允许在一条 SELECT 语句中多次使用相同的表。
请看下面的 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';
输出：
mysql> 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 |
+---------------+--------------------+
2 rows in set (0.00 sec)
mysql>
可以看到，FROM 子句中的三个表全都有别名。Customers AS C 使用 C作为 Customers 的别名，如此等等。这样，就可以使用省略的 C 而不用全名 Customers。在这个例子中，表别名只用于 WHERE 子句。其实它不仅能用于 WHERE 子句，还可以用于 SELECT 的列表、ORDER BY 子句以及其他语句部分。
注意：Oracle 中没有 AS
Oracle 不支持 AS 关键字。要在 Oracle 中使用别名，可以不用 AS，简单地指定列名即可（因此，应该是 Customers C，而不是 Customers AS C）。需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。
使用不同类型的联结
迄今为止，我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结：
自联结（self-join）、自然联结（natural join）和外联结（outer join）。
自联结
如前所述，使用表别名的一个主要原因是能在一条 SELECT 语句中不止一次引用相同的表。下面举一个例子。
假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客。下面是解决此问题的一种方法：
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
输出：
mysql> SELECT cust_id, cust_name, cust_contact
-> FROM Customers
-> WHERE cust_name = (SELECT cust_name
-> FROM Customers
-> WHERE cust_contact = 'Jim Jones');
+------------+-----------+--------------------+
| cust_id | cust_name | cust_contact |
+------------+-----------+--------------------+
| 1000000003 | Fun4All | Jim Jones |
| 1000000004 | Fun4All | Denise L. Stephens |
+------------+-----------+--------------------+
2 rows in set (0.00 sec)
mysql>
这是第一种解决方案，使用了子查询。内部的 SELECT 语句做了一个简单检索，返回 Jim Jones 工作公司的 cust_name。该名字用于外部查询的WHERE 子句中，以检索出为该公司工作的所有雇员（第 11 课中讲授了子查询，更多信息请参阅该课）。现在来看使用联结的相同查询：
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';
输出：
mysql> 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 |
+------------+-----------+--------------------+
2 rows in set (0.00 sec)
mysql>
提示：Oracle 中没有 AS
Oracle 用户应该记住去掉 AS。
此查询中需要的两个表实际上是相同的表，因此 Customers 表在 FROM子句中出现了两次。虽然这是完全合法的，但对 Customers 的引用具有歧义性，因为 DBMS 不知道你引用的是哪个 Customers 表。解决此问题，需要使用表别名。Customers 第一次出现用了别名 c1，第二次出现用了别名 c2。现在可以将这些别名用作表名。例如，SELECT 语句使用 c1 前缀明确给出所需列的全名。如果不这样，DBMS 将返回错误，因为名为 cust_id、cust_name，cust_contact 的列各有两个。DBMS不知道想要的是哪一列（即使它们其实是同一列）。WHERE 首先联结两个表，然后按第二个表中的 cust_contact 过滤数据，返回所需的数据。
提示：用自联结而不用子查询
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。
自然联结
无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。
怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。
自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符
（SELECT *），而对其他表的列使用明确的子集来完成。下面举一个例子
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';
输出：
mysql> 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 | 2020-01-30 00:00:00 | RGAN01 | 50 | 4.49 |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL | 20008 | 2020-02-03 00:00:00 | RGAN01 | 5 | 4.99 |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.00 sec)
mysql>
提示：Oracle 中没有 AS
Oracle 用户应该记住去掉 AS。
分析▼
在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。
事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。
外联结
许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：
对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的顾客。
在上述例子中，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
注意：语法差别
需要注意，用来创建外联结的语法在不同的 SQL 实现中可能稍有不同。下面段落中描述的各种语法形式覆盖了大多数实现，在继续学习之前请参阅你使用的 DBMS 文档，以确定其语法。
下面的 SELECT 语句给出了一个简单的内联结。它检索所有顾客及其订单：
SELECT Customers.cust_id, Orders.order_num
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;
输出：
mysql> 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 |
+------------+-----------+
5 rows in set (0.00 sec)
mysql>
分析▼
类似上一课提到的内联结，这条 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;
输出：
mysql> SELECT Customers.cust_id, Orders.order_num
-> FROM Customers
-> RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
+------------+-----------+
| cust_id | order_num |
+------------+-----------+
| 1000000001 | 20005 |
| 1000000001 | 20009 |
| 1000000003 | 20006 |
| 1000000004 | 20007 |
| 1000000005 | 20008 |
+------------+-----------+
5 rows in set (0.00 sec)
mysql>
注意：SQLite 外联结
SQLite 支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。幸好，如果你确实需要在 SQLite 中使用 RIGHT OUTER JOIN，有一种更简单的办法，这将在下面的提示中介绍。
提示：外联结的类型
要记住，总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整 FROM 或 WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。
还存在另一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语法如下：
SELECT Customers.cust_id, Orders.order_num
FROM Customers
FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
输出：
注意：FULL OUTER JOIN 的支持
MariaDB、MySQL 和 SQLite 不支持 FULL OUTER JOIN 语法。
补充：
8026此版本不支持全外联结了
使用带聚集函数的联结
如第 9 课所述，聚集函数用来汇总数据。虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据，但这些函数也可以与联结一起使用。
我们来看个例子，要检索所有顾客及每个顾客所下的订单数，下面的代码使用 COUNT()函数完成此工作：
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
输出：
mysql> SELECT Customers.cust_id,
-> COUNT(Orders.order_num) AS num_ord
-> FROM Customers
-> INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
-> GROUP BY Customers.cust_id;
+------------+---------+
| cust_id | num_ord |
+------------+---------+
| 1000000001 | 2 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+---------+
4 rows in set (0.00 sec)
mysql>
这条 SELECT 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联。GROUP BY 子句按顾客分组数据，因此，函数调用 COUNT(Orders.order_num)对每个顾客的订单计数，将它作为 num_ord 返回。聚集函数也可以方便地与其他联结一起使用。请看下面的例子
SELECT 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;
输出：
mysql> SELECT 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_id | num_ord |
+------------+---------+
| 1000000001 | 2 |
| 1000000002 | 0 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+---------+
5 rows in set (0.00 sec)
mysql>
这个例子使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客 1000000002，他有 0 个订单，这和使用INNER JOIN 时不同。
使用联结和联结条件
在总结讨论联结的这两课前，有必要汇总一下联结及其使用的要点。
注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
关于确切的联结语法，应该查看具体的文档，看相应的 DBMS 支持何种语法（大多数 DBMS 使用这两课中描述的某种语法）。
保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。
小结
本课是上一课的延续，首先讲授了如何以及为什么使用别名，然后讨论不同的联结类型以及每类联结所使用的语法。我们还介绍了如何与联结一起使用聚集函数，以及在使用联结时应该注意的问题。
小练习
1.使用 INNER JOIN 编写 SQL语句，以检索每个顾客的名称（Customers表中的 cust_name）和所有的订单号（Orders 表中的 order_num）。
SELECT cust_name, order_num
FROM Customers
JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;
输出：
mysql> SELECT cust_name, order_num
-> FROM Customers
-> JOIN Orders ON Customers.cust_id = Orders.cust_id
-> ORDER BY cust_name;
+---------------+-----------+
| cust_name | order_num |
+---------------+-----------+
| Fun4All | 20006 |
| Fun4All | 20007 |
| The Toy Store | 20008 |
| Village Toys | 20005 |
| Village Toys | 20009 |
+---------------+-----------+
5 rows in set (0.00 sec)
2.修改刚刚创建的 SQL 语句，仅列出所有顾客，即使他们没有下过订单。
SELECT cust_name, order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;
输出：
mysql> SELECT cust_name, order_num
-> FROM Customers
-> LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
-> ORDER BY cust_name;
+---------------+-----------+
| cust_name | order_num |
+---------------+-----------+
| Ben's Toys | NULL |
| Fun4All | 20006 |
| Fun4All | 20007 |
| Kids Place | NULL |
| The Toy Store | 20008 |
| Village Toys | 20005 |
| Village Toys | 20009 |
+---------------+-----------+
7 rows in set (0.00 sec)
mysql>
3.使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名 称（prod_name）和与之相关的订单号（order_num）的列表，并按 商品名称排序。
SELECT prod_name, order_num
FROM Products LEFT OUTER JOIN OrderItems
ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name;
输出：
mysql> SELECT prod_name, order_num
-> FROM Products LEFT OUTER JOIN OrderItems
-> ON Products.prod_id = OrderItems.prod_id
-> ORDER BY prod_name;
+---------------------+-----------+
| prod_name | order_num |
+---------------------+-----------+
| 12 inch teddy bear | 20006 |
| 18 inch teddy bear | 20005 |
| 18 inch teddy bear | 20006 |
| 18 inch teddy bear | 20007 |
| 18 inch teddy bear | 20008 |
| 8 inch teddy bear | 20005 |
| 8 inch teddy bear | 20006 |
| Bird bean bag toy | 20007 |
| Bird bean bag toy | 20008 |
| Bird bean bag toy | 20009 |
| Fish bean bag toy | 20007 |
| Fish bean bag toy | 20008 |
| Fish bean bag toy | 20009 |
| King doll | NULL |
| Queen doll | NULL |
| Rabbit bean bag toy | 20007 |
| Rabbit bean bag toy | 20008 |
| Rabbit bean bag toy | 20009 |
| Raggedy Ann | 20007 |
| Raggedy Ann | 20008 |
+---------------------+-----------+
20 rows in set (0.00 sec)
mysql>
4.修改上一题中创建的 SQL 语句，使其返回每一项产品的总订单数 （不是订单号）。
SELECT prod_name, COUNT(order_num) AS orders
FROM Products LEFT OUTER JOIN OrderItems
ON Products.prod_id = OrderItems.prod_id
GROUP BY prod_name
ORDER BY prod_name;
输出：
mysql> SELECT prod_name, COUNT(order_num) AS orders
-> FROM Products LEFT OUTER JOIN OrderItems
-> ON Products.prod_id = OrderItems.prod_id
-> GROUP BY prod_name
-> ORDER BY prod_name;
+---------------------+--------+
| prod_name | orders |
+---------------------+--------+
| 12 inch teddy bear | 1 |
| 18 inch teddy bear | 4 |
| 8 inch teddy bear | 2 |
| Bird bean bag toy | 3 |
| Fish bean bag toy | 3 |
| King doll | 0 |
| Queen doll | 0 |
| Rabbit bean bag toy | 3 |
| Raggedy Ann | 2 |
+---------------------+--------+
9 rows in set (0.00 sec)
mysql>
5.编写 SQL语句，列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT() 聚合函数来计算 Products 表中每种产品的数量。注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。
SELECT Vendors.vend_id, COUNT(prod_id)
FROM Vendors
LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id
GROUP BY Vendors.vend_id;
输出：
mysql> SELECT Vendors.vend_id, COUNT(prod_id)
-> FROM Vendors
-> LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id
-> GROUP BY Vendors.vend_id;
+---------+----------------+
| vend_id | COUNT(prod_id) |
+---------+----------------+
| BRE02 | 0 |
| BRS01 | 3 |
| DLL01 | 4 |
| FNG01 | 2 |
| FRB01 | 0 |
| JTS01 | 0 |
+---------+----------------+
6 rows in set (0.01 sec)
mysql>
浙公网安备 33010602011771号