内连接、外连接、左连接、右连接、等值连接、自然连接和自连接之间的区别

多表查询经常用到连接，各种连接之间的区别应该注意总结。

首先大概认识各种连接的关系和由来：

表之间的连接常有以下两种：

• 在SELECT语句的WHERE子句中使用比较运算符给出连接条件，对表进行连接，将这种表示形式称为连接谓词表示形式。连接谓词中的比较符可以是<、<=、=、>、>=、!=、<>、!< 和 !>，当比较符为“=”时，就是等值连接，等值连接的结果中有重复列，在目标列中去除相同的字段名就是自然连接。
• 以JOIN关键字指定的连接，T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有所增强，以JOIN关键字指定的连接有三种类型：内连接、外连接、交叉连接（笛卡尔积）。

下面详细分析各种连接方式的特点：

1.等值连接（相等连接）：

SELECT XSB.* , CJB.*
 FROM  XSB , CJB
 WHERE XSB.学号 = CJB.学号

使用”=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2.自然连接：

数据库应用中最常用的是“自然连接”，它在目标列中去除相同的字段名。

SELECT XSB.* , CJB.课程号, CJB.成绩
FROM XSB , CJB
WHERE XSB.学号= CJB.学号

进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。

等值连接和自然连接的区别：

1）等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。

2）等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。

3.内连接

指定了INNER关键字的连接是内连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。内连接是系统默认的，可以省略INNER关键字。使用内连接后仍可使用WHERE子句指定条件。扩展：超全的数据库建表/SQL/索引规范，适合贴在工位上！

例1

SELECT  *
 FROM  XSB  INNER  JOIN  CJB 
   ON  XSB.学号 =CJB.学号

例2

SELECT 姓名, 成绩
     FROM XSB JOIN CJB 
       ON XSB.学号 = CJB.学号 
       WHERE 课程号 = '206'  AND 成绩>=80

4.自连接

自连接作为一种特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

SELECT a.学号, a.课程号, b.课程号, a.成绩
 FROM CJB a  JOIN  CJB b 
   ON  a.成绩=b.成绩 AND  a.学号=b.学号 AND  a.课程号!=b.课程号

5.外连接（左外连接，右外连接、全外连接）

指定了OUTER关键字的为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下三种：

左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行。

【例】 查找所有学生情况，以及他们选修的课程号，若学生未选修任何课，也要包括其情况

 SELECT XSB.* , 课程号
    FROM  XSB  LEFT OUTER JOIN CJB 
   ON  XSB.学号 = CJB.学号

本例执行时，若有学生未选任何课程，则结果表中相应行的课程号字段值为NULL。

右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行。

完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。

其中的OUTER关键字均可省略**

6.交叉连接（又名笛卡尔积）

首先，先简单解释一下笛卡尔积。

有两个集合A和B，A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。

从以上的数据分析我们可以得出以下两点结论：

1. 两个集合相乘，不满足交换率，既 A×B ≠ B×A;
2. A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性，即两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数。

交叉连接：

SELECT * from 表1 JOIN 表2；

交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，称为‘笛卡尔积表’，结果表的行数等于两个表的行数之积。

如果两张表的数据量都比较大的话，那样就会占用很大的内存空间这显然是不合理的。所以，我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。

因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

喜欢的朋友欢迎关注，订阅更多精彩内容