联结表

连结

关系表

例如：两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商应具有唯一的标识，称为主键（primary key）。products表只存储产品信息，除了存储供应商ID之外不存储其他的供应商信息。vendors表的主键又叫products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

例：定义外键

ALTER TABLE orderitems
ADD CONSTRAINT fk_irderitems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_irderitems_products
FOREIGN KEY (prod_id) REFERENCES products(prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_irderitems_customers
FOREIGN KEY (cust_id) REFERENCES customers(cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_irderitems_vendors
FOREIGN KEY (vend_id) REFERENCES vendors(vend_id);

在使用关系表时，仅在关系列中插入合法的数据非常重要，如果在products表中插入拥有非法供应商ID（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。

创建连结

SELECT vend_name, prod_name, prod_price FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

两个表用WHERE子句联结

笛卡儿积：由没有连结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

内部连结

等值联结：基于两个表之间的相等测试，也叫内部连结

可以使用另一种语法来明确指定联结的类型

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

FROM 子句的组成部分，以INNER JOIN指定，联结条件用ON子句

联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
#显示编号为20005的订单中的物品。订单物品存储在orderitems表中，按每个产品的ID存储。
它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商

例：返回订购产品INT2的客户列表

SELECT cust_name,cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

创建高级联结

使用表别名

SELECT Concat(RTrim(vend_name),'('Rtrim(vend_country),')') AS vend_title
FROM vendors ORDER BY vend_name;

别名除了用于列名和计算字段外，SQL还允许给表起别名，这样做有两个主要理由：

• 缩短SQL语句
• 允许在单条SELECT语句中多次使用相同的表。

例：

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';

使用不同类型的联结

自联结

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

#子查询
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
#output
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
#使用自联结
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';

自然联结

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

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

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

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

外部联结

有时候需要包含没有关联的那些行

例：需要联结来完成以下工作

• 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户
• 列出所有产品及订购数量，包括没人订购的产品
• 计算平均销售规模，包括那些至今尚未下订单的客户

#内部联结
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中选择所有的行。

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

使用带聚集函数的联结

例：检索所有客户及每个客户所下的订单数

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

聚集函数也可以方便地与其他联结一起使用。例：

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。

使用联结和联结条件

• 注意所使用的联结类型，一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡尔积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。应该在一起测试他们前分别测试每个联结。