15联结表
1外键&联结
外键(forergn key):外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。是实现联结表的前提。
联结: 是检索的一种机制,用来在一条select语句中关联表,因此称之为联结。可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
2创建联结
上述SQL实现了内部联结(等值联结)。作用是从vendors,products表中查找vend_name,prod_name,prod_price列,并匹配vendors.vend_id=products.vend_id,返回结果先按vend_name排序,再按prod_name排序。
(上述等号两端用到了完全限定列名,要求在引用可能出现二义性时,必须使用完全限定列名)
检索结果有三列,其中后两列位于同一个表中,第一列位于另一个表中。它们是通过where子句进行联结并返回匹配行的,from后跟所有关联的表。
如果没有where子句,第一个表中的每个行将与第二个表中的每个行配对,而不管它们逻辑上是否可以匹配。(返回结果叫笛卡尔积:由没有联结条件的表关系反回的结果为笛卡尔积。检索出的行的数目是第一个表中的行数乘以第二个表中的行数。)
上述SQL的另外一种句法:
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
3联结多个表
前一篇笔记提到的子查询:
联结表的方法可以更简洁的检索相同数据:
16高级联结表
1表别名
第2篇笔记第10节中提到了列别名,同样也可以使用表别名,例如上一小节最后的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 = 'TNT2';
表别名只供查询不返回客户端。
2使用不同类型的联结
2.1自联结
现在要找到生产ID为DINTER的物品的供应商,然后找出这个供应商生产的其他物品。使用子查询的句法如下:
再看看使用自联结:
此查询需要的两个表实际上是想同的表,因此products在from语句中出现了两次。这是合法的,但是对products的引用具有二义性。为解决此问题,使用了表别名。products第一次出现为p1,第二次出现为p2。where(通过匹配p1中的vend_id和p2中的vend_id)首先(自)联结两个表,然后按第二个表的prod_id过滤数据并返回。
2.2自然联结
无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结(15节中的内部链结)返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次。事实上,之前的每个联结都是自然联结,很可能我们永远都不会用到不是自然联结的内部联结。
2.3外部联结
外部联结中包含了在相关表中没有关联的行。
下面的select语句给出一个简单的内部联结(INNER JOIN)。他检索所有客户及其订单(即返回客户与订单的关联行):
如果使用外部联结(OUTER JOIN),就可以检索所有客户,包括没有订单的客户(即除了关联行,不关联的行同样返回):
外部联结分为左外部联结和右外部联结,可以理解为以OUTER JOIN 左边的表还是右边的表为基准。如上LEFT OUTER JOIN就是以OUTER JOIN 左边的表(customers表)为基准,返回customers表的所有列,如果order_num中有未关联行则返回NULL。
3使用带聚集函数的联结
3.1
此SELECT语句使用INNER JOIN 将customers和orders表互相关联。GROUP BY子句按客户(cust_id)分组数据,然后调用COUNT(orders.order_num)对每个客户的订单计数,将它作为num_ord返回。
3.2
聚集函数也可以与外部联结一起使用,如:
使用左外部联结包含了所有用户(关联和不关联)。结果显示包含了客户Mouse House,他有0个订单。
4使用联结和联结条件
- 注意使用的联结类型。经常使用的是内部联结。
- 保证使用正确的联结条件,否则返回不正确的结果。
- 应该总是提供联结条件,否则会返回笛卡尔积。