内联，左外联，右外联，全连接，交叉连接 的区别

 内联，左外联，右外联，全连接，交叉连接 的区别

连接分为：内连接、外连接、交叉连接
一、内连接——最常用
定义：仅将两个表中满足连接条件的行组合起来作为结果集。
在内连接中，只有在两个表中匹配的行才能在结果集中出现
关键词：INNER JOIN
格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式
说明：
（1）列名表中的列名可以出自后面的两个表，但如果两个表中有同名列，应在列名前标明出处，格式为：表名.列名
（2）若连接的两个表名字太长，可以为它们起个别名。 格式为：表名 AS 别名
（3）INNER是默认方式，可以省略

eg:

select *
from   t_institution i
inner join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = "5801"
其中inner可以省略。
等价于早期的连接语法
select *
from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"

二、外连接
1、左（外）连接
定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL
关键字：LEFT JOIN

eg:

select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
其中outer可以省略。

注意:

当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件
加入到 join子句时，SQL
Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句 中，SQL
Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响：
条件在join子句
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
and i.inst_no = “5801”
结果是：
inst_no     inst_name             inst_no     teller_no   teller_name
5801        天河区                5801        0001        tom
5801        天河区                5801        0002        david
5802        越秀区
5803        白云区
条件在where子句
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = “5801”
结果是：
inst_no     inst_name             inst_no     teller_no   teller_name
5801           天河区             5801          0001               tom
5801           天河区             5801          0002              david

2、右（外）连接
定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL
关键字：RIGHT JOIN
3、完全连接
定义：在内连接的基础上，还包含两个表中所有不符合条件的数据行，并在其中的左表、和右表列填写NULL
关键字：FULL JOIN
三、交叉连接
定义：将两个表的所有行进行组合，连接后的行数为两个表的乘积数。（笛卡尔积）
关键词：CROSS JOIN
格式：FROM 表名1 CROSS JOIN 表名2

四, 自身连接
自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。
下面例子是在机构表中查找本机构和上级机构的信息。
select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
from t_institution i
join t_institution s
on i.superior_inst = s.inst_no

结果是：
superior_inst sup_inst_name         inst_no     inst_name
800           广州市                5801        天河区
800           广州市                5802        越秀区
800           广州市                5803        白云区

 

联接条件可在   FROM   或   WHERE   子句中指定，建议在   FROM   子句中指定联接条件。WHERE   和   HAVING   子句也可以包含搜索条件，以进一步筛选联接条件所选的行。     
      
  联接可分为以下几类：       
      
  内联接（典型的联接运算，使用像   =   或   <>   之类的比较运算符）。包括相等联接和自然联接。       
  内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索   students   和   courses   表中学生标识号相同的所有行。     
      
  外联接。外联接可以是左向外联接、右向外联接或完整外部联接。       
  在   FROM   子句中指定外联接时，可以由下列几组关键字中的一组指定：     
      
  LEFT   JOIN   或   LEFT   OUTER   JOIN。       
  左向外联接的结果集包括   LEFT   OUTER   子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。     
      
  RIGHT   JOIN   或   RIGHT   OUTER   JOIN。       
  右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。     
      
  FULL   JOIN   或   FULL   OUTER   JOIN。       
  完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。     
      
  交叉联接。       
  交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。     
      
  例如，下面的内联接检索与某个出版商居住在相同州和城市的作者：     
      
  USE   pubs     
  SELECT   a.au_fname,   a.au_lname,   p.pub_name     
  FROM   authors   AS   a   INNER   JOIN   publishers   AS   p     
        ON   a.city   =   p.city     
        AND   a.state   =   p.state     
  ORDER   BY   a.au_lname   ASC,   a.au_fname   ASC     
      
  FROM   子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。       
      
      
      
      
  例子：     
  a表     id   name     b表     id   job   parent_id     
              1   张3                   1     23     1     
              2   李四                 2     34     2     
              3   王武                 3     34     4     
      
  a.id同parent_id   存在关系     
      
  内连接     
  select   a.*,b.*   from   a   inner   join   b     on   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     
      
  结果是       
  1   张3                   1     23     1     
  2   李四                 2     34     2     
  3   王武                 null     
  右连接     
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id     
      
  结果是       
  1   张3                   1     23     1     
  2   李四                 2     34     2     
  null                        3     34     4     
      
  完全连接     
      
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id     
      
      
  结果是       
  1   张3                   1     23     1     
  2   李四                 2     34     2     
  null                        3     34     4     
  3   王武                 null