sql必知必会学习记录（十二）- 联结表

sql必知必会学习记录（十二）- 联结表

1. 联结

• 等值联结（内部联结）

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

笛卡儿积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。又称为叉联结。

• 内部联结

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

SQL规范首选INNER JOIN语法。

• 联结多个表

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

性能考虑 MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

2. 创建高级联结

• 自联结

//使用子查询来完成查询
SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='DTNTR');

//使用自联结查询
SELECT p1. prod_id, pl. prod_name FROM products AS p1, products AS p2 WHERE p1. vend_id=p2. vend_id AND p2. prod_id=' DTNTR';

用自联结而不用子查询.自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句.虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多.应该试一下两种方法,以确定哪一种的性能更好.

• 使用表别名

SELECT
	CONCAT(
		RTRIM(vend_name),
		' (',
		RTRIM(vend_country),
		')'
	) AS vend_title
FROM
	vendors
ORDER BY
	vend_name;

• 使用不同类型的联结

  内联结

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

上面这条条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN。

外部联结的类型存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

带聚集函数的联结

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