LeftJoin条件问题

左联接小结

select u.staff_NO, s.STAFF_NAME, S.*, u.*
  from M_USER_INFO U
  left join MS_V_STAFF_INFO S on u.staff_NO = s.staff_NO
                               
                             and s.dept_code = '4'
                               
                             and u.app_id = 'MS'
 
select u.staff_NO, s.STAFF_NAME, S.*, u.*
  from M_USER_INFO U
  left join (select * from MS_V_STAFF_INFO where dept_code = '4') S on u.staff_NO =s.staff_NO                                                                     
                                                                   and u.app_id = 'MS'

上述两个SQL文，逻辑意义一样。但是下面SQL文，意义就不一样了，结果上区别很大，有兴趣的可以在Tg4server-MS中试验一下

select u.staff_NO, s.STAFF_NAME, S.*, u.*

  from M_USER_INFO U

  left join MS_V_STAFF_INFO S on u.staff_NO = s.staff_NO

where u.app_id = 'MS'

   and s.dept_code = '4'

关键在[u.app_id = 'MS'   and s.dept_code = '4']这两个条件如果放在Where里面则限制只抽出对应记录.
而放在ON里面则限制只对相应记录进行左联结,而其他记录抽出但是不进行左联结