Join-随笔(3)

对于Join操作中的Where子句,正是因为对历史的兼容性造成了一些误解.
从SQL 92标准以后,Join语法规范中并不包含Where子句.
对于外联结来说,Where只是提供Join操作结果集上的过滤或者说是搜索.

对于outer join来说,良好的书写习惯是:
用on子句说明联结条件,用Where子句来说明search条件.
如果把search条件放到了on子句中,会出现意想不到的结果,看下例:
    table_A.a          table_A.b                             table_B.a
        3                        1                               3
        6                        6                               6

(1) SELECT *
     FROM table_A
     LEFT OUTER JOIN table_B ON  (table_A.a = table_B.a)
     WHERE table_A.b >5;
结果是:   
      table_A.a          table_A.b                             table_B.a
          6                      6                                6
         
(2) SELECT *
     FROM table_A
     LEFT OUTER JOIN table_B ON  (table_A.a = table_B.a)
     AND (table_A.b >5);
结果是几条记录呢? 2条! 如下:
    table_A.a          table_A.b                             table_B.a
        3                        1                               null
        6                        6                                6

是不是有些奇怪,和想要的结果有些出入?

如果把table_A中(6,6)的记录修改为(6,3),再执行语句2,又得到什么结果呢?
答案还是2条记录. 具体是什么,可以自己执行一下看看.
之所以会有这样的结果,会在后面进行解释.

在进一步探讨on,where子句之前,我先引入如下概念
由于A left join B和B right join A的等效性,在很多时候说左表和右表就比较容易
混淆了.所以在一些资料中, 会使用Inner table(内表)和Outer Table(外表)来区分
Inner Table:内表就是只提供匹配记录信息的表;
Outer Table:外表是指对外联结同时提供未匹配记录信息的表.
对于inner join来说,参与join的表都是内表;
对于A  left outer join B来说, A就是外表,B就是内表.
我个人也觉得这样来定义能够更好的表达后面的论述,所以下面也就用
Inner table和Outer table来说明.

对于outer join来说,遵循如下规律:
<1>对于外联结,一般来说,结果集合的记录数是由Outer table来决定的;

<2>对于Outer table的过滤条件,必须放在where子句中才能生效.
       在where子句中对Outer table进行过滤后,再进行join操作.

<3>如果把Outer table的过滤条件放到On子句中,并不会影响到结果集中的
      记录数.它所能影响的只是在结果集中的记录中,哪些记录对应的inner table
      的信息会以null形式出现.
      再看一下例句2的返回结果.它的意思就是对于join操作的结果,
      其中table_A.b<=5的记录,其table_B.a都是null.
      同理,把table_A中(6,6)的记录修改为(6,3),所得到的结果所有table_B.a
      都显示成了null...

<4>在Where条件中如果对Inner Table进行过滤操作,
       那么虽然在SQL语句中出现了left/right/full outer join的关键词,但实际上
       的实现方式是内联结,优化器会以inner join方式实现!
      原因:因为内表只提供匹配记录信息,对于这些信息进行过滤,最终的操作
              与inner join等效;
             
      (3) SELECT *
     FROM table_A
     LEFT OUTER JOIN table_B ON  (table_A.a = table_B.a)
     WHERE  table_B.a > 3;
     可以用数据库对该语句和inner join的方式的执行计划比较一下,是否相同.
    
<5>把Inner table的过滤条件放到On子句中,相当于对Inner table先进行过滤
       Outer table和过滤后的inner table子集进行join操作.
 
综上,在outer join操作中,如果想进行过滤操作,
对于Outer table,放在where子句中,
对于Inner table,放在On子句中;

把Inner table的过滤操作放在Where子句中,转化称为内联结,
把Outer table的过滤操作放在On子句中,只是让不满足过滤条件的记录,
其相应的关联字段取值为null.
             
清楚了以上几种情况,相信再复杂的关联查询理解起来也不是什么难事了.
至于self join,可以通过alias name方式视同两个结构一样的表,然后进行
相应的join操作了. 实际的应用中多是用来把纵表拉成横表的应用,
这里就不再多说了.

基本上能想到的Join语法里面应该注意的东西就是这几篇随笔里面
所提到的.希望对大家能够有所帮助.