SQL学习之--联结表

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。

关系表
理解关系表，可以来看一个例子。
有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述，，价格，以及生产该产品的供应商。
把它们放在一张表中必然会有重复的信息，一个供应商的信息将重复在他生产的产品后出现，相同的数据出现多次绝不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联。
在这个例子中可建立两个表：一个存储供应商信息（products），一个存储商品信息（vendors）。vendors表的主键与products表关联。

创建联结

SELECT vend_name, prod_name, prod_priceFROM vendors, productsWHERE vendors.vend_id = products.vend_id;

内联结（inner join）

内联结也称为等值联结。

SELECT vend_name, prod_name, prod_priceFROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

注意此代码与上面代码的不同之处。

联结多个表

FROM…WHERE版本。

SELECT vend_name, prod_name, prod_price, quantityFROM vendors, products, orderitemsWHERE vendors.vend_id = products.vend_id  AND orderitems.prod_id = products.prod_id  AND order_num = 20007;

上面代码块中，WHERE子句中，前两个条件定义了三个表的联结关系，后一个用来过滤所要的信息。

FROM…ON版本。

SELECT vend_name, prod_name, prod_price, quantityFROM vendors INNER JOIN (products, orderitems) ON vendors.vend_id = products.vend_id  AND orderitems.prod_id = products.prod_id  AND order_num = 20007;

联结表的最大数要参照具体的DBMS，MySQL的限制是？没找到

别名

在SQL中可以给列名，表名和计算字段使用别名。

SELECT vend_name, prod_name, prod_price, quantityFROM vendors AS V, products AS P, orderitems AS OIWHERE V.vend_id = P.vend_id  AND OI.prod_id = P.prod_id  AND order_num = 20007;

高级联结

自联结（self-join）

SELECT cust_id, cust_name, cust_contantFROM customersWHERE cust_name = (SELECT cust_name                   FROM customers                   WHERE cust_contact = 'Jim');

等价于

SELECT C1.cust_id, C1.cust_name, C1.cust_contant   #因为cust_XX都有了两列，所以必须用完全限制名FROM customers AS C1, customers AS C2WHERE C1.cust_name = C2.cust_name                #首先要联结两个表 AND C2.cust_contact = 'Jim';                   #过滤数据

一般能用自联结的时候不用子查询。

自然联结（natural join）

自然联结排除多次出现，使每一列只返回一次。通常使用对一个表使用通配符，其他表的列使用明确的子集。

SELECT C.*, V.vend_name, P.prod_name, P.prod_price, OI.quantity

外联结（outer join）

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联的那些行。例：对每个顾客下的订单计数，包括至今未下订单的顾客。

SELECT c.cust_id, o.order_numFROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id;         #联结条件

外联结：联结包含了那些在相关表中没有关联行的行。即，在包含了orders表中没有关联行的，customers表中的行。

• LEFT：选择左边（OUTER JOIN的左边）的表的所有行；
• RIGHT：选择右边表的所有行；
• FULL：两个表；

使用带聚集函数的联结

检索所有顾客以及每个顾客下的订单数。

SELECT c.cust_id, COUNT(o.order_num) AS num_ordFROM customers AS c INNER JOIN orders AS o ON c.cust_id = o.cust_idGROUP BY c.cust_id;      

使用联结的要点

• 使用正确的联结条件，并且总是提供联结条件，否则会得出笛卡尔积；
• 在一个联结中可以包含多个表，升值对每个联结采取不同的联结类型。但在一起测试它们前请分别测试每个联结。

参考

《SQL必知必会》