南大通用GBase 8s连接查询全解析

在数据密集型的业务场景中，理解和应用多表连接查询是数据库管理员和分析师的核心技能之一。南大通用GBase 8s数据库通过其强大的连接查询功能，使用户能够在不同的数据表之间建立高效的数据关联。本文介绍GBase 8s中的连接查询，包括内连接、外连接、自连接以及多表连接，并通过实例演示其应用，帮助读者理解和掌握。

表1 客户表（customer）实例数据

客户编号 customer_num	姓名 cname	单位 company	地址 address	城市 city	国家 state	电话 phone
101	张一川	金牌大风	解放路18号	香港	中国	13887898075
102	李凤	华谊兄弟	光明路19号	北京	中国	13058221289
103	王宝强	乔杰力娱乐	中华路88号	北京	中国	15853284543
104	杰克	新索娱乐	卡梅尔路422号	洛杉矶	美国	4157433611
105	刘杰		复兴路38号	北京	中国	18853898789

 

表2 订单表（orders）实例数据

订单编号 order_num	客户编号 customer_num	订单日期 order_date	交易日期 ship_date	付款日期 paid_date
1001	101	05/20/2014	06/01/2014	07/22/2014
1002	101	05/21/2014	05/26/2014	06/03/2014
1003	104	05/22/2014	05/23/2014	06/14/2014
1004	102	06/07/2014	07/06/2014	07/21/2014
1005	104	06/22/2014	07/10/2014	07/31/2014

 

表3 订单明细表（items）实例数据

订单明细编号 item_num	订单编号 order_num	商品名称 ship_name	商品单价 ship_price	数量 quantity
10001	1001	商品HRO	250.0	1
10002	1002	商品HSK	960.0	2
10003	1002	商品HSKA	240.0	3
10004	1003	商品ANZA	19.8	5
10005	1004	商品HRO	250.0	1
10006	1005	商品HRR	480.0	5

一、内连接 (join on)

a）说明

内连接只返回两个表中与连接谓词匹配的行，不匹配的行不会被输出。

b）举例

• 需求：查询每位顾客的联系方式及其订货日期
• 分析：顾客信息存放在customer表中，订单日期存放在orders表中，本查询需要涉及两个表，两个表之间的联系是通过customer表的主键customer_num和orders表的外键customer_num实现的。
• 查询语句：

select customer.customer_num, cname, phone, order_num, order_date
from customer join orders
on customer.customer_num=orders.customer_num;

• 查询结果：

customer_num	cname	phone	order_num	order_date
101	张一川	13887898075	1001	05/20/2014
101	张一川	13887898075	1002	05/21/2014
104	杰克	4157433611	1003	05/22/2014
102	李凤	13058221289	1004	06/07/2014
104	杰克	4157433611	1005	06/22/2014

2、外连接（Outer Join）

内连接操作的结果只输出两个表中在连接谓词上匹配的行。如果希望连接操作将左表或者右表中不匹配的行也输出，就需要使用外连接了。外连接分为左外连接、右外连接和完全外连接。

1）左外连接（left outer join on）

a）说明：

左外连接会返回左表中全部的行，即使右表中没有找到匹配的行。如果左表中的行在右表中没有匹配的行，对应的行上来自右表的属性为空值。

b）举例：

• 需求：查询每位顾客的订购物品的信息，目前没有订单的顾客也要列出
• 分析：顾客信息在customer表中，物品的信息在orders表中，两个表通过customer表中的主键customer_num和orders表中的外键customer_num连接。
• 查询语句：

select customer.customer_num, cname, phone, order_num,order_date
from customer left outer join orders
on customer.customer_num=orders.customer_num;

• 查询结果：

customer_num	cname	phone	order_num	order_date
101	张一川	13887898075	1001	05/20/2014
101	张一川	13887898075	1002	05/21/2014
102	李凤	13058221289	1004	06/07/2014
103	王宝强	15853284543
104	杰克	4157433611	1003	05/22/2014
104	杰克	4157433611	1005	06/22/2014
105	刘杰	18853898789

