详解SQL Server连接(内连接、外连接、交叉连接)

by admin on 2019年9月24日

1.连接

  有时候需要将连个表的数据合并成一个结果集来显示。为了解决这个问题,就需要用到JOIN连接。

 

在查询多个表时,我们经常会用“连接查询”。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。

在查询多个表时,我们经常会用“连接查询”。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。

首先准备了两个表 (Student 和 Course),其中 Student 表中的 C_S_Id
字段为外键列,关联的是 Course 表的 C_Id 主键列。

首先准备了两个表 (Student 和 Course),其中 Student 表中的 C_S_Id
字段为外键列,关联的是 Course 表的 C_Id 主键列。

2.内部连接

  内部连接根据一个或几个共同的字段将记录匹配到一起。内部连接仅仅返回那些存在字段匹配的记录。

  图片 1

  例子:

       film表                                 Actors表

 

  图片 2                    图片 3

 

          图片 4图片 5

 

   内部连接的关键就是排他性,如上面的Actor表中演员Bogart因为在Film表中没有对应的ID,所以在结果集中就没有显示。

  

  

图片 6

图片 6

3. 外部连接

   外部连接本质上是包含!!!

  图片 8

    

   
左外部连接:个人理解就是左表边的全保留(包含),如果右边根据条件没有对应的行,则全是NULL,但还是会保留,不像内连接是直接不显示!

      
       Action表                        Film表

      图片 9 
 图片 10

                         内连接

       图片 11

 

        左外连接(左表Film表)                                右外连接(左表Film表)

图片 12 
           
  图片 13

什么是连接查询呢?

  

    概念:根据两个表或多个表的列之间的关系,从这些表中查询数据。

    目的:实现多个表查询操作。

 

什么是连接查询呢?

  

    概念:根据两个表或多个表的列之间的关系,从这些表中查询数据。

    目的:实现多个表查询操作。

 

 

 

 4.完全连接

  顾名思义,将两个表的数据全部匹配。相当于左外连接加右外连接!

图片 14

知道了连接查询的概念之后,什么时候用连接查询呢?

     

   
一般是用作关联两张或两张以上的数据表时用的。看起来有点抽象,我们举个例子,做两张表:学生表(T_student)和班级表(T_class)。

 

                                            T_student                  
                                                  T_class

     
 图片 15 
   
  图片 16

 

知道了连接查询的概念之后,什么时候用连接查询呢?

     

   
一般是用作关联两张或两张以上的数据表时用的。看起来有点抽象,我们举个例子,做两张表:学生表(T_student)和班级表(T_class)。

 

                     T_student                                  
T_class

    图片 17 
 图片 18     
  图片 19

 

内连接(inner
join)
:满足on条件表达式,内连接是取满足条件表达式的两个表的交集(即两个表都有的数据)。

内连接(inner
join)
:满足on条件表达式,内连接是取满足条件表达式的两个表的交集(即两个表都有的数据)。

5.交叉连接

  交叉连接不使用ON运算符,使用CROSS关键字。将左侧表与右侧表所有的记录连接,返回所有记录的笛卡尔积。

  图片 20

连接标准语法格式:

    

    SQL-92标准所定义的FROM子句的连接语法格式为:

    FROM  join_table join_type join_table[ON (join_condition)]

   
其中join_table指出参与连接操作的表名,连接可以对同一个表操作,也可以对多表操作,对同一个表操作的连接又称做自连接。join_type
指出连接类型。join_condition指连接条件。

 

连接标准语法格式:

    

    SQL-92标准所定义的FROM子句的连接语法格式为:

    FROM  join_table join_type join_table[ON (join_condition)]

   
其中join_table指出参与连接操作的表名,连接可以对同一个表操作,也可以对多表操作,对同一个表操作的连接又称做自连接。join_type
指出连接类型。join_condition指连接条件。

 

1 select * from Student s
2 inner join Course c on s.C_S_Id=c.C_Id
1 select * from Student s
2 inner join Course c on s.C_S_Id=c.C_Id

6.联合

  联合(UNION)运算符,用于使两个或两个以上的查询产生一个结果集。

  把一个结果集拼到另一个结果集的末尾。

  使用UNION的注意事项:1.两个结果集(表)的列数要相同

             2.UNION返回结果集的列名以第一个SELECT为准

             3.两个结果集对应列的必须可以隐式转换

             4.UNION默认消去重复行,可以使用关键字ALL来返回重复行

 

