《SQL必知必会》--高级联结

第13课 创建高级联结

上一节课已经讲了简单的等值联结或者说，内联结。
本节课介绍高级联结。

简单等值联结的简单进阶–使用表别名

这一节相当简单，大致就一件事，可以对表命名表别名。

1
2
3
4
5
select cust_name, cust_contact
from customers as C, orders AS O, orderitems AS OI
where C.cust_id = O.cust_id 
	and O.order_num = OI.order_num
    and prod_id = 'RGAN01';

使用不同类型的联结

除内联结外还有三种联结方式————自联结、自然联结和外联结。

自联结

首先，独立思考一个问题，并自己在数据库管理系统中进行实操。

假如要给与$Jim Jones$同一个公司的顾客发送一封邮件。这个查询要求首先找出$Jim Jones$工作的公司，然后找出在该公司工作的顾客。

  1. 首先找出$Jim Jones$工作的公司
    1
    2
    3
    select cust_name
    from customers
    where cust_contact = 'Jim Jones';
  2. 然后找出在该公司工作的顾客
    1
    2
    3
    4
    5
    select cust_contact
    from customers
    where cust_name IN (select cust_name
    		from customers
    		where cust_contact = 'Jim Jones');
    以上的方法使用的是我们之前学过的子查询。
    除此之外，我们应该如何建立其他联结呢？请看代码示例。

首先看一个错误的示例：
INPUT

1
2
3
4
select c1.cust_contact, c2.cust_name, c2.cust_id
from customers as c1, customers as c2
where c1.cust_id = c2.cust_id
	and c1.cust_contact = 'jim jones';

OUTPUT

1
2
3
cust_contact  cust_name  cust_id
------------------------------------
Jim Jones	  Fun4All	 1000000003

在我的思维定式中，总是认为需要使用XXX_id才可以作为完全限定列名以联结两个表列，于是就被教育了。

再看看人家课本上的正确代码示例：

INPUT

1
2
3
4
select c1.cust_id, c1.cust_name, c2.cust_contact
from customers as c1, customers as c2
where c1.cust_name = c2.cust_name
	and c1.cust_contact = 'jim jones';

OUTPUT

1
2
3
4
cust_id     cust_name   cust_contact
------------------------------------
1000000003	 Fun4All	 Jim Jones
1000000003	 Fun4All     Denise L. Stephens
  • 自联结形式上就是将一个表分别命名为不同的两个表，然后对其进行等值联结。
  • 等值联结的列名的选取需要根据需要来选。比如本节就是根据一名联络人选择同一个企业 下的不同联络人。（本代码有无cust_id的结果是一样的）

自联结就是数据表与自身联结。

自然联结

自然联结是一种特殊的等值联结，他要求两个关系中进行比较的分列必须是相同的属性组，并且在结果中把重复的属性列去掉。

自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符*，而对其他表的列使用明确的子集来完成。

下面举一个例子：

1
2
3
4
5
6
select C.*, O.order_num,O.order_date, 
		OI.item_price, OI.prod_id, OI.quantity
from customers as C, orders AS O, orderitems AS OI
where C.cust_id = O.cust_id 
	and O.order_num = OI.order_num
    and prod_id = 'RGAN01';

外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候细节要包含没有关联的那些行。
例如，需要使用联结完成以下工作：

  1. 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
  2. 列出所有产品以及订购数量，包括那些没有人订购的产品；
  3. 计算平均销售规模，包括那些至今尚未下订单的顾客。

在上述例子中，联结包含了那些在相关表中没有关联的行。这种联结称为外联结。

对于上述问题，我首先想到的是NOT指令，然我们看看可不可以完成呢？

  1. 首先找到所有下订单的客户
    1
    2
    3
    select C.cust_name, O.orders_num
    from customers as C, orders AS O
    where C.cust_id = O.cust_id;
  2. 使用NOT指令
1
2
3
4
5
select cust_name
from customers
where cust_name not in (select C.cust_name
		from customers as C, orders AS O
		where C.cust_id = O.cust_id);

将上述两个代码合并才可以得到问题中想找到的所有的客户，并且还要在第二段代码现实空值。

这样的代码操作本质上还是内联结。

现在我们使用外联结。
INPUT

1
2
3
select customers.cust_id, customers.cust_name, orders.order_num
from customers left outer join orders
	on customers.cust_id = orders.cust_id;

OUTPUT

1
2
3
4
5
6
7
8
customers.cust_id  customers.cust_name  orders.order_num
------------------- -------------------------------------
1000000001	        Village Toys	    20005
1000000001	        Village Toys	    20009
1000000002	        Kids Place	
1000000003	        Fun4All	            20006
1000000004	        Fun4All	            20007
1000000005	        The Toy Store	    20008

外部联结是有方向的。LEFT OUTER JOIN 联结左边的表，RIGHT OUTER JOIN 联结右边的表（代表最终输出结果包括左边\右边的为被联结的行）
全联结FULL OUTER JOIN代表最终输出结果包括左边和右边的全部联结的和未被联结的行。

使用带聚集函数的联结

使用联结时可以正常使用聚集函数，不过需要使用完全限定列名。

1
2
3
4
5
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;

小结

本课是上一课的延续，两节课讲述完SQL中的联结。

  • 联结可以分为两类：内联结、外联结；
  • 内联结包括简单的等值联结、自联结、自然联结；
  • 外联结包括左外联结、右外联结、全联结；
  • 使用联结时的一个小技巧为使用表别名。