求一SQL优化

SQL code

SELECT 
     mp.id,
     mp.phone,
     mp.carrier_id,
     mp.region_id,
     rg.name AS regionname,
     ca.name AS carriername,
     rg.area_identity AS areaidentity 
FROM
     gsms_region_carrier_map AS mp 
     JOIN gsms_carrier AS ca 
          ON mp.carrier_id = ca.id 
     JOIN gsms_region_code AS rg 
          ON mp.region_id = rg.id 
WHERE mp.is_removed = FALSE 
     AND mp.id NOT IN 
     (SELECT 
          mp2.remove_id 
     FROM
          gsms_region_carrier_map AS mp2 
     WHERE is_removed = TRUE)

我想把后面的那个子查询变成 join 形式的.
ps:gsms_region_carrier_map 表id是主键.

------解决方案--------------------
SELECT 
mp.id,
mp.phone,
mp.carrier_id,
mp.region_id,
rg.name AS regionname,
ca.name AS carriername,
rg.area_identity AS areaidentity 
FROM
gsms_region_carrier_map AS mp 
JOIN gsms_carrier AS ca 
ON mp.carrier_id = ca.id 
JOIN gsms_region_code AS rg 
ON mp.region_id = rg.id 
left join gsms_region_carrier_map mp2
on mp.id =mp2.remove_id 
WHERE mp.is_removed = FALSE 
AND mp2.remove_id is null