【数据库】HIVE SQL -left join (on 和 where条件放置的区别)

在使用left join的过程中，总是遇到一个问题，就是将条件放在on中还是where条件中。
在查过一些资料和实际操作后，总结了一下：
       在多张表连接时，都会生成一张中间表，然后再将这张临时表返回给用户。
       在用left join中，on和where条件的区别如下：
       （1）on条件是在生成中间表时使用的条件，它不管on中的条件是否为真，都会返回左表中的记录。
       （2）where条件是在中间表生成好之后，再对这张表进行果过滤，这时已经和left join没有关系，不符合where条件的就会被过滤掉。

案例：

select a.*,b.no_b,b.status
  from (select no
          from t1
         where d = '2019-04-01'
           and no in (1686475705, 1743166512, 1780711122)
       ) a
  left join (select no, status
               from t2
              where d = '2019-04-01'
                and no in (1686475705, 1743166512, 1780711122)
            ) b
    on a.no = b.no
   and b.status <> 'C'

 结果：

no                       no_b                  status
1686475705      null                  null
1743166512      1743166512      s
1780711122      1780711122       s

select a.*
  from (select no
          from t1
         where d = '2019-04-01'
           and no in (1686475705, 1743166512, 1780711122)
       ) a
  left join (select no, status
               from t2
              where d = '2019-04-01'
                and no in (1686475705, 1743166512, 1780711122)
            ) b
    on a.no = b.no
 where b.status <> 'C'

结果：

no
1743166512
1780711122

   其实，关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的`在这里插入代码片`特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
    left join的where和join有执行的先后顺序，若条件写在on中，就是先过滤这其中的条件，过滤完之后在按照left join执行，所以第一段代码，就相当于先把status <> 'C'的过滤掉，再和a表关联，实际返回的结果应该是a中的所有记录。

   当该条件在where中时，就是先执行完left join的操作，对于返回的结果再进行过滤。