mysql join 自联结_MySQL自学笔记_联结（join）

1.  背景及原因

关系型数据库的一个基本原则是将不同细分数据放在单独的表中存储。这样做的好处是：

1).避免重复数据的出现

2).方便数据更新

3).避免创建重复数据时出错

例子：

有供应商信息和产品信息两部分。如果将他们放在一个表中。弊端有：

1). 同一供应商的所有数据所在行的供应商信息相同，即产生重复数据。

2). 在供应商信息变更时，如果更换联系方式或地址，需要在所有包含此供应商信息的行更新。

3). 在录入同一供应商的多个产品时，有可能会供应商信息不同，导致调取数据时无法使用。

通过将供应商和产品信息分别放在不同的表中，可以避免以上弊端。

1). 一个供应商只需一条记录，省时省空间。

2). 变更供应商信息时，只需变更一次。方便快捷不易出错。

3). 录入信息时，不会出错。

DB中存在如下几张表：

orders:

order_num， order_date， cust_id

venders:

vend_id, vend_name, vend_address, vend_contact

products:

prod_id, prod_name, vend_id, prod_price

customers:

cust_id, cust_name, cust_address, cust_city, cust_state

orderitems:

order_num, order_item, prod_id, quantity, item_price

2. 表的联结

由于以上原因，在调出多个表中信息时，就需要将多个表通过主键和外键联结。

联结的基本方法：1. 指出需要联结的表；2. 指出通过哪个字段联结。

例子:

需要供应商和所提供产品信息：

select vend_name, prod_name, prod_price #selectfieldsfrom vendors, products #fromtableswhere vendors.vend_id=products.vend_id #how to join tables;

3. 两种联结方法

除了上面的在where字句中创建联结，还可以使用关键字join ... on...

例如上面的语句还可写成：

select vend_name, prod_name, prod_price #selectfieldsfrom vendors inner join products #fromtableson vendors.vend_id=products.vend_id #how to join tables;

此处注意，在指定联结字段时，需要使用完全限定列名，既table.column的格式。

笛卡儿积：当没有制定联结字段时会出现笛卡儿积。既，被联结的两个表中任意一行都和另一个表中所有行联结。

4. 联结多个表

在一条SQL语句中可以联结任意多张表。但是要注意：联结表非常消耗数据库系统资源，所以一定要注意控制联结的使用。

一个例子：

selectprod_name, vend_name, prod_price, quantityfromproducts, vendors, orderitemswhere products.prod_id =orderitems.prod_idand vendors.vend_id=products.vend_idand orderitems = 20005;

5. 联结的类型

1). 内联结和外联结

内联结：查找出的行是通过两个表的相等测试的数据。inner join on

外联结：在联结是指定一个表，并反回其中所有行，无论是否通过相等测试。外联结包括左联结和右联结。left/right outer join on

一个例子：检索系统中所有下了订单的客户id和订单数量

内联结：

selectcustomers.cust_id, orders.order_numfrom customers inner joinorderson customers.cust_id= orders.cust_id;

只有下了订单的客户信息会被检索到。

外联结：

selectcustomers.cust_id, orders.order_numfrom customers left outer joinorderson customers.cust_id= orders.cust_id;

left outer join左边的customers表中所有的行都会被检索到。不论客户是否下单。

左联结和右联结的区别：

左联结关键字的左边的表会被检索出所有行，右联结关键字右边的表会被检索出所有行。所以左联结和右联结可以轻易转换，在实现功能上没有区别。

2).自联结

在一些特定情况下，需要让一张表自己和自己做联结，就需要用到自联结。例如需要查出生产了产品ID为DTNTR的供应商的所有产品。

使用子查询：

selectprod_id, prod_namefromproductswhere vend_id in (selectvend_idfromproductswhere prod_id="DTNTR");

使用自联结：

selectp1.prod_id, p2.prod_namefrom products as p1 inner join products asp2on p1.vend_id=p2.vend_idand p2.prod_id="DTNTR";

3).自然联结

所有查找出的列都是唯一的，不会有一个行被输出两次。自然联结需要通过人工手动实现，没有公式或关键字能制定自然联结。

6. 有聚合函数的联结

联结可以和聚合函数一起使用。例如，需要检索出所有客户的订单数：

内连接：

selectcustomers.cust_id, customers.cust_name,count(orders.order_num) asnum_ordfrom customers inner joinorderson customers.cust_id inner joinorders.cust_idgroup by customers.cust_name;

检索出所有已下单客户的订单数。

外联结：

selectcustomers.cust_id, customers.cust_name,count(orders.order_num) asnum_ordfrom customers left outer joinorderson customers.cust_id inner joinorders.cust_idgroup by customers.cust_name;

检索出所有客户的订单数，包括没有订单数为0的客户。

7. 有关full join

MySQL不支持full join 关键字，但是可以通过union间接实现。

full join: 既将两张表通过连接字段连接，两张表的行都会保留无论是否经过了相等测试。

通过下面的MySQL中的替代方式也可以理解full join的原理：

selectproducts.prod_name,products.prod_price,venders.vend_name,venders.vend_contactfromvendersleft outer joinproductson venders.vend_id =products.vend_idunion

selectproducts.prod_name,products.prod_price,venders.vend_name,venders.vend_contactfromvendersright outer joinproductson venders.vend_id = products.vend_id