SQL学习笔记之创建高级联结

使用表别名

列别名使用方法：列的表达式/对列聚集函数/对列使用其他函数 as 列别名
表别名使用方法：在from子句中，表名 as 表别名
给表起别名有两个优点：

1. 缩短SQL语句，使得语法更简洁
2. 可以在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 = 'RGAN01';

在完全限定列名中可以使用表别名，更加简洁明了
表别名只在查询执行中使用，和列别名不一样，表别名不返回到客户端

自联结

在一条select语句中需要引用多次相同的表时，可以使用自联结

select cust_id, cust_name, cust_contact
from Customers
where cust_name = (select cust_name
                   from Customers
                   where cust_contact = 'Jim Jones');

这个例子中，Customers表被检索两次，第一次检索出cust_contact含有Jim Jones的顾客的名字，第二次检索出对应顾客的顾客ID， 顾客名和顾客联系方式，可以将这种检索同一个表多次的子查询转化为自联结：

select C1.cust_id, C1.cust_name, C1.cust_contact
from Customers as C1, Customers as C2
where C1.cust_name = C2.cust_name
    and C2.cust_contact = 'Jim Jones';

将两个Customers进行自联结，会出现多个名字相同的列，为了区分两个表中相同的列避免混淆，每一列都使用对应的完全限定列名
自联结查询速度比子查询快得多

自然联结

在对表进行内联结时，有些列会重复出现（有的列在多个表中存在）
自然联结会排除掉重复列，使得每个列只出现一次。一般对一个表返回其所有列，对其他表的列进行选择

select C.*, O.order_num, O.order_data, OI.prod_id, OI.quantity, OI.item_price
from Customers as C, Orders as O, OderItems as OI
where C.cust_id = O.cust_id
    and OI.order_num = O.order_num
    and prod_id = 'RGAN01';

返回Customers表中的所有列（C.*），其他表中的列进行选择
大部分内联结都是自然联结，可能永远不会用到不是自然联结的内联结

外联结

联结将两个表中的行进行关联，比如存货表和顾客表通过产品id进行关联，但是两个表中有的行没有产品id
内联结只关联所有具有产品id的行，其他没有产品id的行不被联结，外联结会关联所有的行

SELECT Customers.cust_name, Orders.order_num 
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

只检索出所有具有顾客id的顾客名及其订单数
如果要检索出所有顾客的顾客名及其订单数（包括没有顾客id的顾客），需要使用外联结

SELECT Customers.cust_name, Orders.order_num 
FROM Customers LEFT JOIN Orders
ON Customers.cust_id = Orders.cust_id;

on关键字后面是联结条件
left join左外联结左边的表，即包括左边表（Customers）的所有行
right join右外联结右边的表，即包括右边表（Orders）的所有行
full join全外联结两边的表，即包括左右两个表的所有行

使用带聚集函数的联结

可以使用聚集函数从多个表中汇总数据，需要先将多个表联结再使用聚集函数
要检索所有顾客及其对应的订单数

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

联结两个表之后，对顾客进行分组计算每个顾客的订单数，因为有两个表，为避免歧义每一列都使用完全限定列名
表进行联结以后其他功能照常使用即可

联结注意事项：

1. 注意选取合适的联结类型
2. 不同的DBMS对应不同的联结语法，注意区分
3. 要提供正确的联结条件
4. 联结后注意使用完全限定列名避免混淆
5. 可以联结多个表，每个联结可以是不同的联结类型