2）右外连接（right outer join on）

a）说明：

右外连接会返回左表中全部的行，即使左表中没有找到匹配的行。如果右表中的行在左表中没有匹配的行，对应的行上来自左表的属性为空值。

b）举例（与左连接类似，不再列举）：

• 需求：查询每位顾客的订购物品的信息，目前没有订单的顾客也要列出
• 分析：顾客信息在customer表中，物品的信息在orders表中，两个表通过customer表中的主键customer_num和orders表中的外键customer_num连接。
• 查询语句（使用右外连接，修改左右表位置即可）：

select customer.customer_num, cname, phone, order_num,order_date
from orders left outer join customer
on orders.customer_num=customer.customer_num;

• 查询结果：

customer_num	cname	phone	order_num	order_date
101	张一川	13887898075	1001	05/20/2014
101	张一川	13887898075	1002	05/21/2014
102	李凤	13058221289	1004	06/07/2014
103	王宝强	15853284543
104	杰克	4157433611	1003	05/22/2014
104	杰克	4157433611	1005	06/22/2014
105	刘杰	18853898789

 3、自连接（self-join）

a）说明：

自连接是指一个表与自身进行连接操作，它是特殊形式的内连接。

b）举例：

• 需求：查询提交了一次以上订单的顾客信息
• 分析： 在订单表orders中查看拥有不同的订单的同一个顾客的信息，将customer_num输出。

 该例中将orders表按照customer_num属性等值进行了连接，由于连接的两个表名字相同，无法使系统区分何时调用哪张表，因此需要为两张表取别名A和B ，按照要在同一行中出现不同的订单号，就说明该顾客至少签订了两个订单，通过A.order_num<>B.order_num条件可以过滤掉同一行订单记录自身匹配的情况。

• 查询语句：

select distinct A.customer_num
from orders A inner join orders B on A.customer_num=B.customer_num
where A.order_num<>B.order_num;

或

select distinct A.customer_num
from orders A, orders B
where A.customer_num=B.customer_num and A.order_num <>B.order_num;

或（不用连接查询，使用嵌套查询）

select A.customer_num, count(*)
from orders
group by customer_num having count(*) >1;

• 查询结果：

customer_num

101

104

4、多表连接（Multiple Table Joins）

a）说明：

将两个以上的表进行连接的操作称为多表连接。连接多张表需要使用多个join关键字。可以将多表连接的执行过程理解为：第1张表与第2张表连接，将得到的中间结果表再与第3张表连接，依此类推，直到获得最终结果表。

b）举例：

• 需求：查询每位顾客提交订单的日期、所定的物品名称及物品价格
• 分析：由于内连接运算满足交换律和结合律，多张表的连接顺序不会改变结果表，所以DBMS不一定按照表的先后顺序依次执行连接，而是根据执行代价选择最优的连接顺序。
• 查询语句：

select cname, order_date, ship_name, (ship_price * quantity) as total_price
from customer join orders
on customer.customer_num=orders.customer_num join items on orders.order_num=items.order_num;

或

select cname, order_date, ship_name, (ship_price * quantity) as total_price
from customer , orders , items
where customer.customer_num=orders.customer_num and orders.order_num=items.order_num;

• 查询结果：

cname	order_date	ship_name	total_price
张一川	05/20/2014	商品HRO	$250.00
张一川	05/21/2014	商品HSK	$1920.00
张一川	05/21/2014	商品HSKA	$720.00
杰克	05/22/2014	商品ANZA	$99.00
李凤	06/07/2014	商品HRO	$250.00
杰克	06/22/2014	商品HRR	$2400.00

通过本文的深入分析和实例演示，我们希望读者能够更加熟练地运用GBase 8s的连接查询功能，以解决实际工作中的数据整合需求。连接查询不仅提高了数据处理的效率，也极大地丰富了数据分析的深度和广度。我们期待您通过本文的学习，能够进一步提升在数据库查询优化和数据挖掘方面的专业能力。