Left Join & Right Join & Full Join & Join

Foreword: TBL_BPM_HK_PINS save house keep process instances(contains all outdated pins and uptodate pins), TBL_BPM_PMAP save process maps. Maybe the process map has been deleted, so if want to provider a page to manage these pins, cannot use the common join, or there will be some pins cannot be searched.
Use left join instead.

select m.PMAP_ID, m.PMAP_NAME, m.PMAP_VERSION, i.PINS_ID, i.PINS_STATUS, i.CREATED_BY, i.CREATED_DT, i.UPDATED_DT
from TBL_BPM_PMAP m right join TBL_BPM_HK_PINS i on i.PMAP_ID = m.PMAP_ID
where i.PINS_STATUS like '%status%'
      and m.PMAP_NAME like '%process_map_name%'

The difference of several join mode.
Example:
Table1, City(cityId,cityName)
Table2, Company(companyId,companyName,cityId)
1. Left (Out) Join
returns ALL CITIES no matter whether existing a company in this city.

SELECT ct.cityId, ct.cityName, cp.companyName
FROM City ct LEFT JOIN Company cp ON ct.cityId = cp.cityId

2. Right (Out)Join
returns ALL COMPANIES no matter whether this company located where.

SELECT cp.companyId, cp.companyName, ct.cityName
FROM City ct RIGHT JOIN Company cp ON ct.cityId = cp.cityId

3. Full (Out) Join
returns ALL CITY'names and COMPANY'names.

SELECT ct.cityName, cp.companyName
FROM City ct FULL JOIN Company cp ON ct.cityId = cp.cityId

Note: Mysql dosen't support full join operation. Use below instead.

SELECT ct.cityName, cp.companyName
FROM City ct LEFT JOIN Company cp ON ct.cityId = cp.cityId
union
SELECT  ct.cityName, cp.companyName
FROM City ct RIGHT JOIN Company cp ON ct.cityId = cp.cityId

4. (Inner) Join
return the city'name and the company'name if the company loacted on this city.

SELECT ct.cityName, cp.companyName
FROM City ct JOIN Company cp ON ct.cityId = cp.cityId