SQL连接详解-优快云博客

本文链接：https://blog.youkuaiyun.com/danwind/article/details/6035260

SQL 的连接(JOIN)语句将数据库中的两个或多个表组合起来。基于 ANSI 标准的 SQL 列出了四种 JOIN 方式: 内连接(INNER), 外连接(OUTER), 左外连接(LEFT), 和右外连接(RIGHT). 在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(self-join).

下文中解释"连接"都将用到这里的两张表. 表中的记录(行)用于演示不同类型的"连接"和"连接谓词"的作用. 在下面两张表中, Department.DepartmentID 是主键, Employee.DepartmentID 是外键.

雇员表(Employee)
LastName	DepartmentID
Rafferty	31
Jones	33
Steinberg	33
Robinson	34
Smith	34
Jasper	NULL

部门表(Department)
DepartmentID	部門
31	銷售部
33	工程部
34	書記
35	市場部

注: "市場部" 部门目前没有员工列出. 同样, 雇员 "Jasper" 不在 Department 表中的任何一个部门.

1. 内连接(inner join)是应用程序中用的普遍的"连接"操作, 它一般都是默认的连接类型. 内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起, 产生新的结果表. 查询会将 A 表的每一行和 B 表的每一行进行比较, 并找出满足连接谓词的组合. 当连接谓词被满足, A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行. 连接产生的结果集, 可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合, 然后返回满足连接谓词的记录. 实际上 SQL 产品会尽可能用其他方式去实现连接, 笛卡尔积运算是非常没效率的.

SQL 定义了两种不同语法方式去表示"连接". 首先是"显示连接符号", 它显式地使用关键字 JOIN, 其次是"隐式连接符号", 它使用所谓的"隐式连接符号". 隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分, 并用逗号隔开. 这样就构成了一个"交叉连接", WHERE 语句可能放置一些过滤谓词(过滤条件). 那些过滤谓词在功能上等价于显式连接符号.

内连接"可以进一步被分为: 相等连接, 自然连接, 和交叉连接。

显式的内连接实例:

SELECT *
FROM   employee 
       INNER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

等价于:

SELECT *  
FROM   employee, department 
WHERE  employee.DepartmentID = department.DepartmentID
1.1 相等连接 (equi-join, 或 equijoin), 是比较连接(θ连接)的一种特例, 它的连接谓词只用了相等比较. 使用其他比较操作符(如 <)的不是相等连接. 前面的查询已经展示了一个相等连接的实例:
   
    
     SELECT *
FROM   employee 
       INNER JOIN department 
          ON employee.DepartmentID = department.DepartmentID


    

   
SQL 提供了一种可选的简短符号去表达相等连接, 它使用 USING 关键字 (Feature ID F402):

   

    

     SELECT *
FROM   employee 
       INNER JOIN department 
          USING (DepartmentID)


    

   
USING 结构并不仅仅是语法糖, 上面查询的结果和使用显式谓词得到的查询得到的结果是不同的. 特别地, 在 USING 部分列出的列(column)将以只出现一次, 且名称无表名修饰.在上面的例子中, 将产生单独的名为 DepartmentID 的列, 而不是 employee.DepartmentID 或 department.DepartmentID.
USING 语句现已被 MySQL, Oracle, PostgreSQL, SQLite, 和 DB2/400 等产品支持.
 
1.2自然连接比相等连接的进一步特例化. 两表做自然连接时, 两表中的所有名称相同的列都将被比较, 这是隐式的. 自然连接得到的结果表中, 两表中名称相同的列只出现一次.
上面用于内连接的查询实例可以用自然连接的方式表示如下:

   

    

     SELECT *
FROM   employee NATURAL JOIN department


    

   
用了 USING 语句后, 在连接表中, DepartmentID 列只出现一次, 且没有表名作前缀:
 
1.3 交叉连接(cross join), 又称笛卡尔连接(cartesian join)或叉乘(Product), 它是所有类型的内连接的基础. 把表视为行记录的集合, 交叉连接即返回这两个集合的笛卡尔积. 这其实等价于内连接的链接条件为"永真", 或连接条件不存在.
如果 A 和 B 是两个集合, 它们的交叉连接就记为: A × B.
用于交叉连接的 SQL 代码在 FROM 列出表名, 但并不包含任何过滤的连接谓词.
显式的交叉连接实例:

   

    

     SELECT *
FROM   employee CROSS JOIN department


    

   
隐式的交叉连接实例:

   

    

     SELECT *
FROM   employee, department;


     2.外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 -- 甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.
(在这种情况下left<左> 和 right<右> 表示 JOIN 关键字的两边.)
在标准的 SQL 语言中, 外连接没有隐式的连接符号.
2.1左外连接(left outer join), 亦简称为左连接(left join), 若 A 和 B 两表进行左外连接, 那么结果表中将包含"左表"(即表 A)的所有记录, 即使那些记录在"右表" B 没有符合连接条件的匹配. 这意味着即使 ON 语句在 B 中的匹配项是0条, 连接操作还是会返回一条记录, 只不过这条记录的中来自于 B 的每一列的值都为 NULL. 这意味着左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果.
如, 这允许我们去找到雇员的部门时, 显示所有雇员, 即使这个雇员还没有关联的部门. (在上面的内连接部分由一个相反的例子, 没有关联的部门号的雇员在结果中是不显示的).
左外连接实例: (相对于内连接增添的行用斜体标出)
       
        
         SELECT *  
FROM   employee  LEFT OUTER JOIN department  
          ON employee.DepartmentID = department.DepartmentID

         
2.3全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.
如, 这允许我们查看每一个在部门里的员工和每一个拥有雇员的部门, 同时, 还能看到不在任何部门的员工以及没有任何员工的部门.
全连接实例:
          
           
            SELECT *  
FROM   employee 
       FULL OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID


           

          
2.2右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已. 如果 A 表右连接 B 表, 那么"右表" B 中的每一行在连接表中至少会出现一次. 如果 B 表的记录在"左表" A 中未找到匹配行, 连接表中来源于 A 的列的值设为 NULL.
右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).
例如, 这允许我们在找每一个雇员以及他的部门信息时, 当这个部门里没有任何雇员时, 也把部分显示出来.
右连接的实例: (相对于内连接增添的行用斜体标出)

          

           

            SELECT * 
FROM   employee RIGHT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID
一些数据库系统(如 MySQL)并不直接支持全连接, 但它们可以通过左右外连接的并集(参: union)来模拟实现. 和上面等价的实例:
             
              
               SELECT *
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM   employee
       RIGHT JOIN department
          ON employee.DepartmentID = department.DepartmentID
WHERE  employee.DepartmentID IS NULL


              

             
SQLite 不支持右连接, 全外连接可以按照下面的方式模拟:

             

              

               SELECT employee.*, department.*
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION
SELECT employee.*, department.*
FROM   department
       LEFT JOIN employee
          ON employee.DepartmentID = department.DepartmentID
WHERE  employee.DepartmentID IS NULL
 
3.自连接就是和自身连接。 下面的例子是一个很好的说明.
构建一个查询, 它试图找到这样的记录: 每条记录包含两个雇员, 他们来自于同一个国家. 如果你有两张雇员表(Employee),
 那么只要第一张表的雇员和第二张表的雇员在同样的国家的就行了, 你可以用一个通常的连接(相等连接)操作去得到这个表. 
不过, 这里所有雇员信息都在一张但对大表里