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  sales@villagetoys.com   20005   2004-05-01 00:00:00 1000000001
1000000001  Village Toys    200 Maple Lane  Detroit MI  44444   USA John Smith  sales@villagetoys.com   20009   2004-02-08 00:00:00 1000000001
1000000003  Fun4All 1 Sunny Place   Muncie  IN  42222   USA Jim Jones   jjones@fun4all.com  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
*/