【SQL 必知必会】- 第十三课 创建高级联结

目录

使用表别名

        Oracle 中没有AS

使用不同类型的联结

        自联结

        用自联结而不用子查询

        自然联结

        外联结

        全外联结

使用带聚集函数的联结

使用联结和联结条件

---

使用表别名

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

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

-- 使用别名，更加简洁
select a.name, a.agesfrom students a, employees bwhere a.std_id > 10and a.std_id = b.emp_id;-- 不使用别名，更加冗长
select students.name, students.agesfrom students, employeeswhere students.std_id > 10and students.std_id = employees.emp_id;

---

        Oracle 中没有AS

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

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

---

使用不同类型的联结

        迄今为止，我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

---

        自联结

        如前所述，使用表别名的一个主要原因是能在一条SELECT 语句中不止一次引用相同的表。下面举一个例子。

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_nameFROM CustomersWHERE cust_contact = 'Jim Jones');

        现在来看使用联结的相同查询：

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

        此查询中需要的两个表实际上是相同的表，因此Customers 表在FROM子句中出现了两次。虽然这是完全合法的，但对Customers 的引用具有歧义性，因为DBMS 不知道你引用的是哪个Customers 表。

        解决此问题，需要使用表别名。

---

        用自联结而不用子查询

        自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多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';

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

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

---

        外联结

        许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。

        联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

        下面的SELECT 语句给出了一个简单的内联结。

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

        外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：

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，如下例所示：

        left  ：显示左边表的所有行
        right：显示右边表的所有行
        若两个表有不匹配的地方会用 null / 0 填充，依据不匹配的栏位属性填充。

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

---

        全外联结

        总是有两种基本的外联结形式：左外联结和右外联结。

        它们之间的唯一差别是所关联的表的顺序。换句话说，调整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;

---

使用带聚集函数的联结

        如第9 课所述，聚集函数用来汇总数据。虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据，但这些函数也可以与联结一起使用。

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;

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

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;

---

使用联结和联结条件

        在总结讨论联结的这两课前，有必要汇总一下联结及其使用的要点。

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