SQL之高级联结查询

之前有篇文章介绍了简单的联结查询，现在介绍稍微复杂一点的 联结，如果你没有看过之前的文章，请点击下面的链接。
联结查询和子查询

本次示例使用的数据库关系图

SQL联结方式

内联结

之前一篇文章已经介绍过内联结了,主要是用于寻找两个表中的相等条件建立联结.

内联结又可以分为下面三个方面:

联结方式	定义
等值联结	不进行列名的去重,即此时会有重复的字段.,使用 = 作为联结的条件
不等值联结	不进行列名的去重,即此时会有重复的字段.,使用 = 作为联结的条件
自然联结	在等值,不等值联结的基础上去除重复的列.

等值联结

不进行列名的去重,即此时会有重复的字段.,使用 = 作为联结的条件

-- 此时会有重复的列
select *
from customers inner join orders
on orders.cust_id = customers.cust_id;

/*
 cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email, order_num, order_date, cust_id
 1000000001 Village Toys 200 Maple Lane Detroit MI 44444 USA John Smith [e-mail] 20005 2004-05-01 00:00:00 1000000001
1000000001 Village Toys 200 Maple Lane Detroit MI 44444 USA John Smith [e-mail] 20009 2004-02-08 00:00:00 1000000001
1000000003 Fun4All 1 Sunny Place Muncie IN 42222 USA Jim Jones [e-mail] 20006 2004-01-12 00:00:00 1000000003
*/

不等值联结

使用 > < != 作为判断联结的条件

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;
/*
 cust_id, num_ord
 1000000001 3
 1000000002 5
 1000000003 4
 1000000004 4
 1000000005 4
*/

自然联结

在等值,不等值联结的基础上去除重复的列.

-- 此时不会有重复的数据
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;

/*
 cust_id, num_ord
 1000000001 2
 1000000003 1
 1000000004 1
 1000000005 1
*/

外联结

许多联结将一个表中的行与另一个表 中的行相关联,但有时候需要包含没有关联的那些行,此时需要使用外联结,外联结使用outer join关键字。

外联结方式	定义
左外联结	在左边的表中选择所有行
右外联结	在右边的表中选择所有行
全外联结	包含两个表中所有不关联的行

左外联结

-- 检索所有顾客的订单
select customers.cust_id,orders.order_num
from customers left outer join orders
on orders.cust_id = customers.cust_id;
/*
 1000000001 20005
 1000000001 20009
 1000000002 NULL
 1000000003 20006
 1000000004 20007
 1000000005 20008
*/

全外联结

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

MySql不支持FULL OUTER JOIN，下面给出替代方法
select * from A left join B on A.id = B.id (where 条件）
union
select * from A right join B on A.id = B.id （where条件);
SQLite数据库不支持right outer join，只需将左右表调整位置即可解决这个问题。

交叉联结

在inner 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;

/*
 1000000001 2
 1000000003 1
 1000000004 1
 1000000005 1
*/

-- 使用左外联结，可以检索到下单个数为0的客户
select 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;
/*
 1000000001 2
 1000000002 0
 1000000003 1
 1000000004 1
 1000000005 1
*/