SQL JOIN大全

联接类型
        在关系代数中，联接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。联接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。

        本文从几个简单的例子里，手把手教你如何写内联接、左外联接、右外联接、全外联接以及交叉联接。
        SQL 提供了多种类型的联接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于联接的行时所采用的方法不同。
                内联接：只联接匹配的行；
                左外联接：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行；
                右外联接：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行；
                全外联接：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行；
                (H)(theta)联接：使用等值以外的条件来匹配左、右两个表中的行；
                交叉联接：生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配。
        在 INFORMIX 中联接表的查询。如果 FROM 子句指定了多于一个表引用，则查询会联接来自多个表的行。联接条件指定各列之间（每个表至少一列）进行联接的关系。因为正在比较联接条件中的列，所以它们必须具有一致的数据类型。
        SELECT 语句的 FROM 子句可以指定以下几种类型的联接：
                CROSS JOIN：笛卡尔乘积（所有可能的行对）；
                INNER JOIN：仅对满足联接条件的 CROSS 中的列；
                LEFT OUTER JOIN：一个表满足条件的行，和另一个表的所有行；
                RIGHT OUTER JOIN：与 LEFT 相同，但两个表的角色互换；
                FULL OUTER JOIN：LEFT OUTER 和 RIGHT OUTER中所有行的超集。
        示例详解
        创建 T_STUDENT 表：

 

-- Create table
create table T_STUDENT
(
  ID   NUMBER(15) not null,
  NAME VARCHAR2(50)
)
tablespace SYBJ
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table T_STUDENT
  is '学生表';
-- Add comments to the columns
comment on column T_STUDENT.NAME
  is '姓名';
-- Create/Recreate primary, unique and foreign key constraints
alter table T_STUDENT
  add constraint T_STUDENT_PRIM primary key (ID)
  using index
  tablespace SYBJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

-- Create table
create table T_STUDENT
(
  ID   NUMBER(15) not null,
  NAME VARCHAR2(50)
)
tablespace SYBJ
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T_STUDENT
  is '学生表';
-- Add comments to the columns 
comment on column T_STUDENT.NAME
  is '姓名';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_STUDENT
  add constraint T_STUDENT_PRIM primary key (ID)
  using index 
  tablespace SYBJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 
        编辑 T_STUDENT 表数据如下：

 

        创建 T_SCORE 表：

 

-- Create table
create table T_SCORE
(
  ID    NUMBER(15) not null,
  SCORE NUMBER(3)
)
tablespace SYBJ
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table T_SCORE
  is '学生成绩表';
-- Create/Recreate primary, unique and foreign key constraints
alter table T_SCORE
  add constraint T_SCORE_PRIM primary key (ID)
  using index
  tablespace SYBJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

-- Create table
create table T_SCORE
(
  ID    NUMBER(15) not null,
  SCORE NUMBER(3)
)
tablespace SYBJ
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T_SCORE
  is '学生成绩表';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_SCORE
  add constraint T_SCORE_PRIM primary key (ID)
  using index 
  tablespace SYBJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 
        编辑 T_SCORE 表数据如下：

 

        一、外联接
        1、概念：包括左向外联接、右向外联接或完整外部联接。
        2、左联接：left join 或 left outer join。
                1）左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
                2）sql 语句：

select * from t_student left join t_score on t_student.id=t_score.id

select * from t_student left join t_score on t_student.id=t_score.id

 
                结果集：

 

                注释：包含 t_student 的所有子句，根据指定条件返回 t_score 相应的字段，不符合的以 null 显示。
        3、右联接：right join 或 right outer join。
                1）右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
                2）sql 语句：

select * from t_student right join t_score on t_student.id=t_score.id

select * from t_student right join t_score on t_student.id=t_score.id

 
                结果集：

 

                注释：包含 t_score 的所有子句，根据指定条件返回 t_student 相应的字段，不符合的以 null 显示。
        4、完整外部联接：full join 或 full outer join。
                1）完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
                2）sql 语句：

select * from t_student full join t_score on t_student.id=t_score.id

select * from t_student full join t_score on t_student.id=t_score.id

 
                结果集：

 

                注释：返回左右连接的和（见上左、右连接），即：返回左右联接的结果集的并集。
        二、内联接
        1、概念：内联接是用比较运算符比较要联接列的值的联接。
        2、内联接：join 或 inner join。
        3、sql 语句：

select * from t_student inner join t_score on t_student.id=t_score.id

select * from t_student inner join t_score on t_student.id=t_score.id

 
        结果集：

 

        注释：只返回符合条件的 t_student 和 t_score 的列，即：返回左右联接的结果集的交集。
        4、等价（与下列执行效果相同）：
                A：select a.*,b.* from t_student a,t_score b where a.id=b.id
                B：select * from t_student cross join t_score where t_student.id=t_score.id（注：cross join 后加条件只能用 where，不能用 on）
        三、交叉联接（完全）
        1、概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小（t_student 和 t_score 交叉联接产生 3 * 3 = 9 条记录）。
        2、交叉联接：cross join（不带条件 where...）。
        3、sql语句：

 

select * from t_student cross join t_score

select * from t_student cross join t_score

 
        结果集：

 

        注释：返回 3 * 3 = 9 条记录，即笛卡尔积。
        4、等价（与下列执行效果相同）：
                A：select * from t_student,t_score