Left uniform summary
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'
The above two SQL texts are the same as the logical sense. But the following SQL text, the meaning is different, the result is very different, interested in TG4Server-MS test
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'
The key is in [u.app_id = 'ms' and s.dept_code = '4'] These two conditions are limited to only the corresponding record is limited to the WHERE.
Inside the ON, the restriction is limited only to the corresponding record, and other records are drawn, but do not carry out left link.
