12.创建高级联结---SQL

一、使用表别名

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';

分析▼

可以看到，FROM子句中的三个表全都有别名。Customers AS C使用C作为Customers的别名，如此等等。

这样，就可以使用省略的C而不用全名Customers。在这个例子中，表别名只用于WHERE子句。

其实它不仅能用于WHERE子句，还可以用于SELECT的列表、ORDER BY子句以及其他语句部分。

警告：

Oracle中没有AS Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS，简单地指定列名即可（因此，应该是Customers C，而不是Customers AS C）。

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

二、自联结

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';

提示：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 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';

分析▼

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

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

四、外联结

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

分析▼

这条SELECT语句使用了关键字OUTER JOIN来指定联结类型（而不是在WHERE子句中指定）。

但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

上面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN，

警告：

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 Orders FULL OUTER JOIN Customers ON Orders.cust_id = Customers.cust_id;

警告：

FULL OUTER JOIN的支持 Access、MariaDB、MySQL、OpenOffice Base或SQLite不支持FULL OUTER JOIN语法。

五、使用带聚集函数的联结

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 um_ord 
---------- --------
1000000001 2
1000000003 1
1000000004 1
1000000005 1

分析▼

这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。

GROUP BY子句按顾客分组数据，因此，函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回。

六、使用联结和联结条件

注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
关于确切的联结语法，应该查看具体的文档，看相应的DBMS支持何种语法（大多数DBMS使用这两课中描述的某种语法）。
保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。