总结 SQL 内连接,左外连接,右外连接,全连接,交叉连接


概述

联接条件可在 FROMWHERE 子句中指定,建议在 FROM 子句中指定联接条件。WHEREHAVING 子句也可以包含搜索条件,以进一步筛选联接条件所选的行。

联接可分为以下几类:

内联接

(典型的联接运算,使用像 =<> 之类的比较运算符)。包括相等联接自然联接
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students 和 courses 表中学生标识号相同的所有行。

外联接

外联接可以是左向外联接右向外联接完整外部联接
FROM 子句中指定外联接时,可以由下列几组关键字中的一组指定:

  • LEFT JOINLEFT OUTER JOIN
    左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。

  • RIGHT JOINRIGHT OUTER JOIN
    右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。

  • FULL JOINFULL OUTER JOIN
    完整外部联接(即全连接)返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。

交叉联接

交叉联接从两个或多个连接表返回笛卡尔乘积的记录。交叉联接也称作笛卡尔积。没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。
交叉连接关键字:cross join (不带条件where…)

注意:
FROM子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息,请参见使用外联接。

使用举例

表结构说明:

a表:   id   name     |    b表:   id   job   parent_id   
         1   张3      |            1     23     1   
         2   李四     |            2     34     2   
         3   王武     |            3     34     4   

其中,a.id 同 b.parent_id 存在关联关系

内连接:

select a.*, b.* from a inner join b on a.id = b.parent_id;
--等价于:
select a.*, b.* from a join b on a.id = b.parent_id;
--等价于:
select a.*, b.* from a, b where a.id = b.parent_id;
--等价于:(注:cross join 后加条件只能用 where,不能用 on)
select a.*, b.* from a cross join b where a.id = b.parent_id;

--结果:
1   张3                 1     23     1   
2   李四                2     34     2   

左向外连接:

select a.*, b.* from a left join b on a.id = b.parent_id;
-- 等价于:
select a.*, b.* from a left outer join b on a.id = b.parent_id;

--结果:
1   张3                  1     23     1   
2   李四                 2     34     2   
3   王武                 null  null   null

右向外连接:

select a.*, b.* from a right join b on a.id = b.parent_id;
--等价于:
select a.*, b.* from a right outer join b on a.id = b.parent_id;

--结果:
1       张3                   1     23     1   
2       李四                  2     34     2   
null    null                  3     34     4   

完整外部连接:

select a.*, b.* from a full join b on a.id = b.parent_id;
--等价于:
select a.*, b.* from a full outer join b on a.id = b.parent_id;

--结果:
1      张3                  1     23     1   
2      李四                 2     34     2   
null   null                 3     34     4   
3      王武                 null  null   null

交叉连接:

select * from a cross join b;
--等价于:
select * from a, b;

--结果:(注意,返回 3 * 3 = 9 条记录,即笛卡尔积)
1   张3  1   23  1
1   张3  2   34  2
1   张3  3   34  4
2   李四  1   23  1
2   李四  2   34  2
2   李四  3   34  4
3   王武  1   23  1
3   王武  2   34  2
3   王武  3   34  4

参考文章:
* http://www.blogjava.net/zolly/archive/2007/10/23/SQLJION.html
* https://www.cnblogs.com/youzhangjin/archive/2009/05/22/1486982.html
* https://www.yiibai.com/sql/sql-cartesian-joins.html

发表评论