SQL最强大的功能之一就是能在数据查询的执行中联结(join)表。
关系表
理解关系表,可以来看一个例子。
有一个包含产品目录的数据库表,其中每类物品占一行。对于每一种物品,要存储的信息包括产品描述,,价格,以及生产该产品的供应商。
把它们放在一张表中必然会有重复的信息,一个供应商的信息将重复在他生产的产品后出现,相同的数据出现多次绝不是一件好事,这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表,一类数据一个表。各表通过某些共同的值互相关联。
在这个例子中可建立两个表:一个存储供应商信息(products),一个存储商品信息(vendors)。vendors表的主键与products表关联。
创建联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
内联结(inner join)
内联结也称为等值联结。
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
注意此代码与上面代码的不同之处。
联结多个表
FROM…WHERE版本。
SELECT vend_name, prod_name, prod_price, quantity
FROM vendors, products, orderitems
WHERE 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, quantity
FROM 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, quantity
FROM vendors AS V, products AS P, orderitems AS OI
WHERE 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_contant
FROM customers
WHERE 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 C2
WHERE 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_num
FROM 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_ord
FROM customers AS c INNER JOIN orders AS o
ON c.cust_id = o.cust_id
GROUP BY c.cust_id;
使用联结的要点
- 使用正确的联结条件,并且总是提供联结条件,否则会得出笛卡尔积;
- 在一个联结中可以包含多个表,升值对每个联结采取不同的联结类型。但在一起测试它们前请分别测试每个联结。
参考
《SQL必知必会》