SQL最强大的功能之一就是能在数据查询的执行中联结(join)表。联结是利用 SQL的 SELECT 能执行的最重要的操作。
1、创建联结表
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
这条语句的 FROM子句列出了两个表: Vendors 和 Products 。它们就是这条 SELECT 语句
联结的两个表的名字。这两个表用 WHERE 子句正确地联结, WHERE 子句指示 DBMS将 Vendors 表中的 vend_id 与 Products 表中的 vend_id 匹配起来。
要保证所有联结都有where子句,如果没有联结条件的表关系则返回的结果为笛卡儿积,也成为叉联结(检索出的行的数目将是第一个表中的行数乘以第二个表中的行数)。
2、内联结
目前为止使用的联结称为等值联结( equi join),它基于两个表之间的相等测试。这种联结也称为内联结(inner join)。
其实,可以对这种联结使用稍微不同的语法,明确指定联结的类型。下面的 SELECT 语句返回与前面例子完全相同的数据:
两个表之间的关系是以 INNER JOIN 指定,联结条件用特定的 ON 子句代替where子句。
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
以下是联结多个表的示例:
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
这个例子显示订单 20007 中的物品。订单物品存储在 OrderItems 表中。每个产品按其产品 ID存储,它引用 Products 表中的产品。这些产品通过供应商 ID联结到 Vendors 表中相应的供应商,供应商 ID存储在每个产品的记录中。这里的 FROM 子句列出三个表, WHERE 子句定义这两个联结条件,而第三个联结条件用来过滤出订单 20007 中的物品。
3、高级联结
① 使用表别名
SQL除了可以对列名和计算字段使用别名,还允许给表名起别名。
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';
表别名,不仅能用于 WHERE 子句,还可以用于 SELECT 的列表、 ORDERBY 子句以及其他语句部分。
② 自联结(self-join)
除了内联结inner join(也叫等值联结)外,还有自联结(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');
以下是自联结方法:
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子句中出现了两次。
自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的,但许多 DBMS处理联结远比处理子查询快得多。
③ 自然联结(natural join)
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';
这个例子中,通配符只对第一个表使用,所有其他列明确列出,所以没有重复的列被检索出来。
事实上,我们迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
④ 外联结(outer join)
联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
注意看以下两种查询结果的区别:
与内联结关联两个表中的行不同的是,外联结还包括没有关联行的行。在使用 OUTER JOIN 语法时,必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表。
LEFT OUTER JOIN 左联结,从 FROM 子句左边的表中选择所有行
RIGHT OUTER JOIN 右联结,从 FROM 子句右边的表中选择所有行
FULL OUTER JOIN 全联结,从 FROM 子句两边的表中选择所有行