SQL （十二）创建高级联结

表的别名（AS关键字）

之前只用as给计算字段起过别名，由于有时候不区分计算字段和列，所以也可以说as可以用于给列起别名。总之，as可以给列和计算字段起别名，还可以给表起别名。

select concat(vend_name, '(', vend_country, ')') 
as vend_title
from vendors
order by vend_name;

计算字段必须用as起别名，因为不起名字就是匿名的，没法用。
但是给表起别名则是真的别名，可选的，不起人家也有名字。

select cust_name, cust_contact
from customers as C, orders as O, orderItems as OI
where prod_id = 'RGAN01'
AND OI.order_num = O.order_num
and C.cust_id = O.cust_id;

为什么给表起别名

一直觉得这种起别名的方式很类似C和C++的typedef关键字，所以作用当然也有类似的点：缩短代码。

很难理解，用一个例子来说明：

示例：在一条select语句多次引用一个表

太牛了

假设，我们要给和Jim Jones同一公司的所有顾客发邮件，那么就要找出和Jim Jones同公司的所有顾客，假设我们只找名字，邮箱先不说。

那么首先应该根据Jim Jones来找到Jim Jones工作的公司，然后再去找所有在这个公司工作的顾客。

使用子查询

最内层的查询：找到Jim Jones工作的公司

select cust_name
from Customers
where cust_contact = "Jim Jones";

结果是fun4all公司。

然后直接加上外层查询，写成嵌套的样子：

select cust_id, cust_name, cust_contact
from Customers
where cust_name in (select cust_namefrom Customerswhere cust_contact = "Jim Jones");

于是就找到了Dennise这个顾客。

使用自联结搭配表别名

select c1.cust_id, c1.cust_name, c1.cust_contact
from Customers as c1, Customers as c2 -- 牛不牛逼
where c2.cust_contact = "Jim Jones"
and c1.cust_name = c2.cust_name;

注意别名虽然在第二行才定义，但是第一行就在用了，因为这是一个语句，分行只是格式而已。

第一个联结条件用于过滤出c2表中所有联系人为Jim Jones的行，其实就一行，即公司名cust_name为fun4all的那行；

第二个条件再过滤出c1表中所有和c2表中过滤出的行的公司名相同的行，于是就得到这两行结果。

注意最后显示的是c1表的过滤结果，这个是重要的，因为c2被过滤的只剩一行了

如果把第一行代码改为c2

select c2.cust_id, c2.cust_name, c2.cust_contact
from Customers as c1, Customers as c2 -- 牛不牛逼
where c2.cust_contact = "Jim Jones"
and c1.cust_name = c2.cust_name;

则显示两次jim的信息，因为c2被过滤的只剩一行，却又和c1匹配了2次

• 很多DBMS处理联结的速度比子查询快得多。所以还是尽量用联结。虽然不懂为什么会快得多，大概是底层代码原因。
• 用表别名引用的两个表虽然是同一个表，但是DBMS却把他们完全当做两个表，这样真的很方便。

和列别名的区别：不返回到客户端；只在查询时使用

其他联结（除内联结外）

自联结 self join

见上一节的 示例：在一条select语句多次引用一个表

自然联结 natural join：可能永远也不会用到不是自然联结的内联结

由于没出现过不是自然联结的内联结，所以并不清楚相同列多次出现是啥样的

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

可以看到select后面都是完全限定列名

外联结 outer join

大概明白意思，因为如果一个顾客没有下订单，他这行就不会关联到orders表的某一行，于是联结时就过滤不出来他这一行。

示例：左外联结和右外联结

假设要检索出所有顾客，以及他们的订单编号，使用内联结写代码：

select Customers.cust_id, Orders.order_num
from Customers inner join Orders
on Customers.cust_id = Orders.cust_id;

• 用了完全限定列名指明返回数据来自哪个表
• inner join是用在from子句中的，把顾客表和订单表联结起来了
• 联结的条件用on子句写

如果用外联结写：

select Customers.cust_id, Orders.order_num
from Customers left outer join Orders
on Customers.cust_id = Orders.cust_id;

• left outer join，则要选择顾客表的所有行，即没在orders表的不满足联结条件的行也过滤出来。2号顾客没下订单，也被检索出来了。
• 如果用right，则订单表的所有行都有，但是有订单编号就一定有顾客编号，因为下单了就已经是顾客了，所以这样得到的结果和内联结一样。

select Customers.cust_id, Orders.order_num
from Customers right outer join Orders
on Customers.cust_id = Orders.cust_id;

• 左外联结和右外联结可以互换，只要把表的顺序换一下就好了

select Customers.cust_id, Orders.order_num
from Orders right outer join Customers
on Customers.cust_id = Orders.cust_id;

全外联结（mysql不支持）

相当于左外联结和右外联结的结合版本了

select Customers.cust_id, Orders.order_num
from Orders full outer join Customers
on orders.cust_id = Customers.cust_id;

代码报错，因为mysql不支持

带聚集函数的联结

之前学了5个聚集函数，count是其中之一

要和group by 一起用

内联结

select Customers.cust_id,count(Orders.order_num) as num_order
from Customers inner join Orders
on Customers.cust_id = Orders.cust_id
group by Customers.cust_id;

外左联结

select Customers.cust_id,count(Orders.order_num) as num_order
from Customers left outer join Orders
on Customers.cust_id = Orders.cust_id
group by Customers.cust_id;

使用联结的注意要点

用联结条件：

select cust_name, cust_contact
from customers as C, orders as O, orderItems as OI
where prod_id = 'RGAN01'
AND OI.order_num = O.order_num
and C.cust_id = O.cust_id;

不用联结条件，返回笛卡尔积：

select cust_name, cust_contact
from customers as C, orders as O, orderItems as OI;

下图不完整，但是能明白意思