仍然是之前的两张表:第一个SELECT 起名叫File
之后无论起什么名字都是File,以第一个为准。

图片 21  

 

连接类型:

   

    连接分为三种:内连接、外连接、交叉连接。

 

连接类型:

   

    连接分为三种:内连接、外连接、交叉连接。

 

图片 22

图片 22

内连接(INNER JOIN)

   

   
使用比较运算符(包括=、>、<、<>、>=、<=、!>和!<)进行表间的比较操作,查询与连接条件相匹配的数据。根据比较运算符不同,内连接分为等值连接和不等连接两种。

内连接(INNER JOIN)

   

   
使用比较运算符(包括=、>、<、<>、>=、<=、!>和!<)进行表间的比较操作,查询与连接条件相匹配的数据。根据比较运算符不同,内连接分为等值连接和不等连接两种。

外连接(outer
join)分为
:左外连接(left join / left outer
join)、右外连接(right join / right outer join)和全外连接(full join /
full outer join)

外连接(outer
join)分为
:左外连接(left join / left outer
join)、右外连接(right join / right outer join)和全外连接(full join /
full outer join)

1、等值连接

   
 概念:在连接条件中使用等于号(=)运算符,其查询结果中列出被连接表中的所有列,包括其中的重复列。

 

        [sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>       
  2. select * from T_student s,T_class c where s.classId = c.classId   
  3.      等于  
  4. select * from T_student s inner join T_class c on s.classId = c.classId</span></span>  

       结果是:

               
 图片 24

                 

1、等值连接

   
 概念:在连接条件中使用等于号(=)运算符,其查询结果中列出被连接表中的所有列,包括其中的重复列。

 

[sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>       
  2.     select * from T_student s,T_class c where s.classId = c.classId   
  3.      等于  
  4.      select * from T_student s inner join T_class c on s.classId = c.classId</span></span>  

       结果是:

                 图片 25

                 

左外连接(left join / left outer
join):
满足on条件表达式,左外连接是以左表为准,返回左表所有的数据,与右表匹配的则有值,没有匹配的则以空(null)取代。

左外连接(left join / left outer
join):
满足on条件表达式,左外连接是以左表为准,返回左表所有的数据,与右表匹配的则有值,没有匹配的则以空(null)取代。

2、不等连接

 

 
 概念:在连接条件中使用除等于号之外运算符(>、<、<>、>=、<=、!>和!<)

 

         [sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>       
  2.     select * from T_student s inner join T_class c on s.classId <> c.classId</span></span>  

       结果是:

 

             
  图片 26

2、不等连接

 

 
 概念:在连接条件中使用除等于号之外运算符(>、<、<>、>=、<=、!>和!<)

 

[sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>       
  2.     select * from T_student s inner join T_class c on s.classId <> c.classId</span></span>  

       结果是:

 

                图片 27

1 select * from Student s
2 left join Course c on s.C_S_Id=c.C_Id
1 select * from Student s
2 left join Course c on s.C_S_Id=c.C_Id

 

图片 28

图片 28

外连接

 

   

    外连接分为左连接(LEFT JOIN)或左外连接(LEFT OUTER
JOIN)、右连接(RIGHT JOIN)或右外连接(RIGHT OUTER JOIN)、全连接(FULL
JOIN)或全外连接(FULL OUTER
JOIN)。我们就简单的叫:左连接、右连接和全连接。

 

外连接

 

   

    外连接分为左连接(LEFT JOIN)或左外连接(LEFT OUTER
JOIN)、右连接(RIGHT JOIN)或右外连接(RIGHT OUTER JOIN)、全连接(FULL
JOIN)或全外连接(FULL OUTER
JOIN)。我们就简单的叫:左连接、右连接和全连接。

 

右外连接(right join / right outer
join):满足on条件表达式,右外连接是以右表为准,返回右表所有的数据,与左表匹配的则有值,没有匹配的则以空(null)取代。

右外连接(right join / right outer
join):满足on条件表达式,右外连接是以右表为准,返回右表所有的数据,与左表匹配的则有值,没有匹配的则以空(null)取代。

1、左连接:

 

   
概念:返回左表中的所有行,如果左表中行在右表中没有匹配行,则结果中右表中的列返回空值。

 

         [sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>      
  2.     select * from  T_student s left join T_class c on s.classId = c.classId</span></span>  

  结果是:
             
  图片 30

 

  总结:左连接显示左表全部行,和右表与左表相同行。

 

1、左连接:

 

   
概念:返回左表中的所有行,如果左表中行在右表中没有匹配行,则结果中右表中的列返回空值。

 

[sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>      
  2.     select * from  T_student s left join T_class c on s.classId = c.classId</span></span>  

  结果是:
                图片 31

 

  总结:左连接显示左表全部行,和右表与左表相同行。

 

1 select * from Student s
2 right join Course c on s.C_S_Id=c.C_Id
1 select * from Student s
2 right join Course c on s.C_S_Id=c.C_Id

2、右连接:

 

 
 概念:恰与左连接相反,返回右表中的所有行,如果右表中行在左表中没有匹配行,则结果中左表中的列返回空值。

         [sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>     
  2.    select * from  T_student s right join T_class c on s.classId = c.classId</span></span>  

   结果是:

 

           
  图片 32

  总结:右连接恰与左连接相反,显示右表全部行,和左表与右表相同行。

 

2、右连接:

 

 
 概念:恰与左连接相反,返回右表中的所有行,如果右表中行在左表中没有匹配行,则结果中左表中的列返回空值。

[sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>     
  2.    select * from  T_student s right join T_class c on s.classId = c.classId</span></span>  

   结果是:

 

              图片 33

  总结:右连接恰与左连接相反,显示右表全部行,和左表与右表相同行。

 

图片 34

图片 34

3、全连接:

 

 
概念:返回左表和右表中的所有行。当某行在另一表中没有匹配行,则另一表中的列返回空值

 

         [sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>      
  2.    select * from  T_student s full join T_class c on s.classId = c.classId</span></span>  

   结果是:

 

         
  图片 36

   总结:返回左表和右表中的所有行。

 

3、全连接:

 

 
概念:返回左表和右表中的所有行。当某行在另一表中没有匹配行,则另一表中的列返回空值

 

[sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>      
  2.    select * from  T_student s full join T_class c on s.classId = c.classId</span></span>  

   结果是:

 

            图片 37

   总结:返回左表和右表中的所有行。

 

全外连接(full join / full outer
join):满足on条件表达式,返回两个表符合条件的所有行,a表没有匹配的则a表的列返回null,b表没有匹配的则b表的列返回null,即返回的是左连接和右连接的并集。

全外连接(full join / full outer
join):满足on条件表达式,返回两个表符合条件的所有行,a表没有匹配的则a表的列返回null,b表没有匹配的则b表的列返回null,即返回的是左连接和右连接的并集。

交叉连接(CROSS JOIN):也称迪卡尔积

 

   
概念:不带WHERE条件子句,它将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积(例如:T_student和T_class,返回4*4=16条记录),如果带where,返回或显示的是匹配的行数。

 

交叉连接(CROSS JOIN):也称迪卡尔积

 

   
概念:不带WHERE条件子句,它将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积(例如:T_student和T_class,返回4*4=16条记录),如果带where,返回或显示的是匹配的行数。

 

1 select * from Student s
2 full join Course c on s.C_S_Id=c.C_Id
1 select * from Student s
2 full join Course c on s.C_S_Id=c.C_Id

1、不带where:

 

         [sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>     
  2.    select *from T_student cross join T_class  
  3.   ‘等于  
  4.    select *from T_student, T_class</span></span>  

    

结果是:

                         
 图片 38

       总结:相当与笛卡尔积,左表和右表组合。

 

1、不带where:

 

[sql] view
plain copy

 

  1. <span style=”font-size:18px;”><span style=”font-family:System;”>     
  2.    select *from T_student cross join T_class  
  3.   ‘等于  
  4.    select *from T_student, T_class</span></span>  

    

结果是:

                         
 图片 39

       总结:相当与笛卡尔积,左表和右表组合。

 

图片 40

图片 40

2、有where子句,往往会先生成两个表行数乘积的数据表,然后才根据where条件从中选择。

 

         [sql] view
plain copy

 

  1. select * from T_student s cross join T_class c where s.classId = c.classId   
  2.    (注:cross join后加条件只能用where,不能用on)  

 

 

      查询结果跟等值连接的查询结果是一样。

 

 

      连接查询非常简单,只需要在项目中多多实践,不断总结。

 

转载自:

2、有where子句,往往会先生成两个表行数乘积的数据表,然后才根据where条件从中选择。

 

[sql] view
plain copy

 

  1. select * from T_student s cross join T_class c where s.classId = c.classId   
  2.    (注:cross join后加条件只能用where,不能用on)  

 

 

      查询结果跟等值连接的查询结果是一样。

 

 

      连接查询非常简单,只需要在项目中多多实践,不断总结。

交叉连接(cross
join)
:交叉连接将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。

交叉连接(cross
join)
:交叉连接将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。

不加条件返回两个表行数的乘积:

不加条件返回两个表行数的乘积:

1 select * from Student s
2 cross join Course c 
1 select * from Student s
2 cross join Course c 

图片 42

图片 42

图片 44

图片 44

加上条件返回满足条件表达式的两个表的行:

加上条件返回满足条件表达式的两个表的行:

1 select * from Student s
2 cross join Course c 
3 where s.C_S_Id=c.C_Id
1 select * from Student s
2 cross join Course c 
3 where s.C_S_Id=c.C_Id

图片 46

图片 46

PS:cross
join后加条件只能用where,不能用on,这一点跟后面的自连接一样。

PS:cross
join后加条件只能用where,不能用on,这一点跟后面的自连接一样。

自连接:自连接,连接的两个表都是同一个表,即自己连接自己。

自连接:自连接,连接的两个表都是同一个表,即自己连接自己。

1 -- 查询出男生身高比女生身高矮的学生信息
2 select s1.S_Name,s1.S_Sex,s1.S_Height,s2.S_Name,s2.S_Sex,s2.S_Height  
3 from Student s1,
4 Student s2 
5 where s1.S_BirthDate=s2.S_BirthDate
6 and s1.S_Height<s2.S_Height
7 and s1.S_Sex='男' 
8 and s2.S_Sex='女'
1 -- 查询出男生身高比女生身高矮的学生信息
2 select s1.S_Name,s1.S_Sex,s1.S_Height,s2.S_Name,s2.S_Sex,s2.S_Height  
3 from Student s1,
4 Student s2 
5 where s1.S_BirthDate=s2.S_BirthDate
6 and s1.S_Height<s2.S_Height
7 and s1.S_Sex='男' 
8 and s2.S_Sex='女'

图片 48

图片 48

1 --查询出学生身高一样但是学号不一样的学生信息
2 select * from Student s1,Student s2
3 where s1.S_Height=s2.S_Height
4 and s1.S_StuNo<>s2.S_StuNo
1 --查询出学生身高一样但是学号不一样的学生信息
2 select * from Student s1,Student s2
3 where s1.S_Height=s2.S_Height
4 and s1.S_StuNo<>s2.S_StuNo

图片 50

图片 50

由于表的数据的原因,所以自连接的示例可能不太容易理解。可以看下面这个示例,根据表名查询出表的主外键约束名,示例如下:

由于表的数据的原因,所以自连接的示例可能不太容易理解。可以看下面这个示例,根据表名查询出表的主外键约束名,示例如下:

1 select a.Name as 表名,b.Xtype as 键类型,b.Name as 键名
2 from sysobjects a,sysobjects b
3 where a.ID=b.parent_obj and a.name='Student'
4 and b.Xtype in('F','PK')
1 select a.Name as 表名,b.Xtype as 键类型,b.Name as 键名
2 from sysobjects a,sysobjects b
3 where a.ID=b.parent_obj and a.name='Student'
4 and b.Xtype in('F','PK')

内连接如果没有指定连接条件的话,和笛卡尔积的交叉连接结果一样,但是不同于笛卡尔积的地方是,没有笛卡尔积那么复杂要先生成行数乘积的数据表,内连接的效率要高于笛卡尔积的交叉连接。

内连接如果没有指定连接条件的话,和笛卡尔积的交叉连接结果一样,但是不同于笛卡尔积的地方是,没有笛卡尔积那么复杂要先生成行数乘积的数据表,内连接的效率要高于笛卡尔积的交叉连接。

最后说明一下,交叉连接如果有WHERE子句的话,往往会先生成两个表行数乘积的行的数据表然后再根据WHERE条件从中选择。 

最后说明一下,交叉连接如果有WHERE子句的话,往往会先生成两个表行数乘积的行的数据表然后再根据WHERE条件从中选择。 

因此,如果两个表数据量太大,将会非常非常慢,不建议使用。

因此,如果两个表数据量太大,将会非常非常慢,不建议使用。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图