数据库操作：创建高级联结

https://blog.youkuaiyun.com/xylyaya/article/details/104674541

迄今为止，我们使用的只是称为内部联结或等值联结的简单联结。

还有三种其他联结：自联结、自然联结、外部联结。

 

自联结

假如发现某物品（ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNRT的物品的供应商，然后找出这个供应商生产的其他物品。

SELECT prod_id,prod_name
FROM products
WHERE vend_id=(SELECT vend_id
               FROM products
               WHERE prod_id='DTNTR')

它使用了子查询。内部SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。

现在来看使用联结的相同查询：

SELECT p1.prod_id,p1.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id=p2.vend_id
   AND p2.prod_id='DTNTR';

此查询需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。为解决此问题，使用了表别名。products第一次出现为别名p1，第二次出现为别名p2。SELECT使用p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名prod_id，prod_name的列。MySQL不知道想要的是哪一列（虽然它们事实上是同一个列）。

WHERE（通过匹配p1中的vend_id和我p2的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。

 

自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符，对所有其他表的列使用明确的子集来完成的。

SELECT c.*,o.order_num,o.order_date,
       oi.prod_id,oi.quantity,oi.item_price
FROM customer 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='FB';

在这个例子中，通配符只对第一个表使用。所有其它列明确列出，没有重复的列被检索出来。

事实上，迄今为止我们建立的每个内部联结都是自然联结。

 

外部联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候会需要包含没有关联行的那些行。

联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

下面的SELECT语句给出了一个简单的内部联结。它检索所有客户及订单。

SELECT customers.cust_id,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id;

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行：

SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id;

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子从customers表中选择所有行。

外部联结形式：左外部联结和右外部联结。