SQL学习之高级联结(自联结、自然联结、外联接)

create table Customers(
Id int identity(1000000,1),
Company varchar(30) null,
Name varchar(20) null
)
insert into Customers values('Fun4All','Tom')
insert into Customers values('Alibaba','Tim')
insert into Customers values('BaiDu','Jerry')
insert into Customers values('G0ogle','Herry')
insert into Customers values('Tencent','MaHuaTeng')
insert into Customers values('Fun4All','Bill')

这是讲解所需要的sql脚本

一、自联结       代码如下:

select * from Customers

现在有个需求,需要给Tom同一公司的所有会员发送一条邮件。

分析下基本思路,首先根据Tom找到其所在的公司名,在根据公司名找到其公司民下的所有会员。下面是解决代码:

select * 
from Customers 
where Company in(select Company from Customers where Name='Tom')

ok,通过子查询轻松的完成需求！

但是这边有一个重点,你必须要知道,子查询虽然方便,但是大多数DBMS的处理子查询的速度比处理联结要慢的多，如果数据量少的话,几乎没什么影响,但是如果后期数据会很大的话,就应该使用自联结！

下面是使用自联结的解决代码：

select *
from Customers c1,Customers c2
where c1.Company=c2.Company

这是自联结之后的效果！

select *
from Customers c1,Customers c2
where c1.Company=c2.Company
and c2.Name='Tom'

这个是两张表(实际是一张表)联结之后的结果图,ok完成需求！

自联结通过联结的机制,将所有公司名相同的记录联结到一起,即每一个公司的会员,都会和同一公司的其他会员联结一次,因为他们的公司名相同,这是时候指定c2.name='Tom'，就能拿到Tom和其公司所有会员的资料，这个时候就可以发邮件了！

 

 

二、自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中(被联结的列)。标准的联结返回所有的数据,相同的列甚至出现多次。而自然联结就是排除多次出现,是每一列只出现一次。

但是SQL不提供这项功能,自然联结的功能是需要你自己去完成的,自然联结要求你只能选择哪些唯一的列,一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成。

注意:我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

 

 

三、外联结(左外联结(LEFT OUTER JOIN)和右外联结(RIGHT OUTER JOIN))

许多联结将一个表中的行与另一个表中的行相关联,但有时候需要需要包含哪些没有关联的行。例如,有如下需求

(1)对每个顾客下的订单书进行计数,包括那些至今尚未下订单的顾客；

(2)列出所有产品以及订购数量，包括没有人订购的产皮

(3)计算平均销售规模,包括那些至今尚未下订单的顾客;

内联结代码如下:

select * from Customers
select * from Orders
select * 
from Customers a INNER JOIN Orders b
ON a.Id=b.CustomerId

 

这边我们使用了内联结检索出所有用户的订单，通过内联结的检索结果和外联结的检索结果比较,来更好的理解外联结！

通过结果图,发现内联结确实把所有用户的订单都检索出来了,但是这里内联结似乎把没有订单的用户给过滤掉了，所以我们分析得出内联结只把有订单的所有用户信息包括订单信息检索出来,但是如果用户没有订单,则该用户信息会被过滤！

 

下面再来看外联结代码:

select * from Customers
select * from Orders

select * 
from Customers a LEFT OUTER JOIN Orders b
ON a.Id=b.CustomerId

这边我们使用了左外联结检索出所有用户的订单

通过结果图,发现外联结也把所有用户的订单都检索出来了,但是这里外联结把没有订单的用户也检索出来了(即外联结能检索出没有关联行的行,对应的用null来填充其关联行),只是它的订单信息没有,所以对应的用null来替换,这就是外联结和内联结的最主要的差别,我们可以根据不同的业务需求,来选择性的使用它们!

 

 

四、全外连接(FULL OUTER JOIN)

select * from Customers
select * from Orders
select * 
from Customers a FULL OUTER JOIN Orders b
ON a.Id=b.CustomerId

分析Orders和Customers表结果图发现:赵六和冯七没有下订单,而订单表中的第四单没有与之对应的顾客，再看全连接之后的结果图,发现全联结把所有没有没有与之对应关联行的行全部用null填充，这就是全联结的作用！

 

五、使用带聚集函数的联结

聚集函数是用来汇总数据，在我前面的随笔中所用的聚集函数的例子都是从一个表中汇总数据,其实聚集函数也可以和联结一起使用,其实联结是多个小表组装而成的一个大表(你可以这样理解).

下面通过例子来了解其用法，代码如下:

select * from Customers
select * from Orders

现在有个需求,需要检索出所有顾客的订单数量(没有则为0),下面是解决代码:

select a.Id,a.Name,COUNT(b.CustomerId) 
from Customers a LEFT JOIN Orders b
ON a.Id=b.CustomerId
GROUP by a.Name,a.Id

ok,完成需求!

六、使用联结和联结条件(使用联结主要注意的要点)

(1)注意所使用的联结类型。一般我们使用内联结(INNER JOIN)使用外联结同样有效!   ---这一点上面'三'已论述

(2)保证使用正确的联结条件(不管采用哪种语法、那种联结),否则会返回不正确的数据。

(3)所有的联结都需要提供联结条件,否则会得出笛卡尔积。

(4)在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。这样做是合法的,也是有用的,但是在一起测试它们前分别测试每个联结。这样会使排除故障更简单。