左联接小结
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'
where 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
where u.app_id = 'MS'
from M_USER_INFO U
left join MS_V_STAFF_INFO S on u.staff_NO = s.staff_NO
and s.dept_code = '4'
where 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
where u.app_id = 'MS'
上述两个SQL文,逻辑意义一样。但是下面SQL文,意义就不一样了,结果上区别很大,有兴趣的可以在Tg4server-MS中试验一下
关键在[and s.dept_code = '4']这句话
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'
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'