SQL内连接、外链接详解

由于SQL语句可实践性，因此这里通过两张表和一些列子来讲解什么是SQL内连接和外链接。

实例：存在两张表T_USER和T_JOB，其中T_JOB表的parent_id关联T_USER表的id字段。即它们存在关联关系。下面通过这两张表来演示内连接、外链接。

表(T_USER)

id   name
1    张三
2    李四
3    王武

表(T_JOB)

id   job   parent_id
1    23    1
2    34    2
3    34    4

注意：表B的parent_id指向表A的id

sql广告位

内联接

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如：检索T_USER表和T_JOB表中用户ID相同的所有行。

注意：

（1）典型的联接运算，使用像=或<>（Oracle存在）之类的比较运算符。

（2）内连接包括相等联接和自然联接。

实例：

select a.*, b.* from T_USER a inner join T_JOB b on a.id=b.parent_id;

结果：

1   张三   1   23   1
2   李四   2   34   2

外联接

外联接可以是左向外联接、右向外联接或完整外部联接。在FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：

1）LEFT JOIN 或 LEFT OUTER JOIN

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

实例：

select a.*, b.* from T_USER a left join T_JOB b on a.id=b.parent_id;

结果:

1   张三   1     23     1
2   李四   2     34     2
3   王武   null

2）RIGHT JOIN 或 RIGHT OUTER JOIN

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

实例：

select a.*, b.* from T_USER a right join T_JOB b on a.id=b.parent_id;

结果：

1   张三   1     23     1
2   李四   2     34     2
null       3     34     4

3）FULL JOIN 或 FULL OUTER JOIN

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

实例：

select a.*, b.* from T_USER a full join T_JOB b on a.id=b.parent_id;

结果是     

1    张三    1     23     1
2    李四    2     34     2
null         3     34     4
3    王武    null

交叉联接（CROSS JOIN）

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；交叉连接有两种：显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。

隐式的交叉连接，没有CROSS JOIN

SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;

显式的交叉连接，使用CROSS JOIN

SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;

SQL查询的基本原理

单表查询

根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。

两表连接查询

对两表求笛卡尔积并用ON条件和连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

多表连接查询

先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

sql广告位

ON后面的条件（ON条件）和WHERE条件的区别

ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。

WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。

从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：ON只进行连接操作，WHERE只过滤中间表的记录。

总结

连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：

1、 查两表关联列相等的数据用内连接。

2、 Col_L是Col_R的子集时用右外连接。

3、 Col_R是Col_L的子集时用左外连接。

4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。

5、 求差操作的时候用联合查询。

多个表查询的时候，这些不同的连接类型可以写到一块。例如：

SELECT T1.C1,T2.CX,T3.CY
FROM TAB1 T1
       INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
       INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
       LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
WHERE T1.X >T3.Y;

上面这个SQL查询是多表连接的一个示范。