mysql必知必会 十六章高级联结

1. 使用表别名

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 自联结
```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';```
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多


2.2 自然联结
2.3 外部链接
检索所有客户，包括那些没有订单的客户
与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

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

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


3. 使用带聚集函数的联结
聚集函数用来汇总数据，这些函数也可以与联结一起使用
检索所有客户及每个客户所下的订单数
```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